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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-12 : 07:16:00
|
| Jon writes "I am trying to without have to do individual queries for each day in a stored procedure look to grab data on each day within a date span. Not just the days that have points in a data set. For example. Here is the table data. DATE QTY 1/4/03 1 1/10/03 4 1/12/03 3 I want to run a query that returns the following DATE Date 1/1/03 0 1/2/03 0 1/3/03 0 1/4/03 1 1/5/03 0 1/6/03 0 1/7/03 0 1/8/03 0 1/9/03 0 1/10/03 4 1/11/03 0 1/12/03 3 So far the only thing I can seem to get this response is to use a While loop for each date and preform my query on each day and print it. This causes no recordset to be returned when used from asp pages with an ado recordset. Is there a way to have a select statement return each day in a date span reguardless of data points. Or is thiere a way to union all these data points into one recordset. jb" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-12 : 07:36:13
|
Yes. Create a tally table. This tool is useful for many things.create table numbers (n int not null constriant pk_numbers primary key) Populate numbers with ints from 1 to 10K or whatever. Then you can join to numbers to perform your query.select @start + n as 'date', sum(qty) as qtyfrom toolbox.dbo.numbers left join jon on @start + n = [date]where @start + n <= @endgroup by @start + n Jay White{0} |
 |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-12 : 09:11:32
|
| create a table datatype (or temporary table if you are using SQL 7).declare @tDates table (@my_date datetime)Expend into it the days. Simply loop from @start_date to @end_date using DATEADD of dy. Then OUTER join both table.Bambola. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-12 : 10:15:58
|
| Why is that better than what I suggested?Jay White{0} |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-12 : 19:53:50
|
I think it is, Jay, for a few reasons. It is more clear to join to columns of the same datatype.It is also better to avoid the (date + int) calculation within the select statment. But if you still feel it is the best approach, I recomend the use of DATEADD.Run a few tests and see for yourself  BTW, Jon, if dates have the time portion and you want to group them by day, you will have to refer to the date part only. One way to do it (not the most efficiant, but clear and simple enough) is to use convert(varchar(10), your_date_field,your_date_format). Bambola. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-13 : 07:23:40
|
| I'll give you the dateadd() arguement, although the difference is hardly measurable (<10ms for 10,000 calculations) ...However, creating a temp table (or table variable) on each execution of the stored procedure will never be faster than using a tally table. Especially as the date range gets wider and wider ...AND, the best way to get just-the-date part of a datetime datatype isselect dateadd(dd,datediff(dd,0,your_date_COLUMN),0)Jay White{0} |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-14 : 08:38:40
|
| It is not about which type of table to use, but about what do you put in it. I could not figure out the choice of int over datetime. it is clear to me that the dates might need to be updated avery once in a while, but the improvment in performance when joining two dates worth the trouble. Bambola. |
|
|
|
|
|
|
|
|
Select Query displaying data for each day in a date span