In my continued SQL adventures, I’ve been experimenting with creating views in SQL. SQL Views are awesome, because once they are accessed they rerun the code, which repulls the data. This is awesome because it refreshes anything connected to it. So for example, if I have a Tableau Dashboard hooked up to a SQL View, every time someone logs onto Tableau and accesses the Dashboard it, it’ll pull the correct, most up to date data.
One slight mind twist is my SQL query normally contains temp SQL tables (signified by the #) which aren’t accepted in creating a view. Some of these queries can get complex, manipulating data to pull into another temp table to finally output the result. To remake that, you can use the ‘with BLANK as…’ syntax. I’ve written out a full example, because there are a few other foibles involved with writing a view.
Views aren’t always appropriate (especially when looking at static tables) but especially with regards to reporting, they can be quite a useful tool.
CREATE VIEW dbo.VW.SPAg
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)