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)