SQL:从开始日期到结束日期重复记录

Question

我有以下数据结构

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

我需要从提交发票之日到付款之日重复我的数据。像这样;

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

如何为此场景创建 SQL 脚本

Answer 1

尝试这个:

  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