--从人事库中查出在职的,薪卡号重复的记录
select FGongKaHao from bas_Employee
where FZaiZhi=0
group by FGongKaHao having count(FGongKaHao)>1
--找出进仓从表的出仓数量和出仓记录中的出仓数量总合不一样的记录
SELECT * FROM
(
select
ISNULL(
(
--计算出仓记录里的出仓数量总合
SELECT SUM(FShuLiang)
FROM sto_ChuCangJL f_ccjl
WHERE f_d.FKey=f_ccjl.FJinCangKey
)
,0) AS FChuCangSLEX, f_d.FChuCangSL AS FChuCangSL2, f_d.*
FROM
(
select f_m.FFormDate, f_m.FBianHao,
f_d.*
from sto_inStore_d f_d
INNER JOIN sto_InStore_m f_m on f_m.FFormKey = f_d.FFormKey
)f_d
)__v
where FChuCangSL<>FChuCangSLEX
order by FBianHao
--进仓从表的出仓数量和出仓记录中的出仓数量总合不一样的记录,更新一至
UPDATE f_d
SET f_d.FChuCangSL=__v.FChuCangSLEX
FROM sto_inStore_d f_d
INNER JOIN
(
select
ISNULL(
(
--计算出仓记录里的出仓数量总合
SELECT SUM(FShuLiang)
FROM sto_ChuCangJL f_ccjl
WHERE f_d.FKey=f_ccjl.FJinCangKey
)
,0) AS FChuCangSLEX, f_d.FChuCangSL AS FChuCangSL2, f_d.*
FROM
(
select f_m.FFormDate, f_m.FBianHao,
f_d.*
from sto_inStore_d f_d
INNER JOIN sto_InStore_m f_m on f_m.FFormKey = f_d.FFormKey
)f_d
)__v ON f_d.FKey=__v.FKey
where __v.FChuCangSL<>__v.FChuCangSLEX
--使用联连的方式来更新一个表
--更新禁止使用的模块
UPDATE #tmpModules SET IsEnable=0
FROM #tmpModules f_tb INNER JOIN sys_cm_ModuleFuncs f_slv ON f_tb.FModuleFuncIndex=f_slv.FIndex
WHERE f_slv.FDisable=1
--更新班次
SELECT @ExeStr=
'UPDATE f_tb
SET FBanCiBH=
(SELECT ['+@SDay+'] FROM hrm_EmpBanCi
WHERE FNianYue='''+@NianYue+''' AND FZhiYuanKey=f_tb.FZhiYuanKey)
FROM hrm_EmpKaoQin f_tb
WHERE f_tb.FApproved<>1
AND f_tb.FKaoQinRQ='''+cast(@mm_Date as varchar(24))+''''
EXEC (@ExeStr)
