求一条能做成每天库存剩余的SQL

[从不签到]

表A中的数据为
Item Qty TransDate Type
A001 100.00 2014/06/01 月初
A001 50.00 2014/06/10 每天
A001 -20.00 2015/06/20 每天
B001 200.00 2014/06/01 月初
B001 60.00 2014/06/15 每天

现在想根据上面的数据作成当月每天的数据，也是放在表A之中
处理的时间为7月份
对于A001，1-9号没发生变化，所以就不变 QTY还是100
10-19号，Qty=150 20-30号 Qty=130
Item Qty TransDate Type
A001 100.00 2014/06/01 每日结算
...
A001 100.00 2014/06/09 每日结算
A001 150.00 2014/06/10 每日结算
...
A001 150.00 2014/06/19 每日结算
A001 130.00 2014/06/20 每日结算
...
A001 130.00 2014/06/30 每日结算

现在的做法是循环表A中所有的 Type=每天的数据，
再去取月初的数据
这样效率非常低
11000条数据执行时间大概为23分钟

问各位大牛怎样才能更加有效，快速的插入数据呢？

[子陌红尘]

估计适用SQL Server 2000，全凭印象，无从测试，所以不确定可行否：

select
    x.Item,
    x.TransDate,
    sum(y.Qty) as Qty,
    '每日结算' as Type
from
    (select 
         m.Item,dateadd(d,m.TransDate,n.id-1) as TransDate 
     from 
         A m,sysobjects n 
     where 
         datediff(m,m.TransDate,dateadd(d,m.TransDate,n.id-1))=0) x,
         a y
where
    x.Item=y.Item
    and
    x.TransDate>=y.TransDate
    and
    datediff(m.x.TransDate,y.TransDate)=0
group by
    x.Item,x.TransDate

[子陌红尘]

符号都敲错。。。。。。

select
    x.Item,
    x.TransDate,
    sum(y.Qty) as Qty,
    '每日结算' as Type
from
    (select 
         m.Item,dateadd(d,m.TransDate,n.id-1) as TransDate 
     from 
         A m,sysobjects n 
     where 
         datediff(m,m.TransDate,dateadd(d,m.TransDate,n.id-1))=0) x,
         a y
where
    x.Item=y.Item
    and
    x.TransDate>=y.TransDate
    and
    datediff(m, x.TransDate, y.TransDate)=0
group by
    x.Item,x.TransDate

[Q315054403]

应该从设计上得出每日、每月结余 要全部统计亦可，如果有几十万种物料，这查询够呛的 更多的是设计问题，而非某SQL问题

[从不签到]

我的sql 是2008 没有问题 现在如果 还有 C001 但是没有1号数据的话， 比如 c001 2014/06/12 100 每天 c001 2014/06/22 200 每天 c001的数据就从12号开始，没有前11天的数据 能改进一下更感激不尽了

[jayzhihui]

是不是这样？？？？？？？？？？？？？？？

IF OBJECT_ID('tempdb..#A')>0 DROP TABLE #A
IF OBJECT_ID('tempdb..#t')>0 DROP TABLE #t
GO
create table #A
(
  Item nvarchar(20),
  Qty numeric(16,2),
  TransDate datetime,
  [Type] nvarchar(10)
)

go

insert into #A values
('A001',	'100.00',	'2014/06/01',	N'月初'),	
('A001',	'50.00',	'2014/06/10',	N'每天')	,
('A001',	'-20.00',	'2014/06/20',	N'每天')	,
('B001',	'200.00',	'2014/06/01',	N'月初')	,
('B001',	'60.00',	'2014/06/15',	N'每天')

with cte as
(
	select *,ROW_NUMBER()OVER(PARTITION BY Item order by TransDate)id 
	from #A
)
select *,
case 
	when datediff(day,TransDate,(select TransDate from cte where Item = a.Item and id = a.id+1)) is not null 
	then datediff(day,TransDate,(select TransDate from cte where Item = a.Item and id = a.id+1)) 
	else datediff(day,TransDate,dateadd(day,-1,cast(DATEPART(YEAR,TransDate) as varchar(10))+right('-0'+cast(DATEPART(MM,TransDate)+1 as varchar(10)),3)+'-01' )) 

