How can I execute a pivot query in a range of numbers and sort the results?

Question

I am looking for an MS SQL query (for use in SQL Reporting Services) to give me the following result:

JobCode 0-10 11-20 21-30 31-40 41-50 A 1 2 0 0 2 B 0 2 2 0 0 C 3 0 1 1 0 

Current request:

 SELECT RecordID , CAST(GETDATE() - JobStartDate AS Int) AS DaysSinceStart , Code FROM tblJobs 

Returns these results:

 RecordID | DaysSinceStart | Code 158987 11 A 158968 3 A 158972 4 C 158973 16 B 158974 23 C 158975 13 B 158976 45 A 158977 32 C 158985 9 C 158981 25 B 158982 47 A 158983 18 A 158978 5 C 158979 27 B 

I can use the following to return a general resume, but does not allow me to view individual results by JobCode:

 SELECT Range AS [Day Range], COUNT(*) AS Jobs FROM (SELECT CASE WHEN DaysSinceStart BETWEEN 0 AND 9 THEN ' 0- 9' WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19' WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29' WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39' WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49' WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59' WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69' WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79' WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89' WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' ELSE 'Over 100' END AS Range FROM (SELECT DaysSinceStart FROM tblJobs) AS derivedtbl_1) AS t GROUP BY Range 

Can someone help with getting a breakdown by code field, please? Thanks in advance. Postscript I am using MS SQL Server 2012

Answer 1

Try the following:

 select * from (SELECT code,Range AS [Day Range], COUNT(*) AS Jobs FROM (SELECT code,CASE WHEN DaysSinceStart BETWEEN 0 AND 9 THEN '0- 9' WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19' WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29' WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39' WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49' WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59' WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69' WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79' WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89' WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' ELSE 'Over 100' END AS Range FROM (SELECT code,DaysSinceStart FROM demo) AS derivedtbl_1) AS t GROUP BY Range,code) q1 pivot (max(jobs) for [Day Range] in ([0- 9],[10-19],[20-29],[30-39],[40-49],[50-59], [60-69],[70-79],[80-89],[90-99],[Over 100]) )as pvt 

SQL Fiddle

Answer 2

Try this option -

Query:

 SET NOCOUNT ON; DECLARE @tblJobs TABLE ( RecordID INT , DaysSinceStart INT , Code CHAR(1) ) INSERT INTO @tblJobs (RecordID, DaysSinceStart, Code) VALUES (158987, 11, 'A'), (158968, 3, 'A'), (158972, 4, 'C'), (158973, 16, 'B'), (158974, 23, 'C'), (158975, 13, 'B'), (158976, 45, 'A'), (158977, 32, 'C'), (158985, 9, 'C'), (158981, 25, 'B'), (158982, 47, 'A'), (158983, 18, 'A'), (158978, 5, 'C'), (158979, 27, 'B') SELECT * FROM ( SELECT t.Code , [Range] = CASE WHEN DaysSinceStart BETWEEN 0 AND 9 THEN '0-9' WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19' WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29' WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39' WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49' WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59' WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69' WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79' WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89' WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' ELSE 'Over 100' END FROM @tblJobs t ) o PIVOT ( COUNT(o.[Range]) FOR [Range] IN ( [0-9], [10-19], [20-29], [30-39], [40-49], [50-59], [60-69], [70-79], [80-89], [90-99], [Over 100] ) ) pt 

Output:

 Code 0-9 10-19 20-29 30-39 40-49 50-59 60-69 70-79 80-89 90-99 Over 100 ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- A 1 2 0 0 2 0 0 0 0 0 0 B 0 2 2 0 0 0 0 0 0 0 0 C 3 0 1 1 0 0 0 0 0 0 0 

Answer 3

Create a view table / CTE / table / temp or possibly a table containing your intervals. Use this table to combine with datediff between JobStartDate and getdate() to get the interval. Then you can use pivot to count the values ​​for each Code and interval .

 with C(low, high, interval) as ( select 0, 10, '0-10' union all select 11, 20, '11-20' union all select 21, 30, '21-30' union all select 31, 40, '31-40' union all select 41, 50, '41-50' union all select 51, 2147483647, 'over 50' ) select Code, [0-10], [11-20], [21-30], [31-40], [41-50], [over 50] from ( select J.Code, J.RecordID, C.interval from tblJobs as J inner join C on datediff(day, J.JobStartDate, getdate()) between C.low and C.high ) as T pivot ( count(T.RecordID) for T.Interval in ([0-10], [11-20], [21-30], [31-40], [41-50], [over 50]) ) as P 

SQL Fiddle