tag:blogger.com,1999:blog-1937347769359951209.post6991635039071030156..comments2024-03-29T00:12:51.762-07:00Comments on Microsoft Business Intelligence (Data Tools): SQL – Recursive Date with CTEMukesh Singhhttp://www.blogger.com/profile/10793266909993773163noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-1937347769359951209.post-81573085397587386992016-09-07T07:15:13.349-07:002016-09-07T07:15:13.349-07:00Using a CTE to do this is REALLY REALLY inefficien...Using a CTE to do this is REALLY REALLY inefficient!! Your code takes 6600 IO and 16 cpu ticks to perform the calculation for 2 years of rows (reported in profiler)!! A simple numbers table will do the same with ZERO IO and ZERO cpu ticks .<br /><br />DECLARE @s DATE=DATEADD(YY,-2,GETDATE()),<br />@e DATE=GETDATE()<br />SELECT StartDate=@s, EndDate=@e<br /><br />--inline Numbers Table<br />; WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),<br />E2(N) AS (SELECT 1 FROM E1 a, E1 b),<br />E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10000 max<br />cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)<br />SELECT DayDate=DateADD(D,N,@s),<br />NameOfDay=DateName(DW,DateADD(D,N,@s)),<br />NameOfMonth=DateName(M,DateADD(D,N,@s)),<br />YearOf=YEAR(DateADD(D,N,@s))<br />FROM cteTally<br />WHERE DateADD(D,N,@s)<=@eTheSQLGuruhttps://www.blogger.com/profile/09262245741634070658noreply@blogger.comtag:blogger.com,1999:blog-1937347769359951209.post-54055321840555797742016-03-31T06:20:24.277-07:002016-03-31T06:20:24.277-07:00excelent, thank you for compartirexcelent, thank you for compartirAnonymoushttps://www.blogger.com/profile/15579974644166774641noreply@blogger.com