April 13, 2012 at 6:16 am
Hi,
I have two SELECT queries. They are the same, only the conditions in the WHERE clause differentiate them. I want to have them together as one so that i can find the difference between each corresponding rows of the two result sets once it appears from either side. But the problem is Row A may have value from first part but do not have a corresponding value from second part and vice versa, i still want that row (reflecting that which ever part is missing) with those with corresponding parts.
Kindly assist. Thanks
select p1_sn,p2_sn,a.B_C,b.B_C,C_V1,C_V2,(C_V2-C_V1) as Change
From (SELECT S_N as P1_SN,B_C,Sum((Qb + Qc)) as C_V1
FROM [HH].[dbo].[W_D]
WHERE month=1
GROUP BY S_N,B_C) as a
join (SELECT S_N as P2_SN,B_C,Sum((Qb + Qc)) as C_V2
FROM [HH].[dbo].[W_D]
WHERE month=2
GROUP BY S_N,B_C) as b
on ((a.P1_SN=b.P2_SN) and (a.B_C=b.B_C))
April 13, 2012 at 6:25 am
You can use INTERSECT to find the similarities, or EXCEPT to find the differences. Or you can use a FULL OUTER JOIN if you want to see the differences on both sides. That's about as much as I can say in the absence of any DDL or sample data.
John
April 13, 2012 at 6:41 am
Hi,
Thanks for your reply, I'll try Intersect and except since i have already tried full outer join and it didn't look to work since there are two columns where all content must appear once it appears in either query (S_N and B_C).
April 13, 2012 at 6:50 am
If you're using the query you just put into your original post, that's an INNER JOIN, and it will only show you the similarities, in the same way that INTERSECT will. You need to explicitly say FULL OUTER, and if you want only to see the differences, add the following:
WHERE a.P1_SN IS NULL OR a.B_C IS NULL
OR b.P2_SN IS NULL OR b.B_C IS NULL
John
April 13, 2012 at 7:13 am
Thanks, actually copied the wrong one since i have been writing different ones. But i think the full outer join worked (though it will another problem to report a single instance of the S_N and B_C column for both queries).
I used full join ... on (a.P1_SN=b.P2_SN) and (a.B_C=b.B_C). Let me confirm it worked.
April 13, 2012 at 7:26 am
You're essentially doing a pivot or crosstab and using any kind of join is an inefficient way of doing that. The following should get you what you need. (NOTE: There's no sense including two references to the same field, so I've simplified the query to only return it once.)
SELECT
S_N
,B_C
,SUM(CASE WHEN [Month] = 1 THEN Qb + Qc ELSE 0 END) AS C_V1
,SUM(CASE WHEN [Month] = 2 THEN Qb + Qc ELSE 0 END) AS C_V2
,SUM(CASE WHEN [Month] = 2 THEN Qb + Qc ELSE -Qb - Qc END) AS Change
FROM dbo.W_D
WHERE [Month] IN (1, 2)
GROUP BY S_N, B_C
Drew
edited: Added missing paren.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 13, 2012 at 8:06 am
Hi, thank you for the replies.
They all worked, i'm so grateful.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply