February 3, 2016 at 1:48 am
Hi,
I have 3 select
I want wrote this selects in one procedure, AND I Need Only Show last select result,
How i do
SELECT dbo.card_in.id_reg, dbo.region.name_reg, dbo.card_in.id_card, dbo.card.name_card, SUM(dbo.card_in.in1) AS sum_in
FROM dbo.card_in INNER JOIN
dbo.card ON dbo.card_in.id_card = dbo.card.id_card INNER JOIN
dbo.region ON dbo.card_in.id_reg = dbo.region.id_reg
WHERE (dbo.card_in.datefa1 BETWEEN 1394110 AND 13941112)
GROUP BY dbo.card_in.id_reg, dbo.region.name_reg, dbo.card.name_card, dbo.card_in.id_card
SELECT dbo.office1.id_reg, dbo.region.name_reg, dbo.card_out.id_card, dbo.card.name_card, SUM(dbo.card_out.out1) AS sum_out
FROM dbo.card_out INNER JOIN
dbo.office1 ON dbo.card_out.id_office = dbo.office1.id_office INNER JOIN
dbo.region ON dbo.office1.id_reg = dbo.region.id_reg INNER JOIN
dbo.card ON dbo.card_out.id_card = dbo.card.id_card
WHERE (dbo.card_out.date1fa BETWEEN 1394110 AND 13941112)
GROUP BY dbo.office1.id_reg, dbo.region.name_reg, dbo.card_out.id_card, dbo.card.name_card
SELECT dbo.in1.sum_in, dbo.out1.sum_out, dbo.in1.id_reg, dbo.in1.name_reg, dbo.in1.id_card, dbo.in1.name_card
FROM dbo.in1 LEFT OUTER JOIN
dbo.out1 ON dbo.in1.id_card = dbo.out1.id_card
February 3, 2016 at 1:53 am
Remove the first two maybe? If you don't need their results, don't put them in the procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2016 at 1:57 am
i need all selects
February 3, 2016 at 2:01 am
i need result of select 1 and select 2 for last select
how to set name for first select and second select for use in last select
February 3, 2016 at 2:09 am
msh083 (2/3/2016)
how to set name for first select and second select for use in last select
John
February 3, 2016 at 2:23 am
how to set name for firest and second table?
my code is:
ALTER procedure [dbo].[test2] (@d1 int,@d2 int,@d3 int,@d4 int)
as
WITH first AS
(
SELECT dbo.card_in.id_reg, dbo.region.name_reg, dbo.card_in.id_card, dbo.card.name_card, SUM(dbo.card_in.in1) AS sum_in
FROM dbo.card_in INNER JOIN
dbo.card ON dbo.card_in.id_card = dbo.card.id_card INNER JOIN
dbo.region ON dbo.card_in.id_reg = dbo.region.id_reg
WHERE (dbo.card_in.datefa1 BETWEEN @d1 AND @d2)
GROUP BY dbo.card_in.id_reg, dbo.region.name_reg, dbo.card.name_card, dbo.card_in.id_card
), second AS(
SELECT dbo.office1.id_reg, dbo.region.name_reg, dbo.card_out.id_card, dbo.card.name_card, SUM(dbo.card_out.out1) AS sum_out
FROM dbo.card_out INNER JOIN
dbo.office1 ON dbo.card_out.id_office = dbo.office1.id_office INNER JOIN
dbo.region ON dbo.office1.id_reg = dbo.region.id_reg INNER JOIN
dbo.card ON dbo.card_out.id_card = dbo.card.id_card
WHERE (dbo.card_out.date1fa BETWEEN @d3 AND @d4)
GROUP BY dbo.office1.id_reg, dbo.region.name_reg, dbo.card_out.id_card, dbo.card.name_card
)
SELECT dbo.in1.sum_in, dbo.out1.sum_out, dbo.in1.id_reg, dbo.in1.name_reg, dbo.in1.id_card, dbo.in1.name_card
FROM dbo.in1 LEFT OUTER JOIN
dbo.out1 ON dbo.in1.id_card = dbo.out1.id_card
February 3, 2016 at 2:28 am
You've given them names. First and Second in your code example. You can reference them by those names in the last query.
What are you trying to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2016 at 2:43 am
GilaMonster (2/3/2016)
You've given them names. First and Second in your code example. You can reference them by those names in the last query.What are you trying to do?
i want use SUM(dbo.card_in.in1) AS sum_in in first select for last select,
in last select i do not know ??
SELECT dbo.card_in.id_reg, dbo.region.name_reg, dbo.card_in.id_card, dbo.card.name_card, SUM(dbo.card_in.in1) AS sum_in
FROM dbo.card_in INNER JOIN
dbo.card ON dbo.card_in.id_card = dbo.card.id_card INNER JOIN
dbo.region ON dbo.card_in.id_reg = dbo.region.id_reg
WHERE (dbo.card_in.datefa1 BETWEEN @d1 AND @d2)
GROUP BY dbo.card_in.id_reg, dbo.region.name_reg, dbo.card.name_card, dbo.card_in.id_card
February 3, 2016 at 4:13 am
Please post CREATE TABLE statements for the tables involved (including constraints and indexes), INSERT statements with some sample data to illustrate the problem, the incorrect output you get now, and the expected output along with an explanation of the logic behind it.
Your current post is like calling the doctor and saying "I've got a pain somewhere, what pill should I take?" I can give you a generic painkiller, but without proper diagnosis I run the risk that it will not help or even make your problems worse.
Also, since this problem appears to be related to the problem you describe in another topic, please continue the conversation in just one of them. In the other one I suggest that you post a note that the conversation has moved elsewhere with a link so that later visitors now that the discussion in that topic is no longer relevant.
February 4, 2016 at 2:43 pm
msh083 (2/3/2016)
GilaMonster (2/3/2016)
You've given them names. First and Second in your code example. You can reference them by those names in the last query.What are you trying to do?
i want use SUM(dbo.card_in.in1) AS sum_in in first select for last select,
in last select i do not know ??
SELECT dbo.card_in.id_reg, dbo.region.name_reg, dbo.card_in.id_card, dbo.card.name_card, SUM(dbo.card_in.in1) AS sum_in
FROM dbo.card_in INNER JOIN
dbo.card ON dbo.card_in.id_card = dbo.card.id_card INNER JOIN
dbo.region ON dbo.card_in.id_reg = dbo.region.id_reg
WHERE (dbo.card_in.datefa1 BETWEEN @d1 AND @d2)
GROUP BY dbo.card_in.id_reg, dbo.region.name_reg, dbo.card.name_card, dbo.card_in.id_card
Without going into details, you can JOIN to first and second like any other tables (after all, the T in CTE stands for Table), then in the select list you refer to the fields using the CTE name or alias: first.sum_in
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply