CODEWITHSUNDEEP
sql-server
synccm2012
synccm2012

Reputation: 497

Get count days for a date range from a different table

I have a table called tbl_days as follows.

Day_Worked  Work_date
0       2020-01-01
1       2020-01-02
0       2020-01-03
1       2020-01-04
1       2020-01-05
0       2020-01-06

Another table called tbl_user as follows.

Name       startdate     enddate
User1   2020-01-01  2020-01-02
user2   2020-01-03  2020-01-04
user3   2020-01-01  2020-01-05
user4   2020-01-01  2020-01-06
user5   2020-01-02  2020-01-06

I want to find count of days for each name as follows.

Name    Days
User1   1
user2   1
user3   2
user4   3
user5   3

I used join for work_date and startdate, also can do a union all with joining enddate, but missing the inclusive dates.

select name,sum(A.total) from (
    select name,count(day_worked) from tbl_days dw inner join tbl_user ds
    on dw.work_date=ds.startdate
    group by name
    union all
    select name,count(day_worked) from tbl_days dw inner join tbl_user ds
    on dw.work_date=ds.enddate
    group by name
     ) A group by name

How do I write a query to join these two tables correctly and select the desired result?

Upvotes: 0

Views: 63

Answers (2)

SteveC
SteveC

Reputation: 6015

It seems you're looking for something like this

select u.[Name], count(*) as [Days]
from tbl_user u
     join tbl_days d on d.work_date between u.startdate 
                                        and u.enddate 
where d.day_worked=1
group by u.[Name],
order by u.[Name];

Upvotes: 1

Ruslan
Ruslan

Reputation: 2797

Since you are using Microsoft SQL Server, you can use an APPLY for this scenario.

Something like this:

select u.name, x.days
from tbl_user u
cross apply (select count(*) as days from tbl_days ix where ix.work_date between u.startdate and u.enddate and ix.day_worked = 1) x
order by u.name

This is not tested but should work!

Upvotes: 1

Related Questions