Sql-server – Grouping count by interval of 15 minutes

Here's what I came up with (SQLFiddle):

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  SUM(
    CASE
      WHEN d.Type = 'Cellphone' THEN 1
      ELSE 0
    END
  ) AS [Mobile Devices],
  SUM(
    CASE
      WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN 1
      ELSE 0
    END
  ) AS [Computers],
  SUM(
    CASE
      WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN 1
      ELSE 0
    END
  ) AS [Audiovisual]
FROM SupportTicket st
JOIN Link l 
  ON st.SupportTicket_ID IN (l.Entity_ID1, l.Entity_ID2)
JOIN Device d 
  ON d.Device_ID IN (l.Entity_ID1, l.Entity_ID2)
WHERE 
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted >= CONVERT(datetime, '20140201', 112)
  AND st.Date_Submitted < CONVERT(datetime, CONVERT(date, CURRENT_TIMESTAMP))
GROUP BY
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department
ORDER BY
  st.SupportTicket_ID;

The basic idea is to compute the sum of an expression that returns 1 if the current row has the required type, and zero otherwise. It's probably possible using PIVOT syntax as well.

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;