Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JohnGault777
Starting Member
31 Posts |
 Posted - 2011-06-10 : 10:47:54
|
| How can I create a temporary table in a query that lists all the days between two given days? I'm trying to count all the business that happens on a given day between multiple product lines. However, not all the products are sold on same day. So if I simply create tables of transaction by day for each of the product lines and join them by day some days won't be there leaving it difficult to actually generate a table displaying sales on a day. So I wanted to create a temporary table with all the dates I need and join the other tables to that temporary table. That way I won't have to worry about a day getting excluded because there wasn't any activity on a particular day. Any help on this would be much appreciated.JG777 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-10 : 11:04:33
|
If you are on SQL 2005 or higher, you can use a CTE to do this:CREATE TABLE #tmpDates (date DATETIME);DECLARE @startDate DATETIME; SET @startDate = '20110101';DECLARE @days INT; SET @days = 365;WITH N(n) AS( SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < @days)INSERT INTO #tmpDates SELECT DATEADD(dd,n-1,@startDate) FROM N OPTION (MAXRECURSION 0); |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-06-10 : 11:06:16
|
I would suggest creating somet sort of Tally/Numbers table or even a Calendar table you can join with. Another alternative is to create an inline tally table you can join with. for example:;WITH Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)SELECT MAX(N)FROM Tally; |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-10 : 23:24:01
|
| >> How can I create a temporary table in a query that lists all the days between two given days? <<You don't do that at all. Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.CREATE TABLE Calendar(cal_date DATE NOT NULL PRIMARY KEY, fiscal_year INTEGER NOT NULL, fiscal_month INTEGER NOT NULL, week_in_year INTEGER NOT NULL, -- SQL Server is not ISO standard holiday_type INTEGER NOT NULL CHECK(holiday_type IN ( ..), -- day_in_year INTEGER NOT NULL, julian_business_day INTEGER NOT NULL, ...);The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday. Derek Dongray came up with a classification of the public holidays and weekends he needed to work with in multiple countries. Heris his list with more added.1. Fixed date every year.2. Days relative to Easter.3. Fixed date but will slide to next Monday if on a weekend4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).5. Specific day of week after a given date (usually first/last Monday in a month but can be other days, e.g. First Thursday after November 22 = Thanksgiving)6. Days relative to Greek Orthodox Easter (not always the same as Western Easter)7. Fixed date in Hijri (Muslim) Calendar - this turns out to only be approximate due to the way the calendar works. An Imam has to see a full moon to begin the cycle and declare it. 8. Days relative to previous Winter Solstice (Chinese holiday of Qing Ming Jie)9. Civil holidays set by decree, such as a National Day Of Mourning. 10. Fixed date except Saturday slides to Friday, and Sunday slides to Monday. 11. Fixed date, but Tuesday slides to Monday, and Thursday to Friday. (Argentina celebrates October 12, the day Columbus discovered America is a national holiday in Argentina. Except when it's a Tuesday, they back it one day to Monday.As you can see, some of these are getting a bit esoteric and a bit fuzzy. A calendar table for US Secular holidays can be built from the data at this website, so you will get the three-day weekends: http://www.smart.net/~mmontes/ushols.htmlTime zones with fractional hour displacements: http://www.timeanddate.com/worldclock/city.html?n=5http://www.timeanddate.com/worldclock/city.html?n=54http://www.timeanddate.com/worldclock/city.html?n=176http://www.timeanddate.com/worldclock/city.html?n=246 But the strange ones are:http://www.timeanddate.com/worldclock/city.html?n=5http://www.timeanddate.com/worldclock/city.html?n=63CREATE TABLE Calendar(cal_date DATE NOT NULL PRIMARY KEY, julian_business_nbr INTEGER NOT NULL, ...);INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter sunday ('2007-04-09', 44), ('2007-04-10', 45); --TuesdayTo compute the business days from Thursday of this week to nextTuesdays:SELECT (C2. julian_business_nbr - C1. julian_business_nbr) FROM Calendar AS C1, Calendar AS C2WHERE C1.cal_date = '2007-04-05', AND C2cal_date = '2007-04-10'; --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Temporary Tables