end nb, 
(select SUM(Qty) from cte where Item = a.Item and id <= a.id ) nqty
into #t
				
			
from cte a



;with cte as
(
select ROW_NUMBER()over(PARTITION by Item order by DATEADD(day,b.number,TransDate)) id,a.Item,a.nqty,newdate=DATEADD(day,b.number,TransDate) from #t a
    join master..spt_values b on b.number< a.nb and b.type = 'P'
)
select * from cte 
union all
select id,Item,nqty,dateadd(day,1,newdate) from cte a
 where id = (select MAX(id) from cte where Item = a.Item)
order by Item,newdate
 

[从不签到]

insert into #A values ('A001', '100.00', '2014/06/01', N'月初'), ('A001', '50.00', '2014/06/10', N'每天') , ('A001', '-20.00', '2014/06/20', N'每天') , ('B001', '200.00', '2014/06/01', N'月初') , ('B001', '60.00', '2014/06/15', N'每天') 到这里没问题，select出来的数据都对 如果再加上 ('C001', '60.00', '2014/06/12', N'每天') 没有月初的数据 结果 C001的数据就是从12号开始了，1-11号的数据没有，想select出来 C001 6/1 0 每日结算 ... C001 6/11 0 每日结算 C001 6/12 60 每日结算 ... C001 6/30 60 每日结算

[从不签到]

表A的数据 Item TransDate Qty TYPE A001 2014-06-01 100.0000000000000000 月初 A001 2014-06-10 50.0000000000000000 每天 A001 2014-06-20 -30.0000000000000000 每天 B001 2014-06-01 200.0000000000000000 月初 C001 2014-06-12 50.0000000000000000 每天 C001 2014-06-22 30.0000000000000000 每天 select出来的结果 A001 B001的都对，就省略 1 C001 50.0000000000000000 2014-06-12 2 C001 50.0000000000000000 2014-06-13 3 C001 50.0000000000000000 2014-06-14 4 C001 50.0000000000000000 2014-06-15 5 C001 50.0000000000000000 2014-06-16 6 C001 50.0000000000000000 2014-06-17 7 C001 50.0000000000000000 2014-06-18 8 C001 50.0000000000000000 2014-06-19 9 C001 50.0000000000000000 2014-06-20 10 C001 50.0000000000000000 2014-06-21 11 C001 80.0000000000000000 2014-06-22 12 C001 80.0000000000000000 2014-06-23 13 C001 80.0000000000000000 2014-06-24 14 C001 80.0000000000000000 2014-06-25 15 C001 80.0000000000000000 2014-06-26 16 C001 80.0000000000000000 2014-06-27 17 C001 80.0000000000000000 2014-06-28 18 C001 80.0000000000000000 2014-06-29 18 C001 80.0000000000000000 2014-06-30

[从不签到]

现在就这么设计的，不可能改变了 只能从SQL优化上着手了

[从不签到]

To libin_ftsafe 执行了一下，提示date overflow 了

[从不签到]

看不懂 libin_ftsafe 您的SQL语句。。。。 我就想根据type=每天，月初的，来自动得到当月每天的数据 需要自动添加， 6月，每个item都要有30条，7月 31条 等等

[还在加载中灬]

;WITH CTE AS(
	SELECT T1.Item,SUM(T2.Qty)Qty,T1.TransDate TransDateStart,'每日结算'[Type]
		,COUNT(T2.Item)RN
	FROM A T1
		JOIN A T2 ON T1.Item=T2.Item AND T1.TransDate>=T2.TransDate
	GROUP BY T1.Item,T1.Qty,T1.TransDate
)
SELECT T3.Item,T3.Qty,DATEADD(DAY,T4.number,TransDateStart)TransDate,T3.[Type] FROM(
	SELECT T1.*
		,ISNULL(T2.TransDateStart,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(T1.TransDateStart),T1.TransDateStart)))TransDateEnd
	FROM CTE T1
		LEFT JOIN CTE T2 ON T1.Item=T2.Item AND T1.RN+1=T2.RN
	)T3
	JOIN master..spt_values T4 ON T4.type='P'AND T4.number<DATEDIFF(DAY,TransDateStart,TransDateEnd)
ORDER BY T3.Item,TransDate

SQL2005+

[从不签到]

非常感谢，成功了