SQL Server transpose a query result

Question

I have a table with the following data:

ItemId  PendingTime
1       2016-11-23 15:57:56.000
2       2016-07-09 17:33:09.000
3       2015-11-27 18:34:03.000

and so on..

I would like to know sum of items pending since last 30 mins, last 1 hr, last 1 week, last 1 month and over an year. I came up with the following query:

SELECT
    SUM(CASE 
           WHEN datediff(MINUTE, PENDING_TIME,GETDATE()) <= 30 
              THEN 1 ELSE 0 
        END) AS '30 Min',
Sum(case when (datediff(MINUTE,PENDING_TIME, GETDATE())) > 30 AND (datediff(MINUTE,PENDING_TIME, GETDATE())) <= 60 then 1 Else 0 End) as 'Over 30 Min',
Sum(case when (datediff(MINUTE,PENDING_TIME, GETDATE())) > 60 AND (datediff(HOUR,PENDING_TIME, GETDATE())) <= 24 then 1 Else 0 End) as 'Over 1 Hr',
Sum(case when (datediff(DAY,PENDING_TIME, GETDATE())) > 1 AND (datediff(DAY,PENDING_TIME, GETDATE())) < 30 then 1 Else 0 End) as '1 month',
Sum(case when datediff(DAY,PENDING_TIME, GETDATE()) >= 30 then 1 Else 0 End) as 'More than month'
from ItemsTable where datepart(yyyy,PENDING_TIME) = DATEPART(yyyy,GETDATE())

This returns data as:

30 Min      Over 30 Min Over 1 Hr   1 month     More than month
----------- ----------- ----------- ----------- ---------------
100          350        NULL        NULL        NULL

I would like the results in 2 column format as:

30 Min       ------     45
Over 30      ------    100
Over 1 hr    ------    null
Over 1 month ------    null

I am aware of the pivot function, however the columns I have selected aren't really in the table I am selecting from, rather they are created as result of aggregate function.

How do I get this in 2 column and multiple rows format.

Thanks for reading through.

mendosi · Accepted Answer · 2016-11-22T07:36:15.533

2

You can create a derived column which classifies the waits into different types, then group by that column:

With waitCodes As (
    Select Case
         When DateDiff(Minute, PENDING_TIME, GetDate()) <= 30 Then '30 Min'
         When DateDiff(Minute, PENDING_TIME, GetDate()) <= 60 Then 'Over 30'
         When DateDiff(Minute, PENDING_TIME, GetDate()) <= 1440 Then 'Over 1 hr'
         When DateDiff(Minute, PENDING_TIME, GetDate()) <= 43200 Then '1 month'
         Else 'More than a month' End As [WaitTime]
      From ItemsTable
      Where DatePart(yyyy, PENDING_TIME) = DatePart(yyyy,GetDate()))
Select [WaitTime], Count(*) As n
  From waitCodes
  Group By [WaitTime];

In addition, I strongly recommend you change the Where clause in your query so that it doesn't apply a function to the PENDING_TIME column. There are multiple benefits of this.

...
Where PENDING_TIME >= Convert(date, Convert(char(4), DatePart(yyyy, GetDate()))+'-01-01 00:00:00.000', 121)
  And PENDING_TIME < Convert(date, Convert(char(4), DatePart(yyyy, GetDate())+1)+'-01-01 00:00:00.000', 121)
....

edited Nov 22 '16 at 07:36

answered Nov 22 '16 at 06:37

mendosi

2,001
1
12
18

Nice solution. BTW, the alternative `WHERE` clause will only work if the table in question has no future dates. – Wagner DosAnjos Nov 22 '16 at 07:02
@wdosanjos Excellent point. I assumed that this situation could not occur, but that might be a dangerous assumption. I have edited the suggested `WHERE` accordingly. – mendosi Nov 22 '16 at 07:07
1

BTW, take a look [here](http://stackoverflow.com/questions/13437362/how-to-get-first-and-last-date-of-current-year) for a solution on how to get the first and last days of the year without string manipulations. `Where PENDING_TIME >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) and PENDING_TIME < DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0)` – Wagner DosAnjos Nov 22 '16 at 07:11
@mendosi: excellent. With sql2014, the query didnt work. However, I modified the case as: Select Case When DateDiff(Minute, PENDING_TIME, GetDate()) <= 30 Then '...' When DateDiff(Minute, PENDING_TIME, GetDate()) <= 60 Then '...' and I works :-). Please update your response. I marked it as answer anyway. – Niraj Srivastava Nov 22 '16 at 07:28
@user1101987 Yes, thanks. I wrote it on my phone so forgot that you can't use inequality comparisons in a simple `CASE` expression. Updated. – mendosi Nov 22 '16 at 07:37

score 0 · Answer 2 · answered Nov 22 '16 at 07:33

you can use cross apply statement per orignal sql like this

    SELECT c.* FROM 
    (
        SELECT
           SUM(CASE 
               WHEN datediff(MINUTE, PENDING_TIME,GETDATE()) <= 30 
                  THEN 1 ELSE 0 
            END) AS '30 Min',
          Sum(case when (datediff(MINUTE,PENDING_TIME, GETDATE())) > 30 AND (datediff(MINUTE,PENDING_TIME, GETDATE())) <= 60 then 1 Else 0 End) as 'Over 30 Min',
          Sum(case when (datediff(MINUTE,PENDING_TIME, GETDATE())) > 60 AND (datediff(HOUR,PENDING_TIME, GETDATE())) <= 24 then 1 Else 0 End) as 'Over 1 Hr',
          Sum(case when (datediff(DAY,PENDING_TIME, GETDATE())) > 1 AND (datediff(DAY,PENDING_TIME, GETDATE())) < 30 then 1 Else 0 End) as '1 month',
          Sum(case when datediff(DAY,PENDING_TIME, GETDATE()) >= 30 then 1 Else 0 End) as 'More than month'
        from ItemsTable where datepart(yyyy,PENDING_TIME) = DATEPART(yyyy,GETDATE())
    )
    CROSS APPLY(VALUES('30 MIN',a),('Over 30',b),('Over 1 hr',c),('Over 1 month',d),('More than month',e)) c(title,[value])

result:

title           value
--------------- -----------
30 MIN          100
Over 30         350
Over 1 hr       NULL
Over 1 month    NULL
More than month NULL

SQL Server transpose a query result

2 Answers2