Multiple PIVOTS? Need to count by Hour Per Month

Question

This should be pretty simple. I have other PIVOT SQL queries working fine. I want to count logins: by hour, by month. I am thinking two PIVOTs or UNPIVOT and then PIVOT? Yes, I have dug around here, other sites, Google, etc. I am pretty stuck.

SELECT
loginid
,DATEPART(MONTH,logtime) Month
, DATEPART(HOUR, logtime) Hour
FROM somelog (nolock)
) temp
PIVOT (
COUNT(loginid)
        FOR Month in (JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)
) AS Pvt

What I want the results to be..

HOUR,JAN,FEB,MAR

00

01

02

..

23

and I don't need 8760 (365 x 24) of them

I have tried GROUP BY HOUR I have tried GROUP BY temp.hour

I have also tried this as well.. It does seem to work, but I get Hour 00 for example 365 times.. Again, the GROUP BY issue..

SELECT
TimeOfDay
, [1] JAN
, [2] FEB
, [3] MAR
, [4] APR
, [5] MAY
, [6] JUN
, [7] JUL
, [8] AUG
, [9] SEP
, [10] OCT
, [11] NOV
, [12] DEC
FROM (SELECT logintime
, loginid
, datepart(month, logintime) [month]
, DatePart(hour, logintime) TimeOfDay
FROM sometable (nolock)) x
PIVOT (
COUNT(loginid)
for [month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt
ORDER BY 1, 2

Thanks,

Kent

Answer 1

One way to do it

WITH hour_list AS (
  SELECT 0 hour
  UNION ALL
  SELECT hour + 1 FROM hour_list WHERE hour < 23
)
SELECT h.hour,
       COALESCE(jan, 0) jan,
       COALESCE(feb, 0) feb,
       COALESCE(mar, 0) mar,
       COALESCE(apr, 0) apr,
       COALESCE(may, 0) may,
       COALESCE(jun, 0) jun,
       COALESCE(jul, 0) jul,
       COALESCE(aug, 0) aug,
       COALESCE(sep, 0) sep,
       COALESCE(oct, 0) oct,
       COALESCE(nov, 0) nov,
       COALESCE(dec, 0) dec
  FROM hour_list h LEFT JOIN
(
  SELECT DATEPART(HOUR, logtime) hour, 
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  1 THEN 1 END) jan,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  2 THEN 1 END) feb,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  3 THEN 1 END) mar,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  4 THEN 1 END) apr,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  5 THEN 1 END) may,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  6 THEN 1 END) jun,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  7 THEN 1 END) jul,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  8 THEN 1 END) aug,
         SUM(CASE WHEN DATEPART(MONTH, logtime) =  9 THEN 1 END) sep,
         SUM(CASE WHEN DATEPART(MONTH, logtime) = 10 THEN 1 END) oct,
         SUM(CASE WHEN DATEPART(MONTH, logtime) = 11 THEN 1 END) nov,
         SUM(CASE WHEN DATEPART(MONTH, logtime) = 12 THEN 1 END) dec
    FROM somelog (NOLOCK)
   GROUP BY DATEPART(HOUR, logtime)
) l 
    ON h.hour = l.hour

or with PIVOT

WITH hour_list AS (
  SELECT 0 hour
  UNION ALL
  SELECT hour + 1 FROM hour_list WHERE hour < 23
)
SELECT h.hour,
       COALESCE([1],  0) jan,
       COALESCE([2],  0) feb,
       COALESCE([3],  0) mar,
       COALESCE([4],  0) apr,
       COALESCE([5],  0) may,
       COALESCE([6],  0) jun,
       COALESCE([7],  0) jul,
       COALESCE([8],  0) aug,
       COALESCE([9],  0) sep,
       COALESCE([10], 0) oct,
       COALESCE([11], 0) nov,
       COALESCE([12], 0) dec
  FROM hour_list h LEFT JOIN 
(
  SELECT DATEPART(MONTH, logtime) month,
         DATEPART(HOUR, logtime) hour,
         COUNT(*) log_count
    FROM somelog (NOLOCK)
   GROUP BY DATEPART(MONTH, logtime), DATEPART(HOUR, logtime)
) s
PIVOT
(
  SUM(log_count) FOR month IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p
    ON h.hour = p.hour

Sample output for both queries:

| HOUR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC |
|------|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
|    0 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|    1 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|    2 |   0 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|    3 |   0 |   0 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|    4 |   0 |   0 |   0 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
...

Here is SQLFiddle demo (using CASE)
Here is SQLFiddle demo (using PIVOT)