1

I have a View to create, which has 2 CTES. This is what I have done below. The first part is fine, but after "AND" things do not work. Your input is appreciated

CREATE VIEW dbo.VW.SPAg
AS With today as 
(SELECT * FROM dbo.Work_Days
 WHERE [Date] = CAST(GETDATE() AS DATE)
)

AND WITH rd as
(SELECT [DATE] AS REP_DATE
 FROM dbo.Link_Days
 WHERE DAY ([DATE]) = 1
)
SELECT wm *,
gr.DATE_ORDINAL AS Goods_Rcvd_Ordinal
gt.DATE_ORDINAL AS Goods_Trnpt_Ordinal
today.DATE

FROM dbo.SPAg sg
INNER JOIN rd
  ON YEAR(sg.Client_Query)= YEAR(rd.REP_DATE)
LEFT JOIN dbo.Work_DAYS gr
ON sg.Goods_Rcvd = gr.[DATE]
LEFT JOIN dbo.Work_DAYS gt
ON sg.Goods_Trnpt = gt.[DATE]
Speedio
  • 45
  • 1
  • 5

1 Answers1

6

To specify multiple steps CTE use ,

CREATE VIEW dbo.VW.SPAg   /* [dbo].[VW_SPAg] Probably you want this name*/
AS 
WITH today as 
(SELECT * FROM dbo.Work_Days
 WHERE [Date] = CAST(GETDATE() AS DATE)
), rd as
(SELECT [DATE] AS REP_DATE
 FROM dbo.Link_Days
 WHERE DAY ([DATE]) = 1
)
SELECT wm *,
gr.DATE_ORDINAL AS Goods_Rcvd_Ordinal
gt.DATE_ORDINAL AS Goods_Trnpt_Ordinal
today.DATE
FROM dbo.SPAg sg
INNER JOIN rd
  ON YEAR(sg.Client_Query)= YEAR(rd.REP_DATE)
LEFT JOIN dbo.Work_DAYS gr
  ON sg.Goods_Rcvd = gr.[DATE]
LEFT JOIN dbo.Work_DAYS gt
  ON sg.Goods_Trnpt = gt.[DATE];

Also I don't like view name dbo.VW.SPAg, do you have database called [dbo]? Or it should be [dbo].[VW_SPAg]?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275