if object_id('tempdb..#') is not null drop table #; go create table # (DOCDATE datetime,KHBM char(8),ITEMCODE char(7), WHSCODE char(3), INQTY int, OUTQTY int); insert # select '2010-3-12','15190001','A200043','W01',1,0 union all select '2010-3-18','15730005','L300276','W01',3,0 union all select '2010-3-18','15730005','L300276','W01',0,2 union all select '2010-3-12','15790005','L300777','W01',3,0 union all select '2010-3-12','15790005','L300777','W01',0,3 union all select '2010-3-16','15790005','L300777','W01',2,0 union all select '2010-3-11','50200003','A200084','W01',0,1 union all select '2010-3-16','50200003','A200084','W01',1,0 union all select '2010-3-6','15750031','A100003','W01',1,0 union all select '2010-3-9','15750031','A100003','W01',0,1 union all select '2010-3-4','15730071','A100005','W01',1,0 union all select '2010-3-14','15730071','A100005','W01',0,1 union all select '2010-3-18','157A0070','A100005','W01',1,0 union all select '2010-3-11','15730031','A100008','W01',2,0 union all select '2010-3-13','15730031','A100008','W01',0,1 union all select '2010-3-6','15750031','A100003','W01',1,0 union all select '2010-3-9','15750031','A100003','W01',0,1 union all select '2010-3-10','15750031','A100003','W01',1,0 union all select '2010-3-15','15750031','A100333','W01',2,0 union all select '2010-3-1','15750031','A100003','W01',2,0 union all select '2010-3-6','15750031','A100003','W01',0,1 union all select '2010-3-9','15750031','A100003','W01',1,0 union all select '2010-3-11','15750031','A100003','W01',0,1;;with t1 as( select *,(select sum(INQTY-OUTQTY) from # where KHBM=t.KHBM and ITEMCODE=t.ITEMCODE and DOCDATE<=t.DOCDATE) [SUM] from # t ) select * from t1 t where INQTY>0 and [SUM]>0 and (select isnull(sum(OUTQTY),0) from t1 where KHBM=t.KHBM and ITEMCODE=t.ITEMCODE and datediff(dd,t.DOCDATE,DOCDATE) between 1 and 7)<[SUM]; /* DOCDATE KHBM ITEMCODE WHSCODE INQTY OUTQTY SUM ----------------------- -------- -------- ------- ----------- ----------- ----------- 2010-03-12 00:00:00.000 15190001 A200043 W01 1 0 1 2010-03-18 00:00:00.000 15730005 L300276 W01 3 0 1 2010-03-16 00:00:00.000 15790005 L300777 W01 2 0 2 2010-03-04 00:00:00.000 15730071 A100005 W01 1 0 1 2010-03-18 00:00:00.000 157A0070 A100005 W01 1 0 1 2010-03-11 00:00:00.000 15730031 A100008 W01 2 0 2 2010-03-10 00:00:00.000 15750031 A100003 W01 1 0 3 2010-03-15 00:00:00.000 15750031 A100333 W01 2 0 2 2010-03-01 00:00:00.000 15750031 A100003 W01 2 0 2 2010-03-09 00:00:00.000 15750031 A100003 W01 1 0 2 */
if object_id('tempdb..#') is not null
drop table #;
go
create table #
(DOCDATE datetime,KHBM char(8),ITEMCODE char(7), WHSCODE char(3), INQTY int, OUTQTY int);
insert #
select '2010-3-12','15190001','A200043','W01',1,0 union all
select '2010-3-18','15730005','L300276','W01',3,0 union all
select '2010-3-18','15730005','L300276','W01',0,2 union all
select '2010-3-12','15790005','L300777','W01',3,0 union all
select '2010-3-12','15790005','L300777','W01',0,3 union all
select '2010-3-16','15790005','L300777','W01',2,0 union all
select '2010-3-11','50200003','A200084','W01',0,1 union all
select '2010-3-16','50200003','A200084','W01',1,0 union all
select '2010-3-6','15750031','A100003','W01',1,0 union all
select '2010-3-9','15750031','A100003','W01',0,1 union all
select '2010-3-4','15730071','A100005','W01',1,0 union all
select '2010-3-14','15730071','A100005','W01',0,1 union all
select '2010-3-18','157A0070','A100005','W01',1,0 union all
select '2010-3-11','15730031','A100008','W01',2,0 union all
select '2010-3-13','15730031','A100008','W01',0,1 union all
select '2010-3-6','15750031','A100003','W01',1,0 union all
select '2010-3-9','15750031','A100003','W01',0,1 union all
select '2010-3-10','15750031','A100003','W01',1,0 union all
select '2010-3-15','15750031','A100333','W01',2,0 union all
select '2010-3-1','15750031','A100003','W01',2,0 union all
select '2010-3-6','15750031','A100003','W01',0,1 union all
select '2010-3-9','15750031','A100003','W01',1,0 union all
select '2010-3-11','15750031','A100003','W01',0,1;;with t1 as(
select *,(select sum(INQTY-OUTQTY) from #
where KHBM=t.KHBM and ITEMCODE=t.ITEMCODE and DOCDATE<=t.DOCDATE) [SUM]
from # t
)
select * from t1 t
where INQTY>0 and [SUM]>0 and (select isnull(sum(OUTQTY),0) from t1
where KHBM=t.KHBM and ITEMCODE=t.ITEMCODE
and datediff(dd,t.DOCDATE,DOCDATE) between 1 and 7)<[SUM];
/*
DOCDATE KHBM ITEMCODE WHSCODE INQTY OUTQTY SUM
----------------------- -------- -------- ------- ----------- ----------- -----------
2010-03-12 00:00:00.000 15190001 A200043 W01 1 0 1
2010-03-18 00:00:00.000 15730005 L300276 W01 3 0 1
2010-03-16 00:00:00.000 15790005 L300777 W01 2 0 2
2010-03-04 00:00:00.000 15730071 A100005 W01 1 0 1
2010-03-18 00:00:00.000 157A0070 A100005 W01 1 0 1
2010-03-11 00:00:00.000 15730031 A100008 W01 2 0 2
2010-03-10 00:00:00.000 15750031 A100003 W01 1 0 3
2010-03-15 00:00:00.000 15750031 A100333 W01 2 0 2
2010-03-01 00:00:00.000 15750031 A100003 W01 2 0 2
2010-03-09 00:00:00.000 15750031 A100003 W01 1 0 2
*/