Is there a better way to write this SQL?

I don't like the idea of using a cursor and then also using a while
loop for each row. Is there a better way to do the same thing?
The problem is that I have a table called TimePeriods that is the
following:
CompanyID
TimePeriodID
BeginDate
EndDate
I need a table that has each date of the time period so I can look up
other data that is associated to a date.
---
CREATE FUNCTION dbo.f_GetDaysInTimePeriod()
RETURNS @.Results TABLE
(
CompanyID VARCHAR(5),
TimePeriodID INTEGER,
SearchDate SMALLDATETIME
)
AS
BEGIN
DECLARE @.CompanyID AS VARCHAR(5)
DECLARE @.TimePeriodID AS INTEGER
DECLARE @.InsertDate AS SMALLDATETIME
DECLARE @.ThruDate AS SMALLDATETIME
DECLARE TimePeriod_Cursor CURSOR FOR SELECT CompanyID, TimePeriodID,
BeginDate, ThruDate from TimePeriods
OPEN TimePeriod_Cursor
-- Perform the first fetch.
FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
@.InsertDate, @.ThruDate
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
WHILE @.InsertDate <= @.ThruDate
BEGIN
INSERT INTO @.Results values(@.CompanyID, @.TimePeriodID, @.InsertDate)
SELECT @.InsertDate = DATEADD(d,1,@.InsertDate)
END
FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
@.InsertDate, @.ThruDate
END
CLOSE TimePeriod_Cursor
DEALLOCATE TimePeriod_Cursor
RETURN
END
---
Thanks for any insight...Could you copy a small sample of records from the TimePeriods table and also
an example of your required output?
<standish22@.hotmail.com> wrote in message
news:1121881564.693074.9270@.o13g2000cwo.googlegroups.com...
>I don't like the idea of using a cursor and then also using a while
> loop for each row. Is there a better way to do the same thing?
> The problem is that I have a table called TimePeriods that is the
> following:
> CompanyID
> TimePeriodID
> BeginDate
> EndDate
> I need a table that has each date of the time period so I can look up
> other data that is associated to a date.
> ---
> CREATE FUNCTION dbo.f_GetDaysInTimePeriod()
> RETURNS @.Results TABLE
> (
> CompanyID VARCHAR(5),
> TimePeriodID INTEGER,
> SearchDate SMALLDATETIME
> )
> AS
> BEGIN
> DECLARE @.CompanyID AS VARCHAR(5)
> DECLARE @.TimePeriodID AS INTEGER
> DECLARE @.InsertDate AS SMALLDATETIME
> DECLARE @.ThruDate AS SMALLDATETIME
> DECLARE TimePeriod_Cursor CURSOR FOR SELECT CompanyID, TimePeriodID,
> BeginDate, ThruDate from TimePeriods
> OPEN TimePeriod_Cursor
> -- Perform the first fetch.
> FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
> @.InsertDate, @.ThruDate
>
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> WHILE @.InsertDate <= @.ThruDate
> BEGIN
> INSERT INTO @.Results values(@.CompanyID, @.TimePeriodID, @.InsertDate)
> SELECT @.InsertDate = DATEADD(d,1,@.InsertDate)
> END
> FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
> @.InsertDate, @.ThruDate
> END
> CLOSE TimePeriod_Cursor
> DEALLOCATE TimePeriod_Cursor
> RETURN
> END
> ---
> Thanks for any insight...
>|||I don't know what's in "TimePeriods" in your example but I don't see
why you can't just use a Calendar table containing as many dates as
you'll ever need:
CREATE TABLE Calendar (dt DATETIME PRIMARY KEY)
SELECT dt, ...
FROM Calendar
WHERE dt >= '20050101'
AND dt < '20060101'
Just join the Calendar table into your queries as required.
If you need mroe help, please post DDL, sample data INSERTs and show us
your required end result so that we can understand you better. I think
your cursor won't work anyway because you would need to specify ORDER
BY - cursors aren't ordered by default.
David Portas
SQL Server MVP
--|||>> I need a table that has each date of the time period so I can look up oth
er data that is associated to a date. <<
Build a Calendar table with all your temporal data. SQL likes tables
and not procedural code. You then use:
WHERE C.cal_date BETWEEN F.start_date AND F.end_date|||Sample data in the TimePeriods:
CompanyID TimePeriodID Descr BeginDate ThruDate
----
1 1 W1 5/30/2005 6/5/2005
1 2 W2 6/6/2005 6/12/2005
1 3 W3 6/13/2005 6/19/2005
I'm looking to return a table from the UDF that would be:
CompanyID TimePeriodID TimecardDate
1 1 5/30/2005
1 1 5/31/2005
1 1 6/1/2005
1 1 6/2/2005
1 1 6/3/2005
1 1 6/4/2005
1 1 6/5/2005
1 2 6/6/2005
1 2 6/7/2005
1 2 6/8/2005
1 2 6/9/2005
1 2 6/10/2005
1 2 6/11/2005
1 2 6/12/2005
1 3 6/13/2005
1 3 6/14/2005
1 3 6/15/2005
1 3 6/16/2005
1 3 6/17/2005
1 3 6/18/2005
1 3 6/19/2005|||Calendar table. Put the w number in there as well if it helps. Why
make life difficult by only storing the beginning and ending dates? You
can create the Periods table as an indexed view.
David Portas
SQL Server MVP
--|||to make it easier for the user.
They could have a time period be a w, a month, a year, or anything.
Having them enter all the days of each time period would be
unacceptable. This calendar table will probably work quite nicely,
however. I've never seen that technique.
Thanks...|||No-one says the user has to enter the days individually. Generate all
the rows once only, at install time. The user changes are just:
UPDATE ...
WHERE dt BETWEEN ...
David Portas
SQL Server MVP
--|||>> I've never seen that technique. <<
Get a copy of SQL FOR SMARTIES; you are in for a lot of surprises :)