SQL Query

[Floetic]

--Number of calls closed in 0-5 days; 6-10; 11+ [for a specified date range]declare @startdate datetime,@finishdate datetimeselect RM.fldPriorityCode as 'Priority',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Closed Calls 0-5'from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and RM.fldRequestDate between '01-01-2007' and '08-05-2007'and RM.fldRequestFlag like 'D'and RM.fldRequestStatus = 'Y' --and datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 --and datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10and datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 group by RM.fldPriorityCode unionselect 'Total' as 'Priority',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Closed Calls 0-5'from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and RM.fldRequestDate between '01-01-2007' and '08-05-2007'and RM.fldRequestFlag like 'D'and RM.fldRequestStatus = 'Y'--and datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5--and datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10and datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 order by RM.fldPriorityCode asc

Results;Priority Closed Calls 0-5 Days -------- ------------------- 1..........................142..........................18683..........................594..........................149Total.....................2090Priority Closed Calls 6-10 Days -------- -------------------- 1..........................42..........................3423..........................234..........................775..........................1Total.....................447Priority Closed Calls 11+ Days -------- -------------------- 1..........................32..........................5163..........................604..........................2255..........................3Total.....................807I'm looking to display my results like this: {excluding the dots} --------------Closed Calls Priority-----0-5 Days----6-10 Days---11+ Days -------- ---------------------------------------------- 1...................14................4...............32...................1868............342............5163...................59................23.............604...................149..............77.............2255...................0..................1...............3Total..............2090............447............807How would I go about doing this?Any help would be gratefully appreciated.

[Yahweh]

All you need are a few self joins:

select	RM_0tot5.fldPriorityCode as 'Priority',	count(datediff(day,RM_0to5.fldRequestDate,RM_6to10.fldComCanDate)) as 'Closed Calls 0-5'	count(datediff(day,RM_6to10.fldRequestDate,RM_6to10.fldComCanDate)) as 'Closed Calls 6-10'	count(datediff(day,RM_11ormore.fldRequestDate,RM_11ormore.fldComCanDate)) as 'Closed Calls 11+'from tblRequestMaster RM_0to5LEFT JOIN tblRequestMaster RM_6to10 on (RM_6to10.fldPriorityCode between 1 and 5 AND RM_6to10.fldRequestDate Between '01-01-2007' and '08-05-2007')LEFT JOIN tblRequestMaster RM_11ormore on (RM_11ormore.fldPriorityCode between 1 and 5  AND RM_11ormore.fldRequestDate Between '01-01-2007' and '08-05-2007')WHERE	RM_1to5.fldPriorityCode between 1 and 5 AND	RM_1to5.fldRequestDate between '01-01-2007' and '08-05-2007' AND	RM_1to5.fldRequestFlag like 'D' AND	RM_1to5.fldRequestStatus = 'Y' AND	RM_6to10.fldRequestFlag like 'D' AND	RM_6to10.fldRequestStatus = 'Y' AND	RM_11ormore.fldRequestFlag like 'D' AND	RM_11ormore.fldRequestStatus = 'Y'group by RM_1to5.fldPriorityCode

Odds are, that query won't work. But it should give you an idea of what your final query should look like.

[Floetic]

Thank you very much Yahweh for your help :)Although I couldn't get it working ~ this seemed to work;declare @startdate datetime,@finishdate datetimeselect RM.fldPriorityCode as 'Priority', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 then 1 else 0 end) as 'Closed Calls 0-5 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10 then 1 else 0 end) as 'Closed Calls 6-10 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 then 1 else 0 end) as 'Closed Calls 11+ Days'from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode unionselect 'Total' as 'Priority', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 then 1 else 0 end) as 'Closed Calls 0-5 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10 then 1 else 0 end) as 'Closed Calls 6-10 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 then 1 else 0 end) as 'Closed Calls 11+ Days'from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y'order by RM.fldPriorityCode asc