SQL Query Summary -

Question

I'm always trying to write the most performant SQL queries, but time and again I run into a report request where I feel like I shouldn't be the first person ever that needed to run such a query and there's probably a better method to generate the data set that I'm looking for.

This pertains to SQL Server 2012.

Consider this table of data, called Sales

In this scenario, a Sale has a start date and an end date. The end date may be null because the customer is still receiving service (e.g. think of a subscription based product/service). You'll notice in the data above, that 3 of the customers have stopped service and 5 are still receiving service.

What would be the best query(s) to write to turn the below into something like this:

*obviously the data sets are different, but this second image as an example.

The "Active" status in the result would be if a customer's StartDate is within OR before the month column AND the customer's EndDate is null or after the month column.

-This could be done with a ton of inline selects (which would be horrible). -Maybe this could be done with T-SQL's PIVOT, but I'm unclear if it's possible to include the StartDate/EndDate logic above, how to (if possible) and if it would be performant (if possible)

Thoughts? Ideas? Examples? THANK YOU!

Answer 1

If you are looking for a Dynamic Pivot, consider the following

Create Some Sample Data

--Drop Table #YourTable
Create Table #YourTable (CustomerID int,StartDate date,EndDate date,SalesPersonID int,ServicePalnID int,DivisionID int)
Insert Into #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')) 
                                    From (
                                            Select Top (DateDiff(MONTH,@Date1,@Date2)+1) 
                                                   D=DateAdd(MONTH,-1+Row_Number() Over (Order By Number),@Date1) 
                                             From  master..spt_values
                                          ) A 
                                     For XML Path('')),1,1,'') 
Select  @SQL = '
Select [YAxis] as [Division],' + @SQL + '
From (
        Select YAxis = concat(''Division '',A.DivisionID)
              ,XAxis = format(D,''MMM yyyy'')
              ,Value = 1
         From  #YourTable A
         Join (
                Select Top (DateDiff(MONTH,'''+concat('',@Date1)+''','''+concat('',@Date2)+''')+1) 
                       D=DateAdd(MONTH,-1+Row_Number() Over (Order By Number),'''+concat('',@Date1)+''') 
                 From  master..spt_values
              ) B
           on D between DateFromParts(Year(StartDate),month(StartDate),1) and EOMonth(IsNull(EndDate,GetDate()))
     ) A
 Pivot (sum(Value) For [XAxis] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns