SELECT a.office
, max(lastdate) AS DeptLastDate
, datediff(DAY, convert(VARCHAR(10), max(lastdate), 111), getdate()) AS DiffDay
, l.Description
FROM
(SELECT d.Department
, left(d.Department, 2) + 00 AS office
, convert(VARCHAR(10), max(StartDate), 111) AS lastdate
FROM
DisasterCount AS D
JOIN LunchBox.dbo.location AS l
ON d.Department = l.DepCode
WHERE
Enabled = 1
AND hurt LIKE %x能%
AND d.Department NOT LIKE U3%
GROUP BY
d.Department) AS a
JOIN LunchBox.dbo.location AS l
ON a.office = l.DepCode
GROUP BY
a.office
, l.Description UNION
SELECT a.office
, max(lastdate) AS DeptLastDate
, datediff(DAY, convert(VARCHAR(10), max(lastdate), 111), getdate()) AS DiffDay
, xx AS DESCRIPTION
FROM
(SELECT TOP 1 Department
, left(Department, 3) + 0 AS office
, convert(VARCHAR(10), max(StartDate), 111) AS lastdate
FROM
DisasterCount
WHERE
Enabled = 1
AND hurt LIKE %x能%
AND Department LIKE U3%
AND Department < U380
GROUP BY
Department order by lastdate desc) as a
group by a.office union SELECT a.office
, max(lastdate) AS DeptLastDate
, datediff(DAY, convert(VARCHAR(10), max(lastdate), 111), getdate()) AS DiffDay
, oo AS DESCRIPTION
FROM
(SELECT TOP 1 Department
, left(Department, 3) + 0 AS office
, convert(VARCHAR(10), max(StartDate), 111) AS lastdate
FROM
DisasterCount
WHERE
Enabled = 1
AND hurt LIKE %x能%
AND Department LIKE U3%
AND Department >= U380
GROUP BY
Department order by lastdate desc) as a
group by a.office |
住戶編號:2634222
檢舉原因
心情: 
