Visionist Visionist - 1 month ago 5
SQL Question

Sql to select count using dates

I am using SQL Sever database, I am writing a query to select count of each option's RecordDate is found in surveys start and end date. If same any option found more than one time between the start and date, even then should be considered once.

tblOptions
----------------------------------
Option, RecardDate
----------------------------------
o1 , 2016-01-01
o1 , 2016-01-03
o1 , 2016-05-08
o2 , 2016-01-04
o2 , 2016-01-01
o2 , 2016-01-23
o2 , 2016-05-15
o3 , 2016-05-01
o3 , 2016-05-02
o3 , 2016-05-03
o3 , 2016-04-04
o3 , 2016-08-04


tblSurveys
----------------------------------
Surey, StartDate, EndDate
----------------------------------
s1 , 2016-01-01 , 2016-01-15
s2 , 2016-01-16 , 2016-01-31
s3 , 2016-05-01 , 2016-05-31


OUTPUT

Option, Count
-------------------
o1, 2 (Exp.:o1's recorddates found between two surveys star and end dates)
o2, 3 (Exp.:o2's recorddates found between three surveys star and end dates)
o3, 1 (Exp.:o3's recorddates found between one surveys star and end dates)

Answer

You can achieve this by joining your data together and then grouping it together (The distinct option below) so you can count the unique combinations:

select [Option]                 -- You really should try to avoid using reserved
        ,count(1) as [Count]    -- words for your column names.
from (
    select distinct o.[Option]
                    ,s.Survey
                    ,s.StartDate
                    ,s.EndDate
    from @tblOptions o
        inner join @tblSurveys s
            on(o.RecardDate between s.StartDate and s.EndDate)
    ) a
group by [Option]