 | |
Re: Avoiding an inline view using DML only
comp.databases.theory comp.databases.theory
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Avoiding an inline view using DML only -
04-24-2008
, 04:59 AM
|
This is a common problem and a common solution is to have a table with integers from zero to some large number (32,767 is common) as described at http://sqlserver2000.databases.aspfa...ers-table.html Although the SQL statements are specific to MS SQL Server, they can be adapted to other RDBMS. Here is part of the article: Generating date ranges When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. |
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.
create table almanac
(adate date,
other columns not relevant to this issue);
Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.
This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.
#12
| |||
| |||
|
|
This is a common problem and a common solution is to have a table with integers from zero to some large number (32,767 is common) as described at http://sqlserver2000.databases.aspfa...ers-table.html Although the SQL statements are specific to MS SQL Server, they can be adapted to other RDBMS. Here is part of the article: Generating date ranges When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. |
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.
create table almanac
(adate date,
other columns not relevant to this issue);
Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.
This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.
#13
| |||
| |||
|
|
This is a common problem and a common solution is to have a table with integers from zero to some large number (32,767 is common) as described at http://sqlserver2000.databases.aspfa...ers-table.html Although the SQL statements are specific to MS SQL Server, they can be adapted to other RDBMS. Here is part of the article: Generating date ranges When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. |
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.
create table almanac
(adate date,
other columns not relevant to this issue);
Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.
This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.
#14
| |||
| |||
|
|
This is a common problem and a common solution is to have a table with integers from zero to some large number (32,767 is common) as described at http://sqlserver2000.databases.aspfa...ers-table.html Although the SQL statements are specific to MS SQL Server, they can be adapted to other RDBMS. Here is part of the article: Generating date ranges When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. |
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.
create table almanac
(adate date,
other columns not relevant to this issue);
Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.
This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.
#15
| |||
| |||
|
|
This is a common problem and a common solution is to have a table with integers from zero to some large number (32,767 is common) as described at http://sqlserver2000.databases.aspfa...ers-table.html Although the SQL statements are specific to MS SQL Server, they can be adapted to other RDBMS. Here is part of the article: Generating date ranges When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. |
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.
create table almanac
(adate date,
other columns not relevant to this issue);
Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.
This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.