Counting Active records for each month based on date range in 2 columns

• keithwms

  SSC Veteran

  Points: 210

  More actions

  May 1, 2018 at 5:16 pm

  #344535

  I am trying to get counts of active subscriptions for all products we have in a subscription table. This table has the columns, among other things, for ID, ProductCode, StartDate, ThroughDate. So what I need to do it to count each record for each month between startdate and throughdate columns.

  What I'm ultimately trying to do is to create a report that shows, probably in SSRS in the end.

  

  Basically what I need to do is to count a record for each month year between the date range. So if i have records
  

  I would need to be able to count for ID 12345, with Product code SubCode1 as active in each of months
  2010-05
  2010-06
  2010-07
  etc through
  2011-04

  and for SubCode02 in each of months
  2018-01
  2018-02
  2018-03
  2018-04
  etc. through
  2018-12

  I'm having issue how to count for each month/year combination since i only have 2 dates. I was thinking of setting up a temp table or view that would do it but not sure how to come up with the months/years between the start and end date.

  Can anyone give me some help on how I might accomplish this?

  Thank you,
  Keith

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  May 1, 2018 at 8:23 pm

  #1988882

  Please see the following article.  It breaks it down to make it real simple.  Although the article is title for the "previous month", it applies to any date range you may need.
  http://www.sqlservercentral.com/articles/T-SQL/105968/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  May 1, 2018 at 8:25 pm

  #1988883

  For generating all dates between start and end dates you could do the following.

  1. set up a cte that generates numbers from 1.. to a reasonably large value. you can use the tally table, or any table with sufficiently large number of rows as rank
  2. perform a conditional join between the subscription table and step 1 to fetch those records which fall in the date range of the end_date and start_date+rank_generated_from_step_1

  
create table subscription(id int, productcode varchar(50),start_date datetime,end_date datetime);

  insert into subscription values(12345,'SubCode1','1-May-2010','30-Apr-2011');
insert into subscription values(12345,'SubCode2','1-Jan-2018','31-Dec-2018');
insert into subscription values(5689,'SubCode1','1-Oct-2015','30-Nov-2016');

  with dates
as (select row_number() over(order by (select null)) as rnk
   from information_schema.columns a
   cross join information_schema.columns b
   )
select cast(dateadd(dd,b.rnk,a.start_date) as date) as each_date
   ,a.productcode
   ,cast(a.start_date as date)
   ,cast(a.end_date as date)
from subscription a
join dates b
  on dateadd(dd,b.rnk,a.start_date)<=a.end_date
order by a.productcode,1; 
 


  Once you get this done then the rest of the query can be done using pivot to transpose rows to columns