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.
;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