Count records by Grouping a period

user_9090849 0
Viewed 62

I have a table that stores per day if a user worked or if he was on vacation based on a value. Example table, Value = 1 -> WorkDay, Value = 2 -> Vacation:

User    | Day        | Value
--------|------------|-------
user-1  | 2021-01-01 | 1
user-1  | 2021-01-02 | 1
user-1  | 2021-01-03 | 1
user-1  | 2021-01-04 | 1
user-1  | 2021-01-05 | 2
user-1  | 2021-01-06 | 2
...

I'll like to convert this table to this (Using the simple example above):

User    | Year | Month | WorkDay | Vacation
--------|------|-------|---------|---------
user-1  | 2021 | 01    | 4       | 2
...

I tried using group by, subqueries and case, but the whole thing become a mess.

SELECT
    YEAR(Day),
    MONTH(DAY),
    User,

    ...

From Table1
Group By YEAR(Day), MONTH(DAY), User
tsql sql-server sql
2 Answers

Just use conditional aggregation:

select year(day), month(day),
       sum(case when value = 1 then 1 else 0 end) as workday,
       sum(case when value = 2 then 1 else 0 end) as vacation
from table1
group by year(day), month(day)
user_11440350

You can use conditional aggregation as below:

select User,
       year(day), 
       month(day),
       sum(case when value = 1 then 1 else 0 end) as WorkDay,
       sum(case when value = 2 then 1 else 0 end) as Vacation
from table1
group by User, year(day), month(day)

DB-Fiddle:

Schema and insert statements:

 create table table1([User] varchar(50), Day date, Value int);
 insert into table1 values('user-1'  , '2021-01-01' , 1);
 insert into table1 values('user-1'  , '2021-01-02' , 1);
 insert into table1 values('user-1'  , '2021-01-03' , 1);
 insert into table1 values('user-1'  , '2021-01-04' , 1);
 insert into table1 values('user-1'  , '2021-01-05' , 2);
 insert into table1 values('user-1'  , '2021-01-06' , 2);

Query:

 select [User] as "user",
        year(day) as "year", 
        month(day) as "month",
        sum(case when value = 1 then 1 else 0 end) as WorkDay,
        sum(case when value = 2 then 1 else 0 end) as Vacation
 from table1
 group by [User], year(day), month(day)
 GO

Output:

user year month WorkDay Vacation
user-1 2021 1 4 2

db<fiddle here

user_86516010
Related