CODEWITHSUNDEEP
sql-server
Warren
Warren

Reputation: 2014

SQL count where between dates by month

Consider the below data:

ID  Reference   Manager LeaseFirstStart            LeaseStop
1   KLEIN       John    2008-04-02 00:00:00.000    2010-04-01 00:00:00.000
2   HAWKER      John    2008-12-18 00:00:00.000    2010-09-17 00:00:00.000
3   SLEEP       Bob     2008-01-23 00:00:00.000    2009-01-22 00:00:00.000
4   CODD        Bob     2009-08-03 00:00:00.000    2010-08-02 00:00:00.000
5   ALLEN       Bob     2008-01-30 00:00:00.000    2009-07-31 00:00:00.000

The earliest month is Jan 2008 and the latest month is Sep 2010.

How can I count the number of leases that were current per month? The output should look like this:

Month      Number of Leases 
2008-01    2    
2008-02    2    
2008-03    2    
2008-04    3    
2008-05    3    
2008-06    3    
2008-07    3    
2008-08    4    
…          …

Ultimately, I want to use the answer to the question to create the dataset below for use in excel by the user so they can see who had how many leases during the data period.

Month     Manager   Number of Leases
2008-01   Bob       2
2008-01   John      0
2008-02   Bob       2
2008-02   John      0
2008-03   Bob       2
2008-03   John      0
2008-04   Bob       2
2008-04   John      1
2008-05   Bob       2
2008-05   John      1
2008-06   Bob       2
2008-06   John      1
2008-07   Bob       2
2008-07   John      1
2008-08   Bob       3
2008-08   John      1
…         …         …

I know I've done it before, but it was a long time ago and I remember it being messy. Thanks in advance!

Upvotes: 1

Views: 190

Answers (2)

Ajay2707
Ajay2707

Reputation: 5798

This is very logical question, finally I created the sql which gives the desired result.. I verified every date and month count and its all ok.

Declare @t table (ID  int, Reference  varchar(50), Manager varchar(50),LeaseFirstStart datetime,LeaseStop datetime)
insert into @t 
values
(1,'KLEIN','John','2008-04-02 00:00:00.000','2010-04-01 00:00:00.000'),
(2,'HAWKER','John','2008-12-18 00:00:00.000','2010-09-17 00:00:00.000'),
(3,'SLEEP','Bob','2008-01-23 00:00:00.000','2009-01-22 00:00:00.000'),
(4,'CODD','Bob','2009-08-03 00:00:00.000','2010-08-02 00:00:00.000'),
(5,'ALLEN','Bob','2008-02-28 00:00:00.000','2009-07-31 00:00:00.000')


declare @lowerdate datetime , @currentdt datetime 
select @lowerdate = min(leasefirststart), @currentdt= max(leasestop) from @t

;with cte as
(   
    select firstday,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, FirstDay) + 1, 0)) Lastday, mng from
    ( select dateadd(m,datediff(m,0,@lowerdate)+v.number,0) as FirstDay     
      From master..spt_values v
        Where v.type='P' and v.number between 0 and datediff(m, @lowerdate, @currentdt) 
    ) as a  
    , (select distinct manager mng from @t ) as b
)

select (convert(varchar,datepart (yyyy,FirstDay )) + '-' + convert(varchar, MONTH(FirstDay ))) MonthAndYear ,mng as mng , count( manager ) cnt
from cte 
left join @t on 
( 
    firstday between LeaseFirstStart and LeaseStop 
    or 
    Lastday between LeaseFirstStart and LeaseStop 
) and cte.mng = Manager
group by firstday, mng
order by FirstDay

Upvotes: 0

sarath
sarath

Reputation: 314

select sum   (no) as no,datet from  ( SELECT  COUNT   (*) as no ,(convert(varchar,datepart (yyyy,[ Start] )) + '-' + convert(varchar, MONTH([ Start] ))) as datet
FROM <tbl>
GROUP BY (convert(varchar,datepart (yyyy,[ Start] )) + '-' + convert(varchar, MONTH([ Start] )))
union SELECT  COUNT   (*) as no ,(convert(varchar,datepart (yyyy,[ End] )) + '-' + convert(varchar, MONTH([ End] ))) as datet
FROM <tbl>
GROUP BY (convert(varchar,datepart (yyyy,[ End] )) + '-' + convert(varchar,   MONTH([ End] )) ) ) t

Upvotes: 1

Related Questions