Clinthuijbers.files.wordpress.com



/*Written by Clint Huijbers @ 2015-03-25This code demonstrates how to use the window functions (OVER-clause) you'll be able to use with SQL Server 2012 (and up), for Fact-tables.Keep updated by following my blog post on:*/DECLARE @DateFrom DATETIME = '2012-12-31' --Starting from 2013-01-01 ,@DateTo DATETIME = CAST(GETDATE() AS DATE);WITH SourceAS( --Let's generate some data: SELECT TOP (DATEDIFF(DAY,@DateFrom,@DateTo)) DATEADD(DAY,[Row],@DateFrom) AS Date ,DATEPART(YEAR,DATEADD(DAY,[Row],@DateFrom)) AS Year ,DATEPART(MONTH,DATEADD(DAY,[Row],@DateFrom)) AS Month ,DATEPART(WEEK,DATEADD(DAY,[Row],@DateFrom)) AS Week ,[Row] % 4 AS Jobs --Number of jobs, value which will be used in the calculations. FROM ( SELECT [Row] = ROW_NUMBER() OVER(ORDER BY v1.Number ASC) FROM master.dbo.spt_values v1 CROSS JOIN master.dbo.spt_values v2 ) d),CalculatedDatasetAS( SELECT s.* --Jobs calculations: ,SUM(s.Jobs) OVER ( PARTITION BY s.Year, s.Week ORDER BY s.Year, s.Week, s.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS JobsWTD --Week to date ,SUM(s.Jobs) OVER ( PARTITION BY s.Year, s.Week ORDER BY s.Year, s.Week ) AS JobsFW --Full week ,SUM(s.Jobs) OVER ( PARTITION BY s.Year, s.Month ORDER BY s.Year, s.Month, s.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS JobsMTD --Month to date ,SUM(s.Jobs) OVER ( PARTITION BY s.Year, s.Month ORDER BY s.Year, s.Month ) AS JobsFM --Full month ,SUM(s.Jobs) OVER ( PARTITION BY s.Year ORDER BY s.Year, s.Month, s.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS JobsYTD --Year to date ,SUM(s.Jobs) OVER ( PARTITION BY s.Year ORDER BY s.Year ) AS JobsFY --Full year FROM Source s)SELECT CY.[Date] ,CY.[Year] ,CY.[Month] ,CY.Week ,'---' AS '---' --Calculated columns for the current year: ,CY.Jobs ,CY.JobsWTD ,CY.JobsFW ,CY.JobsMTD ,CY.JobsFM ,CY.JobsYTD ,CY.JobsFY ,'---' AS '---' --Calculated columns for the previous year: ,PY.Jobs AS JobsPY --Jobs Previous Year ,PY.JobsWTD AS JobsWTDPY --Jobs Week to Date Previous Year ,PY.JobsFW AS JobsFWPY --Jobs Full Week Previous Year ,PY.JobsMTD AS JobsMTDPY --Jobs Month to Date Previous Year ,PY.JobsFM AS JobsFMPY --Jobs Full Month Previous Year ,PY.JobsYTD AS JobsYTDPY --Jobs Year-to-Date Previous Year ,PY.JobsFY AS JobsFYPY --Jobs Full Year Previous YearFROM CalculatedDataset CY --Current Year INNER JOIN CalculatedDataset PY --Previous Year --IMPORTANT: Due to the INNER JOIN, the first year will not be returned in the final resultset. ON DATEADD(YEAR,-1,CY.[Date]) = PY.[Date]ORDER BY CY.[Date] ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download