UNION ALL with CTE

January 28, 2022

I am trying to UNION ALL two tables 1) Current receipts 2) Historical receipts. Both of those tables have CTE because it was the only way how could it change numeric field into date field.

This is my Current receipts table

;WITH t AS 
(SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1 THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNT] r)
SELECT [Day of Week] = ProperDate, [Saturday] = DATEADD
       (DAY, 6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7), ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK,ws.WHDNAM,wv.VNVEN#,wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
  WHERE ws.WHAFLG = 'Y'
  GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
  ORDER BY  wm.ITPPCK DESC

This is my Historical receipts

;WITH t AS 
(
  SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1
    THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNH] r
)
SELECT [Day of Week] = ProperDate,[Saturday] = DATEADD (DAY,  6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7),  ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
WHERE  ws.WHAFLG = 'Y' and ProperDate BETWEEN @Last2WeekDATE AND @LWDATE
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN;

All the column names are the same. I thought that I just need to put UNION ALL in between and put GROUP BY and ORDER BY at the end and it would make it work. However, if I leave both CTEs with Proper Date it basically gives me an error "Incorrect syntax near the line with the second CTE after UNION ALL".

I know it looks like a lot of text, but it is exactly the same fields and same joins between two tables, only difference between both of those 2 tables is that 1 one is from a table [RECTRNT] – recent receipts and 2nd one is from [RECTRNH] – historic receipts.

Everything else is the same.

I will appreciate any ideas.

>Solution :

;WITH Current_data_cte AS 
(SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1 THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNT] r)
  ,Current_data as(
SELECT [Day of Week] = ProperDate, [Saturday] = DATEADD
       (DAY, 6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7), ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK,ws.WHDNAM,wv.VNVEN#,wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
  WHERE ws.WHAFLG = 'Y'
  GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
  )--ORDER BY  wm.ITPPCK DESC
  
 , Historical_data_cte AS 
(
  SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1
    THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNH] r
), 
Historical_data as(
SELECT [Day of Week] = ProperDate,[Saturday] = DATEADD (DAY,  6 - ((DATEPART(WEEKDAY, ProperDate) + @@DATEFIRST - 1) % 7),  ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
WHERE  ws.WHAFLG = 'Y' and ProperDate BETWEEN @Last2WeekDATE AND @LWDATE
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN;
  )
  select * from Current_data
  union
  select * from Historical_data