SQL: Repeat records from startdate to enddate

Question

I have the following data structure

Customer | Order | Date       | Amount | TransactionType
AABB     | AB01  | 2012-06-01 | 3000   | Invoiced
AABB     | AB01  | 2012-06-05 | 3000   | Payment

I need my data to be repeated from the day the invoice was presented to the day the payment was made. Like this;

Customer|Order|Date      | AmountDue|AmountPaid|DatePaid  |TransactionType
AABB    |AB01 |2012-06-01| 3000     |NULL      |2012-06-05|Invoiced
AABB    |AB01 |2012-06-02| 3000     |NULL      |2012-06-05|Invoiced
AABB    |AB01 |2012-06-03| 3000     |NULL      |2012-06-05|Invoiced
AABB    |AB01 |2012-06-04| 3000     |NULL      |2012-06-05|Invoiced
AABB    |AB01 |2012-06-05| 3000     |3000      |2012-06-05|Payment

How can I create a SQL script for this scenario

Answer 1

First, you need to summarize the data you have to get the invoice date and payment date. Then, you can generate a sequence for the additional records. One way to do this is with recursive CTEs. However, I typically do something like the following:

Customer | Order | Date | Amount | TransactionType

select Customer, Order,
       dateadd(d seqnum, InvoiceDate) as Date,
       InvoiceAmount as AmountDue,
       PaymentAmount as AmountPaid,
       PaymentDate as DatePaid,
       (case when dateadd(d seqnum, InvoiceDate) <> PaymentDate then 'Invoice' else 'Payment' end) as TransactionType
from (select customer, order,
             max(case when TransactionType = 'Invoice' then amount end) as InvoiceAmount,
             max(case when TransactionType = 'Payment' then amountend) as PaymentAmount
             max(case when TransactionType = 'Invoice' then date end) as InvoiceDate,
             max(case when TransactionType = 'Payment' then date end) as PaymentDate
      from t
      group by customer, order
     ) t join
     (select row_number() over (order by (select NULL)) - 1 as seqnum
      from INFORMATION_SCHEMA.Columns
     ) seq
     on dateadd(d, 1, InvoiceDate, seqnum) <= PaymentDate

This is assuming that you have one invoice record and one payment record. It also assumes the number of days is not that large. The use of InformationSchema.Columns is just to generate a sequence.

Answer 2

Here's a general SQL version, for RDBMSs that don't have some of the fancier features. Has the additional benefit of also listing accounts that haven't been paid for yet. It does assume you have a calendar file available (real or temporary)

SELECT history.customer, history.order, calendar.calendar_date,
       CASE WHEN history.transaction_type = 'Invoiced'
            THEN history.amount END as amount_due,
       CASE WHEN history.transaction_type = 'Payment'
            THEN history.amount END as amount_paid,
       history.transaction_type
FROM Transaction_History as history
JOIN Calendar
ON calendar.calendar_date < CURRENT_DATE + 1 DAY
AND ((calendar.calendar_date >= history.date 
      AND history.transaction_type = 'Invoiced')
     OR (calendar.calendar_date = history.date
         AND history.transaction_type = 'Payment'))
LEFT JOIN Transaction_History as exclusion
ON exclusion.customer = history.customer
AND exclusion.order = history.order
AND exclusion.transaction_type = 'Payment'
AND history.transaction_type = 'Invoiced'
AND exclusion.date <= calendar.calendar_date
WHERE exclusion.customer IS NULL

Tested on my system (DB2) - SQLFiddle seems to be down.

Answer 3

Ok, I know that OP has been yet selected a solution. But I post my own solution with a row generator. I think that is an elegant way to do this. Here for the future:

Fist, Create the table an populate values:

create table #t (
Customer char(4), [Order] char(4), [Date] date, Amount money,
TransactionType varchar(50)
)
insert into #t values 
( 'AABB','AB01','2012-06-01',3000,'Invoiced'),
( 'AABB','AB01','2012-06-05',3000,'Payment');

Here, the row generator and the query it self:

