generating temp table of dates for specific date r

Results 1 to 2 of 2

Thread: generating temp table of dates for specific date r

  1. #1
    Join Date
    Dec 1969

    Default generating temp table of dates for specific date r

    Hi there.<BR><BR>I&#039;m trying to write a stored proc or function in SQL Server 2000:<BR><BR>I have a table that contains a store_key and a start_date for each store. I have as a parameter an end_date. I would like to generate a table that contains a record for each day between the start date and the end date for each store.<BR><BR>here&#039;s the table:<BR>store_key start_date<BR>75 2002-07-08<BR>76 2002-07-08<BR>77 2002-07-15<BR>78 2002-07-15<BR><BR>here&#039;s the parameter:<BR>end_date = 2002/07/20<BR><BR>I&#039;d like to generate a temp table:<BR>store_key date<BR>75 2002-07-08<BR>75 2002-07-09<BR>75 2002-07-10<BR>...<BR>75 2002-07-20<BR>...<BR>76 2002-07-08<BR>.<BR>.<BR>.<BR><BR>I know the syntax for generating a temp table etc... I just can&#039;t wrap my head around an SQL statement to pull this off w/o resorting to a cursor - which I&#039;d like to avoid...<BR><BR>thanks in advance for the help.<BR><BR>-Jason

  2. #2
    Join Date
    Dec 1969

    Default The only way I see...

    ...would be to have a separate table of all possible dates (that is, all dates you might encounter from start to end) and then JOIN with it.<BR><BR>Otherwise, a cursor seems the only way, since you can&#039;t generate a table out of thin air any other way. That I know of.<BR><BR>If this is something you do often, then creating the silly table of all dates might be worth the trouble, since the JOIN performance would surely be better than the cursor performance.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts