--> 测试数据: @A
declare @A table (billdate datetime,billno int,inputtime datetime,companyID int,prodID int,qty int)
insert into @A
select '2008-7-1',10001,'2008-7-1 12:00:51',101,1,100 union all
select '2008-7-2',10002,'2008-7-2 11:01:06',101,1,49 union all
select '2008-7-2',10003,'2008-7-2 12:01:27',101,1,43 union all
select '2008-7-3',10004,'2008-7-3 12:01:55',101,1,230
--> 测试数据: @B
declare @B table (billdate datetime,companyID int,prodID int,qty int)
insert into @B
select '2008-7-1',101,1,20 union all
select '2008-7-2',101,1,40 union all
select '2008-7-3',101,1,100
select a.billdate,a.billno,a.companyid,a.prodid,
a.qty,isnull(b.qty,0) from @A a left join @b b
on a.billdate=b.billdate
and not exists(select 1 from @a where billdate=a.billdate and inputtime>a.inputtime)
-->生成测试数据
declare @A采购表 table([billdate] Datetime,[billno] int,[inputtime] datetime,[companyID] int,[prodID] int,[qty] int)
Insert @A采购表
select '2008-7-1',10001,N'2008-7-1 12:00:51',101,1,100 union all
select '2008-7-2',10002,N'2008-7-2 11:01:06',101,1,49 union all
select '2008-7-2',10003,N'2008-7-2 12:01:27',101,1,43 union all
select '2008-7-3',10004,N'2008-7-3 12:01:55',101,1,230
--Select * from @A采购表
-->生成测试数据
declare @B销售汇总表 table([billdate] Datetime,[companyID] int,[prodID] int,[qty] int)
Insert @B销售汇总表
select '2008-7-1',101,1,20 union all
select '2008-7-2',101,1,40 union all
select '2008-7-3',101,1,100
--Select * from @B销售汇总表
SELECT * FROM (
SELECT A.[billdate],A.[billno],A.[inputtime],A.[companyID],A.[prodID],A.[QTY] AS QTY_A,B.[QTY] AS QTY_B FROM @A采购表 A
LEFT JOIN @B销售汇总表 B ON B.[billdate] =A.[billdate] AND B.[companyID] =A.[companyID] AND B.[prodID]= A.[prodID]
WHERE NOT EXISTS(
SELECT 1 FROM @A采购表 T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID]
AND T.[inputtime]>A.[inputtime]
)
UNION ALL
SELECT A.[billdate],A.[billno],A.[inputtime],A.[companyID],A.[prodID],A.[QTY], 0 FROM @A采购表 A
WHERE EXISTS(SELECT 1 FROM @A采购表 T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID]
AND T.[inputtime]>A.[inputtime])
) T
ORDER BY 1,2
/*
billdate billno inputtime companyID prodID QTY_A QTY_B
----------------------- ----------- ----------------------- ----------- ----------- ----------- -----------
2008-07-01 00:00:00.000 10001 2008-07-01 12:00:51.000 101 1 100 20
2008-07-02 00:00:00.000 10002 2008-07-02 11:01:06.000 101 1 49 0
2008-07-02 00:00:00.000 10003 2008-07-02 12:01:27.000 101 1 43 40
2008-07-03 00:00:00.000 10004 2008-07-03 12:01:55.000 101 1 230 100
*/
declare @A table (billdate datetime,billno int,inputtime datetime,companyID int,prodID int,qty int)
insert into @A
select '2008-7-1',10001,'2008-7-1 12:00:51',101,1,100 union all
select '2008-7-2',10002,'2008-7-2 11:01:06',101,1,49 union all
select '2008-7-2',10003,'2008-7-2 12:01:27',101,1,43 union all
select '2008-7-5',10004,'2008-7-3 12:01:55',101,1,230
--> 测试数据: @B
declare @B table (billdate datetime,companyID int,prodID int,qty int)
insert into @B
select '2008-7-1',101,1,20 union all
select '2008-7-2',101,1,40 union all
select '2008-7-3',101,1,100 union all
select '2008-7-4',101,1,66 union all
select '2008-7-6',101,1,57
select isnull(A.billdate,b.billdate) billdate,A.billno,isnull(a.companyID,b.companyid),isnull(a.prodID,b.prodid),isnull(A.qty,0),isnull(B.qty,0) from @a a
full join @b b
on a.billdate=b.billdate and a.prodid=b.prodid
order by billdate
select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,isnull(b.qty,0) as b_qty
from @a a
full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID
WHERE NOT EXISTS(
SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID]
AND T.[inputtime]>A.[inputtime])
union all
select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,0
from @a a
full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID
WHERE EXISTS(SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID]
AND T.[inputtime]>A.[inputtime])
order by billdate,inputtime
select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,isnull(b.qty,0) as b_qty
from @a a
full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID
WHERE NOT EXISTS(
SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID]
AND T.[inputtime]>A.[inputtime])
union all
select coalesce(a.billdate,b.billdate) as billdate,a.billno,inputtime,coalesce(a.companyID,b.companyID) as companyID,coalesce(a.prodID,b.prodID),isnull(a.qty,0) as a_qty,0
from @a a
full outer join @b b on a.billdate=b.billdate and a.companyID=b.companyID and a.prodID=b.prodID
WHERE EXISTS(SELECT 1 FROM @A T WHERE T.[billdate] =A.[billdate] AND T.[companyID] =A.[companyID] AND T.[prodID]= A.[prodID]
AND T.[inputtime]>A.[inputtime])
order by billdate,inputtime
