• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

PIVOT query in T-SQL SQL 2005 Server

Hello
I have a table called Calls with fields (among others) EntryDate and Priority. The table also has a field CustomerId, which is primary key to the Customers table. The customers tabe also has a field called RegionId representing the region of the customer.
I would like to produce a cross-tab query showing the number of calls taken for various periods (e.g. This Week, Last Month, etc) broken by Priority for a given Region.


SELECT 'This Week' AS 'Period',[1] AS High,[2] AS Medium,[3] AS Low
      FROM
      (SELECT ID, Priority      
      FROM Calls INNER JOIN Customers ON Calls.CustID = Customers.Id
      WHERE DATEDIFF(DAY,EntryDate,GETDATE())<7
      AND CallStatus='OUTSTANDING'
      AND Customers.Region=15) AS ps
      PIVOT
      (COUNT(ID) FOR Priority IN ([1],[2],[3]))
      AS pvt

The above query executes correctly when there is data returned from the sub-query. The result is

Period          High        Medium       Low
This Week     40             28              90

When there is no data returned from the subquery, I get no rows returned.  I would like however, in this case, to have zeroes returned, like

Period          High        Medium       Low
This Week      0             0                0

How can I do this?
Thanks
 
0
CLoucas
Asked:
CLoucas
  • 2
  • 2
1 Solution
 
Reza RadCommented:
this is not good solution, but will work for you,
add an empty row and union it to your subquery.

SELECT 'This Week' AS 'Period',[1] AS High,[2] AS Medium,[3] AS Low
      FROM
      (
select '' as ID , '' Priority
      union
SELECT ID, Priority      
      FROM Calls INNER JOIN Customers ON Calls.CustID = Customers.Id
      WHERE DATEDIFF(DAY,EntryDate,GETDATE())<7
      AND CallStatus='OUTSTANDING'
      AND Customers.Region=15) AS ps
      PIVOT
      (COUNT(ID) FOR Priority IN ([1],[2],[3]))
      AS pvt

Open in new window

0
 
CLoucasAuthor Commented:
Thanks for your answer

Won't this increment the COUNT function by 1?
0
 
Reza RadCommented:
this will not effect count function because count is for priority in range (1,2,3) not other, and if you generate a '' priority this will not counted
0
 
ralmadaCommented:
Try like this:
select a.Period, isnull(b.High, 0), isnull(b.Medium, 0), isnull(b.Low, 0)
from (SELECT 'This Week' AS Period) a
left join (
	select [1] AS High,[2] AS Medium,[3] AS Low
	FROM
	(SELECT ID, Priority      
	FROM Calls INNER JOIN Customers ON Calls.CustID = Customers.Id
	WHERE DATEDIFF(DAY,EntryDate,GETDATE())<7
		AND CallStatus='OUTSTANDING'
		AND Customers.Region=15) AS ps
	PIVOT (COUNT(ID) FOR Priority IN ([1],[2],[3])) AS pvt
) b on 1=1

Open in new window

0
 
ralmadaCommented:
Honestly, I don't understand how reza_rad's comment will help you. It does not produce the result you requested.
I would really appreciate an explanation.
 
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now