Cross weekdays and hours in a select statement and count records

Question

I'm writing a report application that will tell the user how many records have come into the table, according to the days and hours. This is the basic table structure for dbo.N_Preinscripcion:

ID (int) PK
FechaEnv (DateTime)
Nombre (varchar)
Periodo (varchar)

This is one example of the records you'll find in the table:

ID     | FechaEnv                |   Nombre            |  Periodo
---------------------------------------------------------------------
20000  | 2015-11-20 11:35:38.000 |  María Fernanda     | 2016-01
20001  | 2015-11-20 11:52:10.000 |  LINA MARIA         | 2016-01
20002  | 2015-11-20 15:24:14.000 |  ANA PATRICIA       | 2016-02
20003  | 2015-11-21 09:35:35.000 |  Catalina           | 2016-01
20004  | 2015-11-21 09:40:04.000 |  Angélica  Liliana  | 2016-01
20005  | 2015-11-21 13:09:56.000 |  paula andrea       | 2016-01
20006  | 2015-11-21 17:08:52.000 |  luis orlando       | 2016-01
20007  | 2015-11-21 18:02:33.000 |  ANGELA PAULINA     | 2016-01
20008  | 2015-11-22 17:29:46.000 |  angie carolina     | 2016-01
20009  | 2015-11-22 19:36:18.000 |  Nesly Yurani       | 2016-02
20010  | 2015-11-22 20:49:32.000 |  jennifer lisbeth   | 2016-01

I need to know how many records came in each day and each hour, to get a full report. For example:

DAY      | 10:00 | 11:00 | 12:00 | 13:00 | 14:00...
MON      |   4   |    0  |   2   |   13  |   3  ... 
TUE      |   6   |    2  |   2   |   14  |   6  ...
WEN      |   6   |    5  |   4   |   16  |   7  ...
THU      |   8   |    6  |   5   |   18  |   9  ...
FRI      |   9   |    7  |   1   |   11  |   1  ...
SAT      |   0   |    9  |   8   |   12  |   9  ...
SUN      |   1   |    1  |   1   |   12  |   5  ...

So far, I've manage to write the code for hours and days, but I can't mix them up together.

Hours:

SELECT 
    DATEPART(HOUR,[P].[FechaEnv]) as [Hour],
    COUNT(DATEPART(HOUR,[P].[FechaEnv])) as [Records]
FROM [dbo].[N_Preinscripcion] [P]
WHERE
    ([P].[Periodo] = @p)
GROUP BY 
    DATEPART(HOUR, [P].[FechaEnv])
ORDER BY 
    DATEPART(HOUR, [P].[FechaEnv]);

Days:

SELECT 
    DATEPART(WEEKDAY,[P].[FechaEnv]) as [Day],
    COUNT(DATEPART(WEEKDAY,[P].[FechaEnv])) as [Records]
FROM [dbo].[N_Preinscripcion] [P]
WHERE
    ([P].[Periodo] = @p)
GROUP BY 
    DATEPART(WEEKDAY, [P].[FechaEnv])
ORDER BY 
    DATEPART(WEEKDAY, [P].[FechaEnv]);

How can I achieve this? Some kind of pivot?

You have to use pivot: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — droebi, Sep 27 '19 at 07:44

score 1 · Answer 1 · answered Sep 27 '19 at 11:56

You can use conditional aggregation:

SELECT DATEPART(WEEKDAY,[P].[FechaEnv]) as [Day],
       SUM(CASE WHEN DATEPART(HOUR, P.FechaEnv) = 10 THEN 1 ELSE 0 END) as hour_10,
       SUM(CASE WHEN DATEPART(HOUR, P.FechaEnv) = 11 THEN 1 ELSE 0 END) as hour_11,
       SUM(CASE WHEN DATEPART(HOUR, P.FechaEnv) = 12 THEN 1 ELSE 0 END) as hour_12,
       . . .
FROM [dbo].[N_Preinscripcion] [P]
WHERE [P].[Periodo] = @p
GROUP BY DATEPART(WEEKDAY, [P].[FechaEnv])
ORDER BY MIN([FechaEnv]);

A Redfearn · Accepted Answer · 2019-09-27T13:37:40.667

You can use pivot like so

CREATE TABLE #N_Preinscripcion(
    ID int primary key,
    FechaEnv datetime,
    Nombre varchar(100),
    Periodo varchar(100)
)

INSERT INTO #N_Preinscripcion
VALUES 
(20000, '2015-11-20 11:35:38.000', 'María Fernanda', '2016-01'), 
(20001, '2015-11-20 11:52:10.000', 'LINA MARIA', '2016-01'), 
(20002, '2015-11-20 15:24:14.000', 'ANA PATRICIA', '2016-02'),
(20003, '2015-11-21 09:35:35.000', 'Catalina', '2016-01'), 
(20004, '2015-11-21 09:40:04.000', 'Angélica Liliana', '2016-01'), 
(20005, '2015-11-21 13:09:56.000', 'paula andrea', '2016-01'),
(20006, '2015-11-21 17:08:52.000', 'luis orlando', '2016-01'),
(20007, '2015-11-21 18:02:33.000', 'ANGELA PAULINA', '2016-01'),
(20008, '2015-11-22 17:29:46.000', 'angie carolina', '2016-01'),
(20009, '2015-11-22 19:36:18.000', 'Nesly Yurani', '2016-02'),
(20010, '2015-11-22 20:49:32.000', 'jennifer lisbeth', '2016-01')

DECLARE @P varchar(100) = '2016-01'

;WITH pivotData as
(
    SELECT  
        DATEPART(WEEKDAY,[P].[FechaEnv]) as [Day],
        DATEPART(HOUR,[P].[FechaEnv]) as [Hour],
        ID as [ID]

    FROM #N_Preinscripcion [P]
    WHERE P.Periodo = @P
)
SELECT CASE 
            [Day] 
                When 1 Then 'Sunday'       
                When 2 Then 'Monday'        
                When 3 Then 'Tuesday'        
                When 4 Then 'Wednesday'       
                When 5 Then 'Thursday'        
                When 6 Then 'Friday'        
                When 7 Then 'Saturday' 
            END AS [Day],
            [9],[10],[11],[12],[13],[14],[15],[16],[17],[18], [19], [20]

FROM pivotData
PIVOT(COUNT(ID) for [Hour] in ([9],[10],[11],[12], [13], [14], [15], [16], [17], [18], [19], [20])) as pivoted

DROP TABLE #N_Preinscripcion

Output

Day       9           10          11          12          13          14          15          16          17          18          19          20
--------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Sunday    0           0           0           0           0           0           0           0           1           0           0           1
Friday    0           0           2           0           0           0           0           0           0           0           0           0
Saturday  2           0           0           0           1           0           0           0           1           1           0           0

Cross weekdays and hours in a select statement and count records

2 Answers2