Insert a record for every possible date within a range

Question

Insert a record for every possible date within a range

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Okay, I missed the "simple" ... I think Jared has it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

Adam Bean (6/12/2012)
Eugene Elutin (6/12/2012)
You don't need the cursor! If you have the calendar table, just select records you need filtering by date.
Or you can use some run-time tally table like that:
DECLARE @DateFrom DATETIME
SET @DateFrom = '20120601'
SELECT TOP (DATEDIFF(DAY,@DateFrom,GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER()
OVER (ORDER BY [object_id]) -1, @DateFrom ) AS MyDate
, @@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
FROM sys.columns

You can see the maximum number of records it can return is limited by number of records in sys.columns, if you need more you can create Cartesian product of this table...
If you need some help with use of your calendar table, please post its DDL.
You sir, are my hero ... this is perfect and accomplished exactly what I wanted to do!
Thank you!

Now, a question for you. Do you know how it actually works?

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

The thing I'm not understanding is what purpose the TOP serves ... obviously because I am now running into this error: A TOP N value may not be negative.

I'm still parsing the data (this is insane data set), but I don't even see how it's possible to get a negative date; however I'm assuming it's breaking here: ORDER BY [CalendarDate]) -1

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Adam Bean (6/19/2012)
The thing I'm not understanding is what purpose the TOP serves ... obviously because I am now running into this error: A TOP N value may not be negative.
I'm still parsing the data (this is insane data set), but I don't even see how it's possible to get a negative date; however I'm assuming it's breaking here: ORDER BY [CalendarDate]) -1

Can you post the code you are running as well as the input value when you get the TOP N value may not be negative error?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Disregard, I found it ... was passing a date range on data that did not exist ... thanks all!