declare @fromdate date
set @fromdate = '1/1/2001'
;WITH 
Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
all_dates as (
  SELECT 
     dateadd( day, n , @fromDate) as [date]
  FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
  FROM Nbrs ) D ( n )
),
all_intervals as (
  select 
     t1.customer, t1.[order], t1.[date] as date_from, t1.amount as amount_due,
     t2.[date] as date_paid
  from
     #t t1 inner join
     #t t2 on t2.TransactionType = 'Payment'
              and t1.customer = t2.customer and t1.[order] = t2.[order]
  where t1.TransactionType = 'Invoiced'
  )
select a.*, d.[date]
from all_intervals a
inner join all_dates d 
   on d.[date] between a.date_from and a.date_paid

Results:

customer order date_from     amount_due date_paid     date          
-------- ----- ------------- ---------- ------------- ------------- 
AABB     AB01  2012-06-01 00:00:003000       2012-06-05 00:00:002012-06-01 00:00:00
AABB     AB01  2012-06-01 00:00:003000       2012-06-05 00:00:002012-06-02 00:00:00
AABB     AB01  2012-06-01 00:00:003000       2012-06-05 00:00:002012-06-03 00:00:00
AABB     AB01  2012-06-01 00:00:003000       2012-06-05 00:00:002012-06-04 00:00:00
AABB     AB01  2012-06-01 00:00:003000       2012-06-05 00:00:002012-06-05 00:00:00

You can try it.

Answer 4

Try This:

  create table cust1
       (
          Customer varchar(20),
          Orders varchar(10),
          Date datetime,
          Amount float,
          TransactionType varchar(50)
        )
        INSERT INTO cust1
        VALUES('AABB','AB01','2012-06-01',3000,'Invoiced'),
        ('AABB','AB01','2012-06-05',3000,'Payment')

           DECLARE @stDate datetime,@eddate datetime
select @stDate =MIN(date),@eddate =MAX(date) from cust1

select c1.Customer,c1.Orders,DATEADD(DD,number,@stDate) as [date],
amount amountDue,
CASE WHEN (DATEADD(DD,number,@stDate)) = @eddate then amount else null end as amountPaid,
@eddate as datepaid,
CASE WHEN (DATEADD(DD,number,@stDate)) <> @eddate then 'Invoiced' else 'Payment' end as TransactionType

from master..spt_values p inner join cust1 c1
on right(cast(c1.date as DATE),2) <= (case when p.number = 0 then 1 else p.number end)
where type='p'and DATEADD(DD,number,@stDate) <=@eddate

Answer 5

Here's one try that assumes there are two rows, that payment has been made, that both an invoiced and payment row exists, and that you are trying to solve for one specific customer / order.

DECLARE @t TABLE
(
  Customer VARCHAR(32), 
  [Order] VARCHAR(32), 
  [Date] DATE, 
  Amount INT, 
  TransactionType VARCHAR(32)
);

INSERT @t VALUES
('AABB','AB01','2012-06-01',3000,'Invoiced'),
('AABB','AB01','2012-06-05',3000,'Payment');


;WITH t AS (SELECT * FROM @t AS t WHERE t.Customer = 'AABB' AND t.[Order] = 'AB01'),
rng AS (SELECT s = MIN([Date]), e = MAX([Date]) FROM t), 
n AS (SELECT TOP (DATEDIFF(DAY, (SELECT s FROM rng), (SELECT e FROM rng))) 
      n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects
)
SELECT t.Customer, t.[Order], [Date] = DATEADD(DAY, n.n, t.[Date]), 
    AmountDue = Amount, AmountPaid = NULL, DatePaid = (SELECT e FROM rng), 
    t.TransactionType
FROM n CROSS JOIN t WHERE t.[Date] < (SELECT e FROM rng)
UNION ALL 
SELECT t.Customer, t.[Order], t.[Date], 
    AmountDue = NULL, AmountPaid = Amount, DatePaid = t.[Date], 
    t.TransactionType
FROM t WHERE t.[Date] = (SELECT e FROM rng);