CODEWITHSUNDEEP
sqlsql-server
Abhi619
Abhi619

Reputation: 47

how to fetch count data of 2 date fields in same month in SQL

I am trying to create a query where I have 3 column.

  1. C_Time: contains task Creation date time
  2. Done_Time: Contains Task completion date time
  3. User ID: Unique id of user

I want to get result where I want to get total count of created tasks in particular month and total number of done task at that same month grouped by user id

Output will be like:

UserID    |  CreatedCount   |  DoneCount
------------------------------------------
U12        |    12          |   12
-------------------------------------------
U13       |     7           |    5

here U12 user have created 12 tasks and completed 12 tasks in January 2020 month. But user U13 created 7 tasks in Jan 2020 and done 5 tasks in same month.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can use apply to unpivot the data and then aggregation:

select t.user_id, sum(is_create), sum(is_complete)
from t cross apply
     (values (t.c_time, 1, 0), (t.done_time, 0, 1)
     ) v(t, is_create, is_complete)
where v.t >= '2020-01-01' and v.t < '2020-02-01'
group by t.user_id;

You can also do this with conditional aggregation:

select user_id,
       sum(case when c_time >= '2020-01-01' and c_time < '2020-02-01' then 1 else 0 end),
       sum(case when done_time >= '2020-01-01' and done_time < '2020-02-01' then 1 else 0 end)
from t
group by user_id;

This is probably a little faster for your particular example. However, the first version is more generalizable -- for instance, it allows you to summarize easily by both user and month.

Upvotes: 1

Related Questions