SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Report and maybe Multiple select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mclovin
Starting Member

15 Posts

Posted - 01/09/2013 :  15:59:04  Show Profile  Reply with Quote
Hello here is my issue, I need to create report that should look like this.

FastSpeed FasterSpeed SlowSpeed
NY 5 2 3
LA 2 5 6
TX 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 tables
Services Table - holds the services
Location Table - hold the location
Service Type - Hold the Type of service
Service Contract - Hold the different Contracts

so typical I would do something like this to get the information needed

Select Location.Name
,Service_Contract.Name
,count(Services.Service)
From Services
inner join Location
on Location.ID_Location = Services.ID_Location
inner join Service_Type
on Service_Type.ID_Service_Type = Services.ID_Service_type
inner Join Service_Contract
on Service_Contract.ID_service_Contract = Service_Type.ID_Service_Contract
group by Location.Name, Service_Contract.Name
-----------------
which would give me a results of

Location Contract_Name Number of Services
NY FastSpeed 5
LA FastSpeed 2
TX FastSpeed 1
NY FasterSpeed 2
LA FasterSpeed 5
TX 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
Flowing Fount of Yak Knowledge

USA
1642 Posts

Posted - 01/09/2013 :  18:04:10  Show Profile  Reply with Quote
Have you considered the PIVOT operator? BOL would have the details.

=================================================
Tact is the ability to describe others as they see themselves. -Abraham Lincoln
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/09/2013 :  21:13:48  Show Profile  Reply with Quote
A good ol' fashioned pre-aggregated CROSS TAB is usually faster than a PIVOT. Like this...
WITH
ctePreAgg 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.
http://www.sqlservercentral.com/articles/T-SQL/63681/


--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."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  22:19:35  Show Profile  Reply with Quote
quote:
Originally posted by Jeff Moden

A good ol' fashioned pre-aggregated CROSS TAB is usually faster than a PIVOT. Like this...
WITH
ctePreAgg 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.
http://www.sqlservercentral.com/articles/T-SQL/63681/


--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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/10/2013 :  19:23:32  Show Profile  Reply with Quote
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."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/10/2013 :  22:32:32  Show Profile  Reply with Quote
no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mclovin
Starting Member

15 Posts

Posted - 01/17/2013 :  16:50:51  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/17/2013 :  22:35:40  Show Profile  Reply with Quote
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000