user5963760 user5963760 - 3 months ago 11
SQL Question

Available Year-Months Between

I have a listing of products each product has a [created date] and an [end date](this is null if it has not ended). I am trying to come up with a way to view the number of available products for each 'YYYY-MM'. I need this to go back in time as well as look at current time periods. Any thouhts?

Desired Results:

enter image description here

Raw Data:

enter image description here

Answer

Try

-- parameters
declare 
   @sy int = 2014,
   @sm int = 5,
   @nm int = 30;

with months as (
    -- use tally table instead, if present
    select top(@nm) n = @sy*12 + @sm  - 1  + row_number() over (order by (select null))
    from sys.all_objects
)
select year=(m.n-1)/12, month=(m.n-1)%12 + 1, cnt = count(*) 
from (
     -- sample data
     values (1, cast ('20160105' as date), cast(null as date))
           ,(2, cast ('20151115' as date), cast('20160302' as date))
    ) t (id, startDate, endDate)
join months m ON m.n between year(startDate)*12 + month(startDate) 
            and coalesce (year(endDate)*12 + month(endDate), 9999999) 
group by m.n
order by m.n;

If you need to show months with no data too, reverse the join order

-- parameters
declare 
   @sy int = 2014,
   @sm int = 5,
   @nm int = 30;

with months as (
    -- use tally table instead, if present
    select top(@nm) n = @sy*12 + @sm  - 1  + row_number() over (order by (select null))
    from sys.all_objects
)
select year=(m.n-1)/12, month=(m.n-1)%12 + 1, cnt = count(t.id) 
from  months m
left join (
     -- sample data kindly provided by Slava Murygin
 values 
   (1,cast('2016-05-05' as date),cast('2016-07-23' as date)),
   (2,'2015-04-05',Null),
   (3,'2016-05-05',Null),
   (4,'2016-01-01',Null),
   (5,'2015-02-12','2016-07-23'),
   (6,'2016-03-05',Null),
   (7,'2013-04-15','2016-05-05'),
   (8,'2016-04-10',Null),
   (9,'2015-09-09',Null),
   (10,'2012-08-08','2016-07-23'),
   (11,'2016-06-06',Null),
   (12,'2016-07-07',Null)
    ) t (id, startDate, endDate)
    on m.n between year(startDate)*12 + month(startDate) 
            and coalesce (year(endDate)*12 + month(endDate), 9999999) 
group by m.n
order by m.n;

Result

year    month   cnt
2014    5   2
2014    6   2
2014    7   2
2014    8   2
2014    9   2
2014    10  2
2014    11  2
2014    12  2
2015    1   2
2015    2   3
2015    3   3
2015    4   4
2015    5   4
2015    6   4
2015    7   4
2015    8   4
2015    9   5
2015    10  5
2015    11  5
2015    12  5
2016    1   6
2016    2   6
2016    3   7
2016    4   8
2016    5   10
2016    6   10
2016    7   11
2016    8   8
2016    9   8
2016    10  8
Comments