SQL Server 2008 : create data where there is none?

Question

I am struggling to create data where there is no data, I have a query that returns similar data to this:

enter image description here

This table shows that for client 123 we had payments in June, July and December only.

The only notable item in the query is a DATEDIFF between the month opened and MonthPayment (this gives the mnth column).

Now where I’m falling over is I need to create the above columns but for all the months that have passed regardless like below

enter image description here

Ignore the month payment field for the none paying months, this shouldn't return anything!

Here is my answer to a similar question from the last 15 minutes: http://stackoverflow.com/a/29647724/3094533 — Zohar Peled, Apr 15 '15 at 10:37

score 1 · Accepted Answer · edited May 23 '17 at 12:14

What you need to do is connect this table to a list of all values you might want, as in the question pointed to by Zohar Peled. Your case is slightly complicated, since presumably you need to be able to return multiple clients at a time and only want to see data that pertains to that client's start and end range. I've adapted code from a similar answer I posted some time ago, which should show you how this is done.

-- set up some sample data
    DECLARE @MyTable TABLE
      (
        ClientNo INT,
        Collected NUMERIC(5,2),
        MonthPayment DATETIME,
        MonthOpened DATETIME,
        CurrentMonth DATETIME
      )

    INSERT INTO @MyTable 
      (
        ClientNo,
        Collected,
        MonthPayment,
        MonthOpened,
        CurrentMonth
      ) -- note: I'm in the US, so I'm using the US equivalent of the months you asked for
    SELECT 123, 147.25, '7/1/2014', '12/1/2013', '4/1/2015' 
    UNION
    SELECT 123, 40, '12/1/2014', '12/1/2013', '4/1/2015' 
    UNION
    SELECT 123, 50, '6/1/2014', '12/1/2013', '4/1/2015' 

-- create a recursive CTE that contains a list of all months that you could wish to see
    --define start and end limits
        Declare @todate datetime, @fromdate datetime
        Select @fromdate=(SELECT MIN(MonthOpened) FROM @MyTable), @todate=DATEADD(MONTH, 1, GETDATE())
    -- define CTE
    ;With DateSequence( DateValue ) as
    (
        Select @fromdate as DateValue
            union all
        Select dateadd(MONTH, 1, DateValue)
            from DateSequence
            where DateValue < @todate
    )

--select result
    SELECT 
        ClientStartEnd.ClientNo,
        ISNULL(MyTable.Collected, 0.00) AS Collected,
        DateSequence.DateValue AS MonthPayment,
        ClientStartEnd.MonthOpened,
        DATEDIFF(MONTH, ClientStartEnd.MonthOpened, DateSequence.DateValue) + 1 AS Mnth,
        ClientStartEnd.CurrentMonth
    FROM 
        DateSequence 
         INNER JOIN 
          (
            SELECT DISTINCT 
                ClientNo, 
                MonthOpened, 
                CurrentMonth
            FROM @MyTable 
          ) ClientStartEnd ON 
            DateSequence.DateValue BETWEEN 
                ClientStartEnd.MonthOpened AND 
                ClientStartEnd.CurrentMonth
         LEFT JOIN 
        @MyTable MyTable ON 
            ClientStartEnd.ClientNo = MyTable.ClientNo AND
            DateSequence.DateValue = MyTable.MonthPayment 
    OPTION (MaxRecursion 0)

thanks, managed to figure out what i needed to do after sleeping on it, however this solution would also have worked So i'm marking as answered! thanks for the repsonce. — GPH, Apr 16 '15 at 08:58

SQL Server 2008 : create data where there is none?

1 Answers1