CODEWITHSUNDEEP
sqlsql-servercount
user2543622
user2543622

Reputation: 6756

SQL code to get count over each day of a range

I have a data.frame like below.

toolid          startdate       enddate         stage
abc                 1-Jan-13    5-Jan-13    production
abc                 6-Jan-13    10-Jan-13   down
xyz                 3-Jan-13    8-Jan-13    production
xyz                 9-Jan-13    15-Jan-13   down

I want to get final output which will be like below. The output needs to return - count of each stage (there could be more than 2 stages) over each day between 1jan13 to 15jan13 (or any date range that an user wants). I was able to create the required result in R. I also wrote a cursor in SQL and it achieves the purpose. But is there a way to do the same without using cursors? I am looking for logic and direction.

         date down production
1  2013-01-01    0          1
2  2013-01-02    0          1
3  2013-01-03    0          2
4  2013-01-04    0          2
5  2013-01-05    0          2
6  2013-01-06    1          1
7  2013-01-07    1          1
8  2013-01-08    1          1
9  2013-01-09    2          0
10 2013-01-10    2          0
11 2013-01-11    1          0
12 2013-01-12    1          0
13 2013-01-13    1          0
14 2013-01-14    1          0
15 2013-01-15    1          0

Upvotes: 1

Views: 344

Answers (1)

Dan Bellandi
Dan Bellandi

Reputation: 546

I think this may be what you want. It requires a recursive CTE to get a row for each day in the range.

with daterange as (
    select startdate=min(startdate),enddate=max(enddate) from #source
), dates as (
    select d=(select startdate from daterange) union all
    select dateadd(day,1,d) from dates where d<(select enddate from daterange)
)
select 
    d,
    down=(select count(*) from #source where d between startdate and enddate and stage='down'),
    production=(select count(*) from #source where d between startdate and enddate and stage='production')
from dates
order by d;

Upvotes: 3

Related Questions