|
|
You're in the
sql server (alternate)
group:Dates in a date range
sql server (alternate):
a given date range without the need to have a temporary table and a cursor? perhaps something like: declare @start_date as datetime declare @end_date as datetime set @start_date as '1/1/2005' set @end_date as '1/1/2006' select fn_getuniquedate(@start_date, @end_date) 1/1/2005 1/2/2005 1/3/2005 ... 12/31/2005
Get a copy of SQL FOR SMARTIES and look up the uses for a Calendar
table. You need to stop thinking about functions and start thinking in terms of tables and joins.
Any reason why you can't create a permanent Calendar table in your database?
Calendars are useful for many types of query so it makes sense to have one if you need to do anything with dates. SELECT cal_date FROM Calendar WHERE cal_date BETWEEN @start_date AND @end_date ; Otherwise, you could write an iterative table-valued function to generate the data. Unlikely to perform better than a permanent table in most cases though. -- David Portas SQL Server MVP -- [quoted text, click to view] "PromisedOyster" <PromisedOyster@hotmail.com> wrote in message news:1128748317.108113.292290@g49g2000cwa.googlegroups.com... > Is there a way that I can get a resultset that contains unique dates in > a given date range without the need to have a temporary table and a > cursor? > > perhaps something like: > > declare @start_date as datetime > declare @end_date as datetime > set @start_date as '1/1/2005' > set @end_date as '1/1/2006' > select fn_getuniquedate(@start_date, @end_date) > > > 1/1/2005 > 1/2/2005 > 1/3/2005 > .. > 12/31/2005 >
PromisedOyster (PromisedOyster@hotmail.com) writes:
[quoted text, click to view] > Is there a way that I can get a resultset that contains unique dates in > a given date range without the need to have a temporary table and a > cursor? > > perhaps something like: > > declare @start_date as datetime > declare @end_date as datetime > set @start_date as '1/1/2005' > set @end_date as '1/1/2006' > select fn_getuniquedate(@start_date, @end_date) As David and Celko said, better store this in a table once for all. What they didn't say was how to fill it. Here is how I fill our dates table with dates from 1990 to 2150. Adapt as you like: TRUNCATE TABLE dates go -- Get a temptable with numbers. This is a cheap, but not 100% reliable. -- Whence the query hint and all the checks. SELECT TOP 80001 n = IDENTITY(int, 0, 1) INTO #numbers FROM sysobjects o1 CROSS JOIN sysobjects o2 CROSS JOIN sysobjects o3 CROSS JOIN sysobjects o4 OPTION (MAXDOP 1) go -- Make sure we have unique numbers. CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n) go -- Verify that table does not have gaps. IF (SELECT COUNT(*) FROM #numbers) = 80001 AND (SELECT MIN(n) FROM #numbers) = 0 AND (SELECT MAX(n) FROM #numbers) = 80000 BEGIN DECLARE @msg varchar(255) -- Insert the dates: INSERT dates (thedate) SELECT dateadd(DAY, n, '19800101') FROM #numbers WHERE dateadd(DAY, n, '19800101') < '21500101' SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) + ' rows into #numbers' PRINT @msg END ELSE RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1) go DROP TABLE #numbers -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Thanks for your help, but I do not have CREATE TABLE permissions for
this particular database, hence the request. I only have SELECT permission. Thanks Erland for your assistance on populating the dates table, but that seems a very complicated way to do it. I normally stick it in a while loop and do a DateAdd. Sure, it might not be efficient but that is not an issue
PromisedOyster (PromisedOyster@hotmail.com) writes:
[quoted text, click to view] > Thanks for your help, but I do not have CREATE TABLE permissions for > this particular database, hence the request. I only have SELECT > permission. So use a table varaible or a temp table. [quoted text, click to view] > Thanks Erland for your assistance on populating the dates table, but > that seems a very complicated way to do it. I normally stick it in a > while loop and do a DateAdd. Sure, it might not be efficient but that > is not an issue Complicated? Well, if dateadd() is good enough to you, why did you even bother to ask? :-) The script uses a table of numbers, which is a common way to solve SQL problems where you need a range of values. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Thanks.
But I didn't ask about how to populate it.
[quoted text, click to view]
>> I normally stick it in a WHILE loop and do a DateAdd. << Sure, why learn SQL and RDBMS when you can use proprietary code that looks like BASIC, Cobol or your native 3GL language? [quoted text, click to view] >> Sure, it might not be efficient but that is not an issue << Do you put that on your resume or tell your boss that "my code sucks. but it is not an issue"? Wow!! Pretend that you are a professional programmer. Go to the guy with permissions add tables to the schema and get himto do what he should have done if he had been a professional, so you have a calendar and a sequence table. This is so fundamental I cannot understand why they are not there.
[quoted text, click to view]
>> Thanks Erland for your assistance on populating the dates table, but that seems a very complicated way to do it. << Could you please show us the portable, un-complicated code for determining Easter, Chinese New Year and the Jewish holiidays? The 150+ fiscal calendars under GAAP? 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 SMALLINT NOT NULL, fiscal_month SMALLINT NOT NULL, week_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard holiday SMALLINT NOT NULL CHECK(holiday IN (0,1)), day_in_year SMALLINT NOT NULL, ...); 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.html
--CELKO-- (jcelko212@earthlink.net) writes:
[quoted text, click to view] >>> Thanks Erland for your assistance on populating the dates table, but >>> that seems a very complicated way to do it. << > > Could you please show us the portable, un-complicated code for > determining Easter, Chinese New Year and the Jewish holiidays? The > 150+ fiscal calendars under GAAP? I don't think that he was asking for. He only wanted the days flat out, no mention of holidays or anything. [quoted text, click to view] > 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. And he didn't have the privs to create tables. Rather than using canned rants, try to read people posts. If you ever care to be helpful, that is. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Why would I want to show you portable, un-complicated code for
determining Easter, Chinese New Year and the Jewish holiidays? These are of no relevance or interest to us whatsoever as Erland also pointed out. [quoted text, click to view] --CELKO-- wrote:
> >> Thanks Erland for your assistance on populating the dates table, but that seems a very complicated way to do it. << > > Could you please show us the portable, un-complicated code for > determining Easter, Chinese New Year and the Jewish holiidays? The > 150+ fiscal calendars under GAAP? > > 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 SMALLINT NOT NULL, > fiscal_month SMALLINT NOT NULL, > week_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard > holiday SMALLINT NOT NULL > CHECK(holiday IN (0,1)), > day_in_year SMALLINT NOT NULL, > ...); > 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.html
Well Celko, I have been in the IT industry for a number of years now
and without a doubt you must be one of the rudest and most arrogant people I have came across. Reading some other postings, I am not alone in my view.
The Calendar table is not just for this one problem. It is an
auxiliary table that serves the ENTIRE schema. Think in terms of general, global code instead of handling each problem as a self-contained one-shot. A data model is a whole, not disjoint parts. So the ability to use a fiscal calendar is not required by your accouting department? Your Human Resources department does not care about holidays?
--CELKO-- (jcelko212@earthlink.net) writes:
[quoted text, click to view] > The Calendar table is not just for this one problem. It is an > auxiliary table that serves the ENTIRE schema. Think in terms of > general, global code instead of handling each problem as a > self-contained one-shot. A data model is a whole, not disjoint parts. > > So the ability to use a fiscal calendar is not required by your > accouting department? Your Human Resources department does not care > about holidays? Tell me Celko, in your previous life when you sold vacuum cleaner's at people's doors, were you really successful only because you were so tiresome insisting that they bought one only to get rid of you? We have no idea what business problem PromisedOyster has, so it's quite pointless to cram that calender table down this throat. Particularly, since when we know he does not have have privileges to create tables anyway.) And for that matter, our database has a dates table which is a single-column table with all dates from 1990-01-01 to 2150-01-01. Simply your table of numbers, but with dates. We need to be able to insert/update data into historic tables over a date range. Holidays etc? Yes, there is table for this as well, but it only has entries for Mon-Fri that are not business days, and it's maintained by users. This table could never serves as the date table that I mentioned previously, can you see why? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
>> We have no idea what business problem PromisedOyster has, so it's quite pointless to cram that calender table down this throat. << Okay, what are the odds that he lives in a world without time and aksed this question? [quoted text, click to view] >> Particularly, since when we know he does not have have privileges to create tables anyway.<< So the right answer to temporal problems change depending on your privileges? No, it does not. The abiltiy to do the right thing might change depending on your privileges, but the answer does not. And as a professional it is your duty to speak the truth. [quoted text, click to view] >> Simply your table of numbers, but with dates. << When I do a Sequence table, I often have other columns such as number names, a random number, a weird function, etc.. [quoted text, click to view] >> Holidays etc? Yes, there is table for this as well, but it only has entries for Mon-Fri that are not business days, and it's maintained by users. << Keeping temporal data in two places as you proposed sounds like attribute splitting. Can you tell me why a holiday is a logically different kind of thing from anyother date?
--CELKO-- (jcelko212@earthlink.net) writes:
[quoted text, click to view] >>> We have no idea what business problem PromisedOyster has, so it's quite pointless to cram that calender table down this throat. << > > Okay, what are the odds that he lives in a world without time and aksed > this question? Few people live in a world without food and water. Does that mean that as soon as we design a database, we must have food and warer in ir? [quoted text, click to view] > So the right answer to temporal problems change depending on your > privileges? No, it does not. The abiltiy to do the right thing might > change depending on your privileges, but the answer does not. And as a > professional it is your duty to speak the truth. As a professional it is our duty to help people with the problems they present. Not the problems we invent outselves. All we know is that Promised Oyster needs is a temporary table of some sort that gives him all dates in an interval. It is also our professional duty to behavely politely and respectfully towards people. [quoted text, click to view] >>> Holidays etc? Yes, there is table for this as well, but it only has entries for Mon-Fri that are not business days, and it's maintained by users. << [quoted text, click to view] > > Keeping temporal data in two places as you proposed sounds like > attribute splitting. Can you tell me why a holiday is a logically > different kind of thing from anyother date? The two tables serves different purposes. The table with all the dates puts no attributes on the dates, and is only a help table for some operations. The other table lists only holidays. Neither that table has any other data beside the primary key, beside auditing data. But there is an important difference between the two tables, let's see if you can spot it! -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
>> All we know is that Promised Oyster needs is a temporary table of some sort that gives him all dates in an interval. << No. We have experence and have seen this before. This is like a doctor who treats every pain with a dose of drugs versus a doctor who actually diagnoses the problem and looks for a long-term solution. [quoted text, click to view] >> The table with all the dates puts no attributes on the dates, and is only a help table for some operations. The other table lists only holidays. << Would you also design a schema with a table for male employees and one for female employees? That wouild be splitting the entit on the gender attribute. The holidays and non-holidays are still days. The holiday attribute can change by decree or by definition (Easter, Chinese New Years or other lunar-solar calendar holidays). Nobody can stop time or skip a day. As a simple test, when you have a printed calendar, do you put the holidays on the pages of the calendar or on a separate piece of paper on the other side fo the room? Can a holiday exist without a date (ii.e. Can I put Christmas in a bottle by itself and pull it out as needed)?
--CELKO-- (jcelko212@earthlink.net) writes:
[quoted text, click to view] >>> All we know is that Promised Oyster needs is a temporary table of some sort that gives him all dates in an interval. << > > No. We have experence and have seen this before. This is like a > doctor who treats every pain with a dose of drugs versus a doctor who > actually diagnoses the problem and looks for a long-term solution. And you very much go for the former, I see. [quoted text, click to view] >>> The table with all the dates puts no attributes on the dates, and is only a help table for some operations. The other table lists only holidays. << [quoted text, click to view] > > Would you also design a schema with a table for male employees and one > for female employees? That wouild be splitting the entit on the gender > attribute. The holidays and non-holidays are still days. The holiday > attribute can change by decree or by definition (Easter, Chinese New > Years or other lunar-solar calendar holidays). Nobody can stop time or > skip a day. > > As a simple test, when you have a printed calendar, do you put the > holidays on the pages of the calendar or on a separate piece of paper > on the other side fo the room? Can a holiday exist without a date > (ii.e. Can I put Christmas in a bottle by itself and pull it out as > needed)? Oh, you still don't get it! Here you come with canned responses about calendar tables, and then you cannot model them properly. So, OK, the proper definition depends on the business needs. And, no, this have nothing to do whether there it is a sparse table with only the holidays, or if all days of the year is in table. It's another issue that makes it impossible for me to have the holidays in the same table that has a single row for each day from 1990-01-01 to 2149-12-31. Try to use a little imagination, it's not difficult at all! -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
Erland Sommarskog wrote: > --CELKO-- (jcelko212@earthlink.net) writes: > >>>>Thanks Erland for your assistance on populating the dates table, but >>>>that seems a very complicated way to do it. << >> >>Could you please show us the portable, un-complicated code for >>determining Easter, Chinese New Year and the Jewish holiidays? The >>150+ fiscal calendars under GAAP? Store them in a table. Why bother calculating them? It's not worth the effort. [quoted text, click to view] > > > I don't think that he was asking for. He only wanted the days flat out, > no mention of holidays or anything. Yep, and the simplest way to do this is to create a table just with dates in it from 1/1/2000 to whenever, like JCelko pointed out. [quoted text, click to view] > >>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. > > > And he didn't have the privs to create tables. So what? Shouldn't he be able to ASK someone with the privs to get the table created? It has worked well for me in the past as a contractor at other companies... [quoted text, click to view] > Rather than using canned rants, try to read people posts. If you ever > care to be helpful, that is.
[quoted text, click to view]
Erland Sommarskog wrote: > The two tables serves different purposes. The table with all the dates > puts no attributes on the dates, and is only a help table for some > operations. The other table lists only holidays. Neither that table > has any other data beside the primary key, beside auditing data. But > there is an important difference between the two tables, let's see if > you can spot it! Yes, using the second table with just holidays is a PITA. Flagging dates in the calendar as various holidays is far easier to use. The list of holidays can be derived from the calendar table easily enough.
[quoted text, click to view]
Erland Sommarskog wrote: > corey lawson (corey.lawson@ayeteatea.net) writes: > >>Erland Sommarskog wrote: >> >> >>>The two tables serves different purposes. The table with all the dates >>>puts no attributes on the dates, and is only a help table for some >>>operations. The other table lists only holidays. Neither that table >>>has any other data beside the primary key, beside auditing data. But >>>there is an important difference between the two tables, let's see if >>>you can spot it! >> >>Yes, using the second table with just holidays is a PITA. Flagging dates >>in the calendar as various holidays is far easier to use. > > > No. Well, in the case you only care about one country, it is. We need > to keep track of non-business days in several countries. > > > > So you add other fields to help identify different holidays, right? At the very least, Chicago celebrates Kasmir Pulaski Day (it's an official Chicago holiday, in that schools and city offices are closed. I'll refrain from making any derisive comments about Hizzoner Daley). As far as quickly populating a calendar table, sometimes it's just quicker to fire up Excel, start in A1 with "1/1/2005", and drag it down to A65535 or so to autofill the dates forward, and then import it into a table. It's just so much easier working with a calendar table like this (theta joins work pretty dang good), so that for the person who asked, the DBA should be able to find SOMEWHERE in the database, even in the master database (gasp! shock! horror!). If done right, it'll be static for quite some time (years), and should be relatively obvious for a database geek to realize if it's getting near the end of time to extend it again. Besides, if you're using non-calendar accounting periods (i.e., 4-4-5, 13-wk qtrs, etc), it's about the only way to make them sane, that is, if
corey lawson (corey.lawson@ayeteatea.net) writes:
[quoted text, click to view] > Erland Sommarskog wrote: > >> The two tables serves different purposes. The table with all the dates >> puts no attributes on the dates, and is only a help table for some >> operations. The other table lists only holidays. Neither that table >> has any other data beside the primary key, beside auditing data. But >> there is an important difference between the two tables, let's see if >> you can spot it! > > Yes, using the second table with just holidays is a PITA. Flagging dates > in the calendar as various holidays is far easier to use. No. Well, in the case you only care about one country, it is. We need to keep track of non-business days in several countries. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
corey lawson (corey.lawson@ayeteatea.net) writes:
[quoted text, click to view] > So you add other fields to help identify different holidays, right? > At the very least, Chicago celebrates Kasmir Pulaski Day (it's an > official Chicago holiday, in that schools and city offices are closed. > I'll refrain from making any derisive comments about Hizzoner Daley). This far we have not had reason to care why there is a holiday. All we care about is whether this is a day when the stock exchange and the clearing houses are open. There might be need for changes further down the road, but this model has served us well since 1992. The bottom line is that different systems have different needs, and believing that there is a universal defintion of a calendar that fits all systems is a fallacy. Some systems have no need of a calendar at all. Other systems only needs to cover the local customs, others need to cover local holidays like those in Chicago. And ours need to work only country level, but maybe one day we might have to move it to be by market place and clearing house. Etc. And since needs are different, one should not cram down a calendar table down the throat of anyone who is asking. [quoted text, click to view] > It's just so much easier working with a calendar table like this (theta > joins work pretty dang good), so that for the person who asked, the DBA > should be able to find SOMEWHERE in the database, even in the master > database (gasp! shock! horror!). If done right, it'll be static for > quite some time (years), and should be relatively obvious for a database > geek to realize if it's getting near the end of time to extend it again. Maybe there is one. May there isn't one. Maybe the DBA for political reasons will not let use the table. Again, please stop cramming down solutions down people's throat, when they clearly tell you that the solution you have is not applicable! -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
Erland Sommarskog wrote: > corey lawson (corey.lawson@ayeteatea.net) writes: > >>So you add other fields to help identify different holidays, right? >>At the very least, Chicago celebrates Kasmir Pulaski Day (it's an >>official Chicago holiday, in that schools and city offices are closed. >>I'll refrain from making any derisive comments about Hizzoner Daley). > > > This far we have not had reason to care why there is a holiday. All we > care about is whether this is a day when the stock exchange and the > clearing houses are open. There might be need for changes further down > the road, but this model has served us well since 1992. > > The bottom line is that different systems have different needs, and > believing that there is a universal defintion of a calendar that fits > all systems is a fallacy. Some systems have no need of a calendar at > all. Other systems only needs to cover the local customs, others need > to cover local holidays like those in Chicago. And ours need to work > only country level, but maybe one day we might have to move it to be > by market place and clearing house. Etc. > > And since needs are different, one should not cram down a calendar table > down the throat of anyone who is asking. > > >>It's just so much easier working with a calendar table like this (theta >>joins work pretty dang good), so that for the person who asked, the DBA >>should be able to find SOMEWHERE in the database, even in the master >>database (gasp! shock! horror!). If done right, it'll be static for >>quite some time (years), and should be relatively obvious for a database >>geek to realize if it's getting near the end of time to extend it again. > > > Maybe there is one. May there isn't one. Maybe the DBA for political > reasons will not let use the table. Again, please stop cramming down > solutions down people's throat, when they clearly tell you that the > solution you have is not applicable! > If he can't create a calendar table, how's he gonna get a Holidays table
corey lawson (corey.lawson@ayeteatea.net) writes:
[quoted text, click to view] > If he can't create a calendar table, how's he gonna get a Holidays table > created? If you care to review the thread, you will find that he never asked for one, and that he also said that he didn't need the holidays. All he wanted was the dates for one single year. The holidays were invented by other people that, rather than trying to help, answered some imaginary question. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |