替一个SQL语句
日期：2014-05-17 浏览次数：20418 次
为一个SQL语句
SELECT
Element,
Item,
(SELECT Company FROM dbo.t_ClientInfo WHERE dbo.t_ClientInfo.ClientOID = {0}) AS Company,
trainingoid,
SOPNumber,
employeeoid,
UserName,
LastDate,
NextDue,
logoid
FROM
(SELECT
'Operator Training' AS Element,
'SOP Training' AS Item,
l.trainingoid,
CAST(s.SOPNumber AS varchar(100)) AS SOPNumber,
l.employeeoid,
u.UserName,
l.lastDate AS LastDate,
l.nextDueDate AS NextDue,
l.logoid
FROM
(SELECT
a.logoid,
a.trainingoid,
a.employeeoid,
a.lastDate,
a.nextDueDate,
a.fileName
FROM dbo.OPERATOR_SOP_LOG_ENTRY AS a
INNER JOIN
(SELECT
trainingoid,
employeeoid,
MAX(lastDate) AS mdate
FROM dbo.OPERATOR_SOP_LOG_ENTRY
GROUP BY trainingoid, employeeoid) AS b
ON a.trainingoid = b.trainingoid AND a.lastDate = b.mdate) AS l
LEFT OUTER JOIN dbo.SOP AS s ON l.trainingoid = s.SOPOID
LEFT OUTER JOIN dbo.t_UserInfo AS u ON l.employeeoid = u.UserOID) AS S
WHERE(CAST(MONTH(GETDATE()) AS varchar(2)) + '/' + CAST(DAY(GETDATE()) AS varchar(2)) + '/' + CAST(YEAR(GETDATE()) AS varchar(4)) BETWEEN DATEADD(day, - 7, CAST(MONTH(NextDue) AS varchar(2)) + '/' + CAST(DAY(NextDue) AS varchar(2)) + '/' + CAST(YEAR(NextDue) AS varchar(4))) AND CAST(MONTH(NextDue) AS varchar(2)) + '/' + CAST(DAY(NextDue) AS varchar(2)) + '/' + CAST(YEAR(NextDue) AS varchar(4)))
UNION ALL
SELECT
Element,
Item,
(SELECT Company FROM dbo.t_ClientInfo WHERE dbo.t_ClientInfo.ClientOID = {0}) AS Company,
trainingoid,
trainingType,
employeeoid,
UserName,
LastDate,
NextDue,
logoid
FROM
(SELECT
'Operator Training' AS Element,
'Additional Training' AS Item,
l.trainingoid,
t.trainingType,
l.employeeoid,
u.UserName,
l.lastDate AS LastDate,
l.nextDueDate AS NextDue,
l.logoid
FROM
(SELECT a.logoid,
a.trainingoid,
a.employeeoid,
a.lastDate,
a.nextDueDate,
a.fileName
FROM
dbo.OPERATOR_LOG_ENTRY AS a
INNER JOIN
(SELECT
tr
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
