How can I do Pivot query on number range and sort results?

Question

I am looking for a 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 query:

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 an overall summary but it doesn't let me view the 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 anyone help with getting the breakdown by the Code field please? Many thanks in advance. P.S. I'm using MS SQL Server 2012

Answer 1

Try this :

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 one -

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 derived table / CTE / table variable / temp table or perhaps a regular table that holds your intervals. Use that table to join against datediff between JobStartDate and getdate() to get the intervall. 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