Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Complex Query using Self-Joins Expand / Collapse
Author
Message
Posted Monday, December 23, 2013 9:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 23, 2013 11:09 PM
Points: 36, Visits: 5
As an example, is it possible write SQL to take these 8 rows and return the following 2 rows? My self-joins are too simple and I'm getting a cartesian effect and can't figure this out.

INPUT

close_total received_tag monetary_type_id
------------- ------------- ----------------
01.00 20131212 1
02.00 20131212 2
10.00 20131213 1
20.00 20131213 1
30.00 20131213 2
40.00 20131213 2
50.00 20131213 3
60.00 20131213 3

OUTPUT

received_tag sum_close_total_all sum_close_total_1 sum_close_total_2 sum_close_total_3
--------------------------------------------------------------------------------------------------
20131212 3.00 1.00 2.00 0.00
20131213 210.00 30.00 70.00 110.00

NOTES

sum_close_total_all - monetary_type_id = ALL
sum_close_total_1 - monetary_type_id = 1
sum_close_total_2 - monetary_type_id = 2
sum_close_total_3 - monetary_type_id = 3

Thanks,

Steve
Post #1525681
Posted Monday, December 23, 2013 10:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 352, Visits: 1,448
Try this:

SELECT tag_date
, SUM(close_total)
, ISNULL(SUM(CASE id WHEN 1 THEN close_total ELSE 0 END), 0) AS col1
, ISNULL(SUM(CASE id WHEN 2 THEN close_total ELSE 0 END), 0) AS col2
, ISNULL(SUM(CASE id WHEN 3 THEN close_total ELSE 0 END), 0) AS col3
FROM @t1
GROUP BY tag_date



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1525683
Posted Monday, December 23, 2013 11:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 23, 2013 11:09 PM
Points: 36, Visits: 5
Wow, perfect. Thanks. Happy Holidays.
Post #1525687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse