Convert data to 3 columns x 24 rows matrix and count by ID and hour of day

Question

I would appreciate if you guys could help me figure out how to: Select 12 separate counts on a single column based on time (for one date, I assume through a Declare Date first at the top and then insert time after, that part I think I understand) the second is once 12 separate cases are built run them 3 times so you would have 12 counts on 3 separate IDS.

All of this is in a single table.

the way I figure it would work but didn't is

Declare @Date DATE

set @Date = '24-11-2015' 

Select (case(Select statement here) where Projectid in()) as A,
       (case(Select statement here) where Projectid in()) as B,
       (case(Select statement here) where Projectid in()) as C

If anyone understands what I mean, I would appreciate if you could help me out.

Input data:

Project id  Datetime                 Necessary
----------  -----------------------  ---------
5           11-23-2015 09:00:00.000  1
5           11-23-2015 10:00:00.000  1
6           11-23-2015 11:00:00.000  0
1           11-23-2015 12:00:00.000  1
3           11-23-2015 13:00:00.000  1
124         11-23-2015 14:00:00.000  1
124         11-23-2015 15:00:00.000  1
124         11-23-2015 16:00:00.000  0
576         11-23-2015 17:00:00.000  0
576         11-23-2015 18:00:00.000  1
576         11-23-2015 19:00:00.000  1
etc         11-23-2015 20:00:00.000  1

Expected output:

Datetime       5    124  576
-------------  ---  ---  ---
09:00 - 09:59  0    4    5
10:00 - 10:59  4    3    1
11:00 - 11:59  5    2    1
12:00 - 12:59  1    1    1
13:00 - 13:59  6    1    1
14:00 - 14:59  6    1    1
15:00 - 15:59  7    1    2
16:00 - 16:59  8    1    3
17:00 - 17:59  9    1    3
18:00 - 18:59  1    1    2
19:00 - 19:59  12   1    0
20:00 - 20:59  0    0    0

What I am looking for is projectids 5 124 and 576 as the column variables the way the rows should be counted is based on time per hour.

What I have so far with the help of Salman A

 Hour   5  124  576
    8   9   0   1
    9   0   11  10
    10  4   8   15
    11  0   10  7
    12  1   4   17
    13  6   18  6
    14  2   5   27
    15  4   1   43
    16  4   11  40
    17  3   11  3
    18  6   2   9
    19  9   5   7

That "sql" you have outlined just will not work. A better approach to your question is to show us some "sample data" and then your "expected result" based on that data. From those 2 things we can propose solutions. — Paul Maxwell, Nov 24 '15 at 10:06
I don't understand what you mean. See comment by @Used_By_Already. — user1429080, Nov 24 '15 at 10:51
Thanks. We have an expected result, now all we need is the raw sample data please. Then we can probably suggest a solution. — Paul Maxwell, Nov 24 '15 at 10:54
yes, you did and voila you got an answer too. you should remember to provide sample data and expected result with your next sql related question — Paul Maxwell, Nov 24 '15 at 23:39

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

If I understand correctly, you need to join your table with values 0...11 (tricks discussed here) and follow by COUNT(CASE...).

SELECT
    master..spt_values.number AS [Hour of Day],
    COUNT(CASE WHEN projectid IN (1, 2) THEN 1 END) AS [Project 1 and 2],
    COUNT(CASE WHEN projectid IN (3, 4) THEN 1 END) AS [Project 3 and 4],
    COUNT(CASE WHEN projectid IN (5, 6) THEN 1 END) AS [Project 5 and 6]
FROM master..spt_values
LEFT JOIN yourtable ON master..spt_values.number = DATEPART(HOUR, yourdatetime)
WHERE master..spt_values.number BETWEEN 0 AND 11 AND master..spt_values.type = 'p'
GROUP BY master..spt_values.number
ORDER BY master..spt_values.number

edited May 23 '17 at 11:59

Community

1
1

answered Nov 24 '15 at 10:09

Salman A

262,204
82
430
521

what is the (Then 1 end) doing in this select? – Anon Ymouse Nov 24 '15 at 10:17
Now, how do I then get 12 rows out of this based on a datetime? thank you. for instance row 1 = where datetime between (at)Date 00:00:00.000 and (at)Date 01:00:00.000 thank you – Anon Ymouse Nov 24 '15 at 10:32
1

@AnonYmouse see revised answer. – Salman A Nov 24 '15 at 10:57
Thanks Salman so far, but the system does not recognise "Values" – Anon Ymouse Nov 24 '15 at 11:13
1

I ***think*** the `VALUES` clause should work in SQL 2008. If it does not you can use various tricks such as (i) create a table consisting of one int column and 12 rows (ii) use a construct such as `SELECT 0 UNION ALL SELECT 1...` (iii) use `spt_values`. Revising answer. – Salman A Nov 24 '15 at 11:16
okay, so I now need to confirm now that I have (23) values but I have no clue what they mean to me, how do they relate to time the record was created? (datetime) – Anon Ymouse Nov 24 '15 at 11:55
1

@AnonYmouse each _cell_ consist of count of records that (i) match the `CASE` condition for that column (ii) created in the hour for that row. I hope this explains it. I think I answered without understanding your question. Please consider altering your question to add necessary details such as sample input and expected output. – Salman A Nov 24 '15 at 12:10
Well would you look at that, it seems you answered my question after all. – Anon Ymouse Nov 24 '15 at 12:39

Convert data to 3 columns x 24 rows matrix and count by ID and hour of day

1 Answers1