代码
比这篇新的文章:
Codee#2319
比这篇旧的文章: Codee#2317
作者: devick, 点击113次, 评论(0), 收藏者(0), , 打分:
所有评论，共0条：( 我也来说两句)
比这篇旧的文章: Codee#2317
Codee#2318
语言: SQL, 标签: 无 2009/06/21发布 8个月前更新作者: devick, 点击113次, 评论(0), 收藏者(0), , 打分:
SQL语言: Codee#2318
01 declare Managerdish_declare cursor for
02 SELECT m.id, m.checkdish
03 FROM Managerdish m
04 WHERE monthv=@monthv AND m.checkdish IN (SELECT che.id FROM checkdish che WHERE che.khtype IN('BIG','SMALL'))
05 open Managerdish_declare
06 fetch next from Managerdish_declare into @m_id, @m_checkdish
07 while @@FETCH_STATUS = 0
08 begin
09 SELECT @m_dishpay=sum(u.checkpay), @m_mustpay=sum(u.mustcheckpay), @m_workpay=sum(u.workcheckpay),
10 @m_mustoverpay=SUM(u.mustcheckpay-u.mustcheckpayin),@m_workoverpay=SUM(u.workcheckpay-u.workcheckpayin),
11 @m_incomepay=SUM(u.incomecheckpay)
12 FROM Usercheckrecord u
13 WHERE u.monthv=@monthv AND u.checkdish=@m_checkdish
14
15 update Managerdish set dishpay=@m_dishpay,mustpay=@m_mustpay,mustoverpay=@m_mustoverpay,workpay=@m_workpay,
16 workoverpay=@m_workoverpay,incomepay=@m_incomepay
17 where id=@m_id
18
19 UPDATE Usercheckrecord
20 SET mustoverpay = CASE WHEN mustcomrate<=1 THEN mustcheckpay*mustcomrate ELSE mustcheckpay END,
21 workoverpay = CASE WHEN workcomrate<=1 THEN workcheckpay*workcomrate ELSE workcheckpay END
22 WHERE monthv=@monthv AND checkdish IN (SELECT che.id FROM checkdish che WHERE che.khtype IN('BIG','SMALL'))
23
24 fetch next from Managerdish_declare into @m_id, @m_checkdish
25 end
26 close Managerdish_declare
27 deallocate Managerdish_declare
02 SELECT m.id, m.checkdish
03 FROM Managerdish m
04 WHERE monthv=@monthv AND m.checkdish IN (SELECT che.id FROM checkdish che WHERE che.khtype IN('BIG','SMALL'))
05 open Managerdish_declare
06 fetch next from Managerdish_declare into @m_id, @m_checkdish
07 while @@FETCH_STATUS = 0
08 begin
09 SELECT @m_dishpay=sum(u.checkpay), @m_mustpay=sum(u.mustcheckpay), @m_workpay=sum(u.workcheckpay),
10 @m_mustoverpay=SUM(u.mustcheckpay-u.mustcheckpayin),@m_workoverpay=SUM(u.workcheckpay-u.workcheckpayin),
11 @m_incomepay=SUM(u.incomecheckpay)
12 FROM Usercheckrecord u
13 WHERE u.monthv=@monthv AND u.checkdish=@m_checkdish
14
15 update Managerdish set dishpay=@m_dishpay,mustpay=@m_mustpay,mustoverpay=@m_mustoverpay,workpay=@m_workpay,
16 workoverpay=@m_workoverpay,incomepay=@m_incomepay
17 where id=@m_id
18
19 UPDATE Usercheckrecord
20 SET mustoverpay = CASE WHEN mustcomrate<=1 THEN mustcheckpay*mustcomrate ELSE mustcheckpay END,
21 workoverpay = CASE WHEN workcomrate<=1 THEN workcheckpay*workcomrate ELSE workcheckpay END
22 WHERE monthv=@monthv AND checkdish IN (SELECT che.id FROM checkdish che WHERE che.khtype IN('BIG','SMALL'))
23
24 fetch next from Managerdish_declare into @m_id, @m_checkdish
25 end
26 close Managerdish_declare
27 deallocate Managerdish_declare
所有评论，共0条：( 我也来说两句)