ฉันใช้ SQL Management Studio 2012 และฉันต้องการเลือกโค้ดต่อไปนี้ลงในตาราง แต่ไม่เคยทำในขณะที่ใช้ CTE
With UniqueService AS (
SELECT
CASE WHEN LAG(ScheduleOfServiceID,1) OVER (ORDER BY ScheduleOfServiceID ASC, ActualHoursPerWeek ASC) = ScheduleOfServiceID
THEN 0
ELSE 1
END AS UniqueService
,[ClientID]
,[ClientName]
,[ScheduleOfServiceID]
,[ActualHoursPerWeek]
,[ServicePlannedStartDate]
,[ServicePlannedEndDate]
,[ServiceActualStartDate]
,[ServiceActualEndDate]
,[ServiceProvided]
,[ServiceProvider]
,[AllocatedWorkerTeam]
,[StartDateFormatted]
,[SOSStartYearMonth]
,[EndDateFormatted]
,[SOSEndYearMonth]
,[ServicePlacementStartReason]
,[RecordToBeExcluded]
,CASE WHEN EndDateFormatted IN ('')
AND ServiceActualEndDate IS NULL
AND ServicePlannedEndDate IS NULL
THEN NULL
WHEN EndDateFormatted IS NULL
THEN ServiceActualEndDate
WHEN EndDateFormatted IS NULL
AND ServiceActualEndDate IS NULL
THEN ServicePlannedEndDate
ELSE EndDateFormatted
END AS MasterEndDate
FROM [BIPROJECT].[adults].[tAllAdultsDomiciliaryCareReporting]
WHERE RecordToBeExcluded != 1
)
SELECT *,
CASE WHEN ServicePlannedStartDate BETWEEN '2016-04-01' AND '2016-08-31'
AND (MasterEndDate IS NULL
OR MasterEndDate > '2016/08/31')
THEN 1
ELSE 0
END AS ServiceStarted,
CASE WHEN MasterEndDate BETWEEN '2016-04-01' AND '2016-08-31'
AND ServicePlannedStartDate NOT BETWEEN '2016-04-01' AND '2016-08-31'
THEN 1
ELSE 0
END AS ServiceEnded,
CASE WHEN ServicePlannedStartDate BETWEEN '2016-04-01' AND '2016-08-31'
AND MasterEndDate BETWEEN '2016-04-01' AND '2016-08-31'
THEN 1
ELSE 0
END AS ServiceStartedAndEnded
FROM UniqueService
WHERE UniqueService = 1
AND (ServiceActualStartDate BETWEEN '2016-04-01' AND '2016-08-31'
OR MasterEndDate BETWEEN '2016-04-01' AND '2016-08-31')
ORDER BY ClientID ASC, ScheduleOfServiceID ASC
ฉันใช้ Google อย่างรวดเร็วและดูโพสต์สแต็กอื่น ๆ แต่ยังไม่ประสบความสำเร็จเลย
INTO #tempTable
ก่อนคำสั่งFROM
- person Chris Pickford   schedule 05.09.2016INSERT INTO table (col1, col2, colN)
ก่อนSELECT
หลังคำจำกัดความ CTE - person Chris Pickford   schedule 05.09.2016