扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
作者:赛迪网 李丝 来源:天新网 2008年3月22日
关键字: 数据库 Mssql SQL Server SQL
在本页阅读全文(共2页)
case when KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),
GETDATE(),111)+"/"+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+"/01")
then KSSJ else convert(smalldatetime,CONVERT(NVARCHAR(4),
GETDATE(),111)+"/"+CONVERT(NVARCHAR(2),
MONTH(GETDATE()))+"/01") end as ST,
case when JSSJ
from CQ_KG
where KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),
111)+"/"+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+"/01")
) as Tmp02
)AS TMP31
GROUP BY ACCOUNT_ID
) AS TMP0032
WHERE KGT>72 --三天時間
) AS TMP0033
LEFT JOIN RES_USER
ON TMP0033.ACCOUNT_ID=RES_USER.ACCOUNT_ID
/**//*- RES_USER 表中的STATUS
0 表示 在職
1 崗位除名狀態中
2 離職
*/
UPDATE RES_USER
SET RES_USER.STATUS=2
FROM RES_USER,CQ_GWHF
WHERE RES_USER.ACCOUNT_ID=CQ_GWHF.ACCOUNT_ID AND
DATEDIFF(DAY,CQ_GWHF.CMRQ,GETDATE())>=15 AND CQ_GWHF.STATUS=
0 --崗位除名 離職
UPDATE CQ_GWHF
SET STATUS=1
WHERE DATEDIFF(DAY,HFRQ,GETDATE())=0 AND STATUS=0
UPDATE RES_USER
SET RES_USER.STATUS=0
FROM RES_USER,CQ_GWHF
WHERE RES_USER.ACCOUNT_ID=CQ_GWHF.ACCOUNT_ID AND
DATEDIFF(DAY,CQ_GWHF.HFRQ,GETDATE())=
0 AND CQ_GWHF.STATUS=1 --崗位恢復
UPDATE RES_USER --新進
SET RES_USER.CBID=CQ_XJ.XCB,RES_USER.BMID=
CQ_XJ.XBM,RES_USER.KBID=CQ_XJ.XKB,
RES_USER.XBID=CQ_XJ.XXB,
RES_USER.ZW=CQ_XJ.XZW,RES_USER.BC=CQ_XJ.XBC
FROM RES_USER,CQ_XJ
WHERE RES_USER.BMID="NA" AND RES_USER.ACCOUNT_ID=CQ_XJ.ACCOUNT_ID AND
DATEDIFF(DAY,CQ_XJ.SXRQ,GETDATE())>=0
DELETE FROM CQ_XJ WHERE ACCOUNT_ID IN
( SELECT ACCOUNT_ID FROM RES_USER )
AND DATEDIFF(DAY,CQ_XJ.SXRQ,GETDATE())>=0
INSERT INTO RES_USER(ACCOUNT_ID,FULL_NAME,
CBID,BMID,KBID,XBID,ZW,BC)
SELECT ACCOUNT_ID,FULL_NAME,XCB,XBM,XKB,XXB,XZW,XBC
FROM CQ_XJ
WHERE DATEDIFF(DAY,CQ_XJ.SXRQ,GETDATE())>=0
DELETE FROM CQ_XJ WHERE DATEDIFF(DAY,CQ_XJ.SXRQ,GETDATE())>=0
INSERT INTO MFDREPORT(ACCOUNT_ID,FULL_NAME,
CBID,BMID,KBID,XBID,BC,RQ)
SELECT ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC,
CONVERT(NVARCHAR(10),
GETDATE(),111) AS RQ
FROM RES_USER
WHERE BMID<>"NA" AND STATUS<>2
GO
如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。