Author |
Topic |
mclovin
Starting Member
15 Posts |
Posted - 2013-01-09 : 15:59:04
|
Hello here is my issue, I need to create report that should look like this. FastSpeed FasterSpeed SlowSpeedNY 5 2 3LA 2 5 6TX 1 3 4----------------------------------------------------The data would should look like above.let say the numbers are the number of service in that location with that service Contract.say that there are 4 tablesServices Table - holds the servicesLocation Table - hold the locationService Type - Hold the Type of serviceService Contract - Hold the different Contractsso typical I would do something like this to get the information neededSelect Location.Name ,Service_Contract.Name ,count(Services.Service)From Servicesinner join Locationon Location.ID_Location = Services.ID_Locationinner join Service_Type on Service_Type.ID_Service_Type = Services.ID_Service_typeinner Join Service_Contract on Service_Contract.ID_service_Contract = Service_Type.ID_Service_Contractgroup by Location.Name, Service_Contract.Name-----------------which would give me a results ofLocation Contract_Name Number of ServicesNY FastSpeed 5LA FastSpeed 2TX FastSpeed 1NY FasterSpeed 2LA FasterSpeed 5TX FasterSpeed 3---------------------------------------------Which is what i told it do with the statements but how do i get it to look like the first example which is what i want. Any help would be appreciated. Sorry if the example was to long, just wanted to make sure it could be understood |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-01-09 : 18:04:10
|
Have you considered the PIVOT operator? BOL would have the details.=================================================Tact is the ability to describe others as they see themselves. -Abraham Lincoln |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 21:13:48
|
A good ol' fashioned pre-aggregated CROSS TAB is usually faster than a PIVOT. Like this...WITHctePreAgg AS( SELECT LocationName = loc.Name , ContractName = svcc.Name , ServiceCount = COUNT(svc.Service) FROM dbo.Services svc JOIN dbo.Location loc ON loc.ID_Location = svc.ID_Location JOIN dbo.Service_Type svct ON svct.ID_Service_Type = svc.ID_Service_type JOIN dbo.Service_Contract svcc ON svcc.ID_Service_Contract = svct.ID_Service_Contract GROUP BY loc.Name, svcc.Name) SELECT Location , FastSpeed = SUM(CASE WHEN ContractName = 'FastSpeed' THEN ServiceCount ELSE 0 END) , FasterSpeed = SUM(CASE WHEN ContractName = 'FasterSpeed' THEN ServiceCount ELSE 0 END) , SlowSpeed = SUM(CASE WHEN ContractName = 'SlowSpeed' THEN ServiceCount ELSE 0 END) , Total = SUM(ServiceCount) FROM ctePreAgg ORDER BY Location; Of course, unsubstantiated claims of performance are just begging for a fight so here's a link that demonstrates it.[url]http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:19:35
|
quote: Originally posted by Jeff Moden A good ol' fashioned pre-aggregated CROSS TAB is usually faster than a PIVOT. Like this...WITHctePreAgg AS( SELECT LocationName = loc.Name , ContractName = svcc.Name , ServiceCount = COUNT(svc.Service) FROM dbo.Services svc JOIN dbo.Location loc ON loc.ID_Location = svc.ID_Location JOIN dbo.Service_Type svct ON svct.ID_Service_Type = svc.ID_Service_type JOIN dbo.Service_Contract svcc ON svcc.ID_Service_Contract = svct.ID_Service_Contract GROUP BY loc.Name, svcc.Name) SELECT Location , FastSpeed = SUM(CASE WHEN ContractName = 'FastSpeed' THEN ServiceCount ELSE 0 END) , FasterSpeed = SUM(CASE WHEN ContractName = 'FasterSpeed' THEN ServiceCount ELSE 0 END) , SlowSpeed = SUM(CASE WHEN ContractName = 'SlowSpeed' THEN ServiceCount ELSE 0 END) , Total = SUM(ServiceCount) FROM ctePreAgg GROUP BY Location ORDER BY Location; Of course, unsubstantiated claims of performance are just begging for a fight so here's a link that demonstrates it.[url]http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it."
fixed a typo------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-10 : 19:23:32
|
Thanks for the cover, Visakh.--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 22:32:32
|
no problemyou're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mclovin
Starting Member
15 Posts |
Posted - 2013-01-17 : 16:50:51
|
Sorry Guys for not getting back sooner but My internet went down the same day after a posted the and had came back up. Just wanted to say thanks for responding and that i got it to work with you guys help. really appreciated it. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-17 : 22:35:40
|
cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|