do until loop on query - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

do until loop on query

Author

Topic

hermiod
Starting Member

7 Posts

Posted - 2009-04-09 : 04:09:36

Hi all

I am a complete newbie to t-sql so I am hoping you guys can help me.

I have the following query:

SELECT '01-09', SUM(PVStart) - SUM(PVXfr) AS Starts, SUM(PVComp) + SUM(PVCont) AS Retained
FROM PV_Midpoint
WHERE pv_midpointid = '0506070809017'
AND PG_QType2ID not in (18,30)
AND PG_AimID NOT IN ('00116433','00114135')
AND SUBSTRING(PG_AimID,1,1) NOT IN ('Z','X')
AND PG_AimID NOT IN ('CMISC001','Q1053811','1004968X','1002489X','00114135','00263113','10014640','10020627')
AND SUBSTRING(PV_MidPoint.PG_AggCourseID,1,3) NOT IN ('E2E','C4S','TVC')
AND PV_MidPoint.PG_AgeGroupID IN (0,1)
AND PVStart = 1
AND PV_midpoint.PG_Structure2ID != '-'
AND pg_durationtypeid = 0
AND pg_expendyrid = '08/09'
AND StartDate <= CONVERT(datetime,'01/09/2008',103)

This query needs to be repeated until the date in the last line is within 7 days of the current date. The date string in the select list also needs to match the date in the where clause, but can only be dd-mm.
The result of the query needs to be added to a temporary table so that the results of all the queries are returned in one dataset for me to use in a report.

I am sure T-SQL can do this, but I don't even know where to start. I have read a few websites on doing loops, but I just don't understand it enough to be able to write one to suit my needs.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-09 : 05:20:58

Sorry, can you explain this a little better, possibly with some examples of what you are after as i do not get why you want to loop. You are not changing your date in the query (for example by using a variable), so why do you want to loop through records?

hermiod
Starting Member

7 Posts

Posted - 2009-04-09 : 05:51:37

Sorry about that, I probably should've explained it more clearly.

The date in the query will want to change but I just haven't got a clue where to start.

The plan is
Run the query for 01/09/2008.
This returns a single row which needs to be added to a temporary table.
The query runs again for 08/09/2008.
A single row is returned and added on to the same temporary table as the last query.

The query will be run over and over again, with the output being added to the temporary table, until the date is within 7 days of the current date.

So basically, the query is run for every 7 day period starting from 01/09/2008 up to the current week, with each week having a row added to the temporary table and then the temporary table being returned as the final dataset.

I know how to use variables to change the date, but I have got no idea how to do the looping to force the query to repeat for every week from 01/09/2008 up to the current week.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-09 : 06:10:41

You need to define the temp table first and you need to add aliases to all your fields in your select.

declare @dte datetime

set @dte = dateadd(dd,0,datediff(dd,0,'20080901'))

while @dte < dateadd(dd,-7,datediff(dd,0,getdate()))
begin
insert into #temp --This is your predefined temporary table, define it outside the while.
SELECT '01-09', SUM(PVStart) - SUM(PVXfr) AS Starts, SUM(PVComp) + SUM(PVCont) AS Retained
FROM PV_Midpoint
WHERE pv_midpointid = '0506070809017'
AND PG_QType2ID not in (18,30)
AND PG_AimID NOT IN ('00116433','00114135')
AND SUBSTRING(PG_AimID,1,1) NOT IN ('Z','X')
AND PG_AimID NOT IN ('CMISC001','Q1053811','1004968X','1002489X','00114135','00263113','10014640','10020627')
AND SUBSTRING(PV_MidPoint.PG_AggCourseID,1,3) NOT IN ('E2E','C4S','TVC')
AND PV_MidPoint.PG_AgeGroupID IN (0,1)
AND PVStart = 1
AND PV_midpoint.PG_Structure2ID != '-'
AND pg_durationtypeid = 0
AND pg_expendyrid = '08/09'
AND StartDate <= CONVERT(datetime,@dte,103)

set @dte = dateadd(dd,7,datediff(dd,0,@dte))
end

hermiod
Starting Member

7 Posts

Posted - 2009-04-09 : 08:55:03

Thank you greatly for your help RickD, I have tweaked what you posted and below is the result.

DECLARE @temp TABLE
(
Measure NVARCHAR(40),
MonthName NVARCHAR(40),
Starts INT,
Retained INT,
OrderID INT
)

declare @dte datetime
declare @Measure NVARCHAR(40)
declare @OrderID INT
declare @MonthName NVARCHAR(40)

set @dte = dateadd(dd,0,datediff(dd,0,'20080901'))
set @MonthName = datename(MONTH,@dte)
set @Measure = datepart(dd,@dte)
set @OrderID = 1

while @dte < dateadd(dd,-7,datediff(dd,0,getdate()))
begin
INSERT into @temp
SELECT @Measure AS Measure, @MonthName as MonthName, SUM(PVStart) - SUM(PVXfr) AS Starts, SUM(PVComp) + SUM(PVCont) AS Retained, @OrderID AS OrderID
FROM PV_Midpoint
WHERE pv_midpointid = '0506070809017'
AND PG_QType2ID not in (18,30)
AND PG_AimID NOT IN ('00116433','00114135')
AND SUBSTRING(PG_AimID,1,1) NOT IN ('Z','X')
AND PG_AimID NOT IN ('CMISC001','Q1053811','1004968X','1002489X','00114135','00263113','10014640','10020627')
AND SUBSTRING(PV_MidPoint.PG_AggCourseID,1,3) NOT IN ('E2E','C4S','TVC')
AND PV_MidPoint.PG_AgeGroupID IN (0,1)
AND PVStart = 1
AND PV_midpoint.PG_Structure2ID != '-'
AND pg_durationtypeid = 0
AND pg_expendyrid = '08/09'
AND StartDate <= CONVERT(datetime,@dte,103)

set @dte = dateadd(dd,7,datediff(dd,0,@dte))
set @MonthName = datename(MONTH,@dte)
set @Measure = datepart(dd,@dte)
set @OrderID = @OrderID + 1

end

SELECT * FROM @temp

It works perfectly, thanks a lot

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-09 : 09:18:31

No problem, just as a side note, table variables can be slow, so if you start getting speed issues, this will likely be your culprit. Instead, use a #temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources