CTE function with insert statement

I have 2 queries and I need to combine them into one query with an insert statement.

This is my first query that already has an insert statement:

with q as (
      select s.department
           ,s.months
           ,s.years     
           ,count(case when s.sum_lost_time >='10:00:00' then NAME end) as RTOTALLOSTTIME
           ,count(case when s.sum_ot >='20' then NAME end) as ROT
     from (select MONTH(STATUSIN)  as [months]
                   ,YEAR(STATUSIN) as [years]
                   ,NIP
                   ,NAME
                   ,DEPARTMENT         
                   ,convert(varchar,dateadd(second,sum(datediff(second,'00:00:00',cast(TOTALLT as time))),0),108) as sum_lost_time
                   ,SUM(CAST(OT AS FLOAT)) as sum_ot
              from SUMMARYDATA b 
          group by MONTH(STATUSIN)
                   ,YEAR(STATUSIN)
                   ,NIP
                   ,NAME
                   ,DEPARTMENT
            )s
    group by  s.department
             ,s.months
             ,s.years  
     )
INSERT INTO REPORTDATA(DEPARTMENT,MONTHS,YEARS,RTOTALLOSTTIME,ROT)
SELECT DEPARTMENT,MONTHS,YEARS,RTOTALLOSTTIME,ROT
FROM q

This is the result from first query in table REPORTDATA:

And this is my second query.

WITH cte AS 
(
    SELECT DISTINCT [NAME], DEPARTMENT, MONTH(STATUSIN) [MONTH], YEAR(STATUSIN) [YEAR], 
        SUM(CASE WHEN LATECOME = '00:00:00' THEN 0 ELSE 1 END) OVER(PARTITION BY [NAME], DEPARTMENT, MONTH(STATUSIN), YEAR(STATUSIN)) Total
        ,SUM(CASE WHEN EARLYLEAVE = '00:00:00' THEN 0 ELSE 1 END) OVER(PARTITION BY [NAME], DEPARTMENT, MONTH(STATUSIN), YEAR(STATUSIN)) TotalEarlyLeave 
    FROM SUMMARYDATA
)

SELECT SUM(CASE WHEN TOTAL > 2 THEN 1 ELSE 0 END) LATECOME, 
SUM(CASE WHEN TotalEarlyLeave > 1 THEN 1 ELSE 0 END) EARLYLEAVE
FROM cte
GROUP BY DEPARTMENT, [MONTH], [YEAR] 

And this is the result from second query:

I want to place it into my first query but I don’t know how to combine it into one in insert statement. Can anyone solve my problems?

This is the sample to my first query: Count summary records per month with conditional SQL and this is the sample to second query: Count records per month with condition in SQL Server

Answer

It’s easy if you concatenate your queries as multiple CTEs, and finally JOIN them.

Like this :

;
with cte1 as (
      select s.department
           ,s.months
           ,s.years     
           ,count(case when s.sum_lost_time >='10:00:00' then NAME end) as RTOTALLOSTTIME
           ,count(case when s.sum_ot >='20' then NAME end) as ROT
     from (select MONTH(STATUSIN)  as [months]
                   ,YEAR(STATUSIN) as [years]
                   ,NIP
                   ,NAME
                   ,DEPARTMENT         
                   ,convert(varchar,dateadd(second,sum(datediff(second,'00:00:00',cast(TOTALLT as time))),0),108) as sum_lost_time
                   ,SUM(CAST(OT AS FLOAT)) as sum_ot
              from SUMMARYDATA b 
          group by MONTH(STATUSIN)
                   ,YEAR(STATUSIN)
                   ,NIP
                   ,NAME
                   ,DEPARTMENT
            )s
    group by  s.department
             ,s.months
             ,s.years  
     ),
cte2 as (
    SELECT DISTINCT [NAME], DEPARTMENT, MONTH(STATUSIN) [MONTH], YEAR(STATUSIN) [YEAR], 
        SUM(CASE WHEN LATECOME = '00:00:00' THEN 0 ELSE 1 END) OVER(PARTITION BY [NAME], DEPARTMENT, MONTH(STATUSIN), YEAR(STATUSIN)) Total
        ,SUM(CASE WHEN EARLYLEAVE = '00:00:00' THEN 0 ELSE 1 END) OVER(PARTITION BY [NAME], DEPARTMENT, MONTH(STATUSIN), YEAR(STATUSIN)) TotalEarlyLeave 
    FROM SUMMARYDATA
    ),
cte3 as (
    SELECT DEPARTMENT, [MONTH], [YEAR], SUM(CASE WHEN TOTAL > 2 THEN 1 ELSE 0 END) LATECOME, 
    SUM(CASE WHEN TotalEarlyLeave > 1 THEN 1 ELSE 0 END) EARLYLEAVE
    FROM cte2
    GROUP BY DEPARTMENT, [MONTH], [YEAR] 
    )
INSERT INTO REPORTDATA (DEPARTMENT, MONTHS, YEARS, RTOTALLOSTTIME, ROT, RLATECOME, REARLYLEAVE)
SELECT cte1.DEPARTMENT, cte1.MONTHS, cte1.YEARS, cte1.RTOTALLOSTTIME, cte1.ROT,
       cte3.LATECOME, cte3.EARLYLEAVE  
FROM cte1
     LEFT JOIN cte3 ON cte3.DEPARTMENT = cte1.DEPARTMENT and cte3.[MONTH] = cte1.[MONTH] and cte3.[YEAR] = cte1.[YEAR]