为一个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
trainingoid,
employeeoid,
MAX(lastDate) AS mdate
FROM dbo.OPERATOR_LOG_ENTRY
GROUP BY trainingoid, employeeoid) AS b
ON a.trainingoid = b.trainingoid AND a.lastDate = b.mdate) AS l
LEFT OUTER JOIN dbo.OPERATOR_TRAINING_TYPE AS t ON l.trainingoid = t.trainingoid
LEFT OUTER JOIN dbo.t_UserInfo AS u ON l.employeeoid = u.UserOID) AS A
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,
--Company,
(SELECT Company FROM dbo.t_ClientInfo WHERE dbo.t_ClientInfo.ClientOID = {0}) AS Company,
trainingoid,
trainingType,
vendoroid,
UserName,
LastDate,
NextDue,
logoid
FROM
(SELECT
'Contractors' AS Element,
'Contractor Employee Safety Training' AS Item,
u.vendorName AS Company,
l.trainingoid,
t.trainingType,
l.vendoroid,
'' AS UserName,
l.lastDate AS LastDate,
l.nextDueDate AS NextDue,
l.logoid
FROM
(SELECT
a.logoid,
a.trainingoid,
a.vendoroid,
a.lastDate,
a.nextDueDate,
a.fileName
--,
--a.createdDate
FROM
dbo.CONTR_LOG_ENTRY AS a
INNER JOIN
(SELECT
trainingoid,
vendoroid,
MAX(lastDate) AS mdate
FROM
dbo.CONTR_LOG_ENTRY
GROUP BY trainingoid, vendoroid) AS b
ON a.trainingoid = b.trainingoid AND a.lastDate = b.mdate) AS l
LEFT OUTER JOIN dbo.CONTR_TRAINING_TYPE AS t ON l.trainingoid = t.trainingoid
LEFT OUTER JOIN dbo.VENDOR AS u ON l.vendoroid = u.vendoroid) AS c
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,
--Company,
(SELECT Company FROM dbo.t_ClientInfo WHERE dbo.t_ClientInfo.ClientOID = {0}) AS Company,
trainingoid,
trainingType,
UserOID,
UserName,
lastDate,
nextDueDate,
logoid
FROM
(SELECT
'Emergency Planning and Response' AS Element,
'Hazmat Team Training' AS Item,
--(SELECT TOP (1) setupName FROM dbo.t_firstTimeSetup) AS Company,
l.trainingoid,
t.trainingType,
l.UserOID,
u.UserName,
l.lastDate,
l.nextDueDate,
l.logoid
FROM
(SELECT
a.logoid,
a.trainingoid,
a.UserOID,
a.lastDate,
a.nextDueDate,
a.fileName
FROM dbo.ERP_LOG_ENTRY AS a
INNER JOIN
(SELECT
trainingoid,
UserOID,
MAX(lastDate) AS mdate
FROM dbo.ERP_LOG_ENTRY
GROUP BY trainingoid, UserOID) AS b
ON a.trainingoid = b.trainingoid AND a.lastDate = b.mdate) AS l
LEFT OUTER JOIN dbo.ERP_TRAINING_TYPE AS t ON l.trainingoid = t.trainingoid
LEFT OUTER JOIN dbo.t_UserInfo AS u ON l.UserOID = u.UserOID) AS e
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(nextDueDate) AS varchar(2)) + '/' + CAST(DAY(nextDueDate) AS varchar(2)) + '/' + CAST(YEAR(nextDueDate) AS varchar(4)))
AND CAST(MONTH(nextDueDate) AS varchar(2)) + '/' + CAST(DAY(nextDueDate) AS varchar(2)) + '/' + CAST(YEAR(nextDueDate)AS varchar(4)))
