Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generating Missing Dates and Numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, February 05, 2008 9:54 PM
 SSC-Addicted Group: General Forum Members Last Login: Saturday, May 11, 2013 8:17 AM Points: 460, Visits: 2,521
 Comments posted to this topic are about the item Generating Missing Dates and Numbers .
Post #452000
 Posted Tuesday, February 05, 2008 11:28 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: 2 days ago @ 2:44 PM Points: 149, Visits: 277
 Another thing that works in order to find the gaps is to "interleave" the numbers together.Say you have a table where the ID field (works with date values too) is usually incremented, but could have gaps in it...select t1.id, t1.id+1 as nextvalfrom t1 left outer join t2 on t1.id = t2.id - 1where t2 is nullI think I got this from one of JCelko's books.It of course doesn't help fill in the blanks in and of itself, but by providing the next number for the lowest number available to be filled, it definitely could be used in an application.The cartesian joins to generate the list of numbers is slick, though!!!
Post #452016
 Posted Wednesday, February 06, 2008 2:24 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, September 08, 2010 2:57 AM Points: 95, Visits: 114
 How aboutSELECT TOP 1 A AS FirstMissingNumberFROM (SELECT ROW_NUMBER() OVER (ORDER BY CoordinatorID) AS A, CoordinatorIDFROM Coordinators) TWHERE A < CoordinatorID
Post #452069
 Posted Wednesday, February 06, 2008 2:30 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, November 26, 2013 3:56 PM Points: 477, Visits: 396
 I think you'll find thatSELECTCAST('2007-10-01' AS DATETIME) + Number-1FROM dbo.GetNumbers(1, 30)assumes a 30-day month, and misses 2007-10-31
Post #452074
 Posted Wednesday, February 06, 2008 4:32 AM
 SSC Rookie Group: General Forum Members Last Login: Friday, November 16, 2012 4:39 AM Points: 31, Visits: 104
Post #452120
 Posted Wednesday, February 06, 2008 5:00 AM
 SSC Rookie Group: General Forum Members Last Login: 2 days ago @ 7:49 AM Points: 31, Visits: 544
 I like it. For the date calculations, I suggest a wrapper function that takes start date and end date, or start date and range type, and calculates the number of days first, this way you don't have to know how many days are in October or whether February 2000 was a leap year...
Post #452127
 Posted Wednesday, February 06, 2008 6:17 AM
 SSC-Addicted Group: General Forum Members Last Login: Saturday, May 11, 2013 8:17 AM Points: 460, Visits: 2,521
 agreed! .
Post #452158
 Posted Wednesday, February 06, 2008 7:01 AM
 SSCoach Group: General Forum Members Last Login: Friday, November 01, 2013 1:55 PM Points: 15,442, Visits: 9,579
 Yet again, I have to say this is clever, but a Numbers table is better.Method in the article, run against a table with 9989 rows:----------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 4 ms.(1 row(s) affected)Table 'Table'. Scan count 1, logical reads 39996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#28D10FF3'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 140 ms, elapsed time = 137 ms.----------------Numbers table method:`select min(number)from Common.dbo.Numbersleft outer join dbo.Table on number = idwhere id is nulland number > 0`--------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(1 row(s) affected)Table 'Table'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Numbers'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 6 ms.-----------------I ran each five times and results were +/- 2 ms total.To make it more fair, I changed the method in the article to a left join, instead of a Where Not In:`SELECT MIN(Number) FROM dbo.GetNumbers(0, 9999) left outer join dbo.Table on number = idWHERE id is null`--------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(1 row(s) affected)Table 'Table'. Scan count 0, logical reads 19998, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#28D10FF3'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 109 ms, elapsed time = 103 ms.----------------Which was a measurable improvement over the Not In method, but still much slower than a Numbers table.Test against code suggested in article: Numbers table 20 times as fastTest against article modified to Left Join instead of Not In: Numbers table 15 times as fastYes, if you for some reason need to create a list of sequential numbers on the fly, this CTE is probably the fastest means of doing so. It certainly is at least A fast means of doing so. But having an actual Numbers table, with a clustered index on it, is MUCH better.(Yes, this is the third or fourth time I've written on this exact subject. But for whatever reason, it keeps coming up.) - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #452176
 Posted Wednesday, February 06, 2008 7:05 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 1:49 AM Points: 2,724, Visits: 3,794
 Hello Jacob,Beautiful article, nice and crisp!I have recently used a similar approach for identifying the next business date for a given date which required the recognition of weekends and holidays. The solution was to - select the minimum date from this dates set as you described - which is greater than the date of the transaction - which is not a weekend (datename <> Sunday / Saturday) - which is not a holiday (date not in HolidayTable) Best Regards, Chris Büttner
Post #452178
 Posted Wednesday, February 06, 2008 7:13 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 7:38 AM Points: 34,581, Visits: 28,766
 Very nicely done, Jacob.Please... not trying to take anything away from this great article... just sharing a different method...As a slightly easier to remember (and, a bit faster, too) approach to generating numbers, check out the following (comparison between Itzek's and a method that I and several others use)...`SET STATISTICS TIME ONGODECLARE @BitBucket INT--=============================================================================PRINT REPLICATE('=',100)PRINT 'Itzek''s method:';WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT @BitBucket = N FROM NUM WHERE N <= 1000000;--=============================================================================PRINT REPLICATE('=',100)PRINT 'Jeff Moden''s Method'; WITH cTally AS(----------------------------------------------------------------------------- --==== High performance CTE equivalent of a Tally or Numbers table SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY t1.ID) AS N FROM Master.sys.SysColumns t1 CROSS JOIN Master.sys.SysColumns t2)-----------------------------------------------------------------------------SELECT @BitBucket = N FROM cTally --Do your outer join with table being checked herePRINT REPLICATE('=',100)`True, Itzeks's will generate more than what an INT can handle, but, how often do you think you're gonna need to generate more than 121 million numbers? ;)Here's the same thing as a programmable function...` CREATE FUNCTION dbo.fnTally/**************************************************************************************** Purpose: Given a range of Integers not exceeding a count of 121 million, return the range of numbers as a table. Notes: Preserved as an "inline" single statement function for sheer performance. Therefore, no error checking, etc. Revision History: Rev 00 - 23 Dec 2005 - Jeff Moden - Initial creation and unit test****************************************************************************************/--===== Declare the parameters ( @piStartNumber INT, @piEndNumber INT )RETURNS TABLE AS RETURN (WITH cTally AS (-------------------------------------------------------------------------------- --==== High performance CTE equivalent of a Tally or Numbers table SELECT TOP (@piEndNumber-@piStartNumber+1) ROW_NUMBER() OVER (ORDER BY t1.ID) AS Number FROM Master.sys.SysColumns t1 CROSS JOIN Master.sys.SysColumns t2 )-------------------------------------------------------------------------------- SELECT N = Number+@piStartNumber-1 FROM cTally )`Still, a small (11k rows - 30 years of dates) permanent Tally table with a Clustered Index on N will usually beat calculated table functions once the table is cached.Again, I say, nice article, Jacob! Do it again! --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #452181

 Permissions