sql server - SQL Query Summary - -

i'm trying write performant sql queries, time , again run report request feel shouldn't first person ever needed run such query , there's better method generate data set i'm looking for.

this pertains sql server 2012.

consider table of data, called sales

in scenario, sale has start date , end date. end date may null because customer still receiving service (e.g. think of subscription based product/service). you'll notice in data above, 3 of customers have stopped service , 5 still receiving service.

what best query(s) write turn below this:

*obviously data sets different, second image example.

the "active" status in result if customer's startdate within or before month column , customer's enddate null or after month column.

-this done ton of inline selects (which horrible). -maybe done t-sql's pivot, i'm unclear if it's possible include startdate/enddate logic above, how (if possible) , if performant (if possible)

thoughts? ideas? examples? thank you!

if looking dynamic pivot, consider following

create sample data

--drop table #yourtable create table #yourtable (customerid int,startdate date,enddate date,salespersonid int,servicepalnid int,divisionid int) insert #yourtable values (1,'2017-01-01','2017-02-06',1,5,1), (2,'2017-01-01',null        ,1,5,1), (3,'2017-02-04',null        ,1,5,1), (4,'2017-02-05','2017-04-05',1,5,2), (5,'2017-06-06',null        ,2,6,2), (6,'2017-03-26','2017-04-03',2,6,2), (7,'2017-04-01',null        ,2,6,3), (8,'2017-04-04',null        ,3,6,3) 

the dynamic query

declare @date1 date = '2017-01-01' declare @date2 date = getdate()  declare @sql varchar(max) = stuff((select ',' + quotename(format(d,'mmm yyyy'))                                      (                                             select top (datediff(month,@date1,@date2)+1)                                                     d=dateadd(month,-1+row_number() on (order number),@date1)                                                master..spt_values                                           )                                       xml path('')),1,1,'')  select  @sql = ' select [yaxis] [division],' + @sql + ' (         select yaxis = concat(''division '',a.divisionid)               ,xaxis = format(d,''mmm yyyy'')               ,value = 1           #yourtable          join (                 select top (datediff(month,'''+concat('',@date1)+''','''+concat('',@date2)+''')+1)                         d=dateadd(month,-1+row_number() on (order number),'''+concat('',@date1)+''')                    master..spt_values               ) b            on d between datefromparts(year(startdate),month(startdate),1) , eomonth(isnull(enddate,getdate()))      )  pivot (sum(value) [xaxis] in (' + @sql + ') ) p' exec(@sql); 

returns