Select into a table with a CTE

Question

I'm using SQL Management Studio 2012 and I want to select the following code into a table but have never done so whilst using a 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

I've had a quick google and looked through some other stack posts but haven't had any success as of yet.

*select the following code into a table* Do you want to insert the result of above select query into a existing table or you want to create a new table and insert the result — Pரதீப், Sep 05 '16 at 15:33
Just add `INTO #tempTable` right before the `FROM` statement. — Chris Pickford, Sep 05 '16 at 15:35
- or `INSERT INTO table (col1, col2, colN)` right before the `SELECT` after the CTE definition. — Chris Pickford, Sep 05 '16 at 15:36

Select into a table with a CTE

0 Answers0