While loop with dynamic dates - 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

General SQL Server Forums

New to SQL Server Programming

While loop with dynamic dates

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-10-20 : 17:00:47

Hi I have a query which i have put in a while loop. There are two problems for which i'm seeking help :

1. The while loop shows the correct result but it does it as a separate query for each loop executed. I'd like all the results in a single output

2. How can i loop so that it shows today, yesterday, day b4 yesterdays date as the loop executes

so if i execute it for two loops by setting @intflag <=2, the query will be executed two times :

First result
2009-10-19 00:00:00.000 I 1 1 0 3
2009-10-19 00:00:00.000 P 0 0 3 4
2009-10-19 00:00:00.000 R 0 0 3 6

second result :
2009-10-19 00:00:00.000 car 1 1 0 3
2009-10-19 00:00:00.000 bus 0 0 3 4
2009-10-19 00:00:00.000 truck 0 0 3 6

also it just shows the current date.
i'd like the results like the one given below
2009-10-19 00:00:00.000 car 1 1 0 3
2009-10-19 00:00:00.000 bus 0 0 3 4
2009-10-19 00:00:00.000 truck 0 0 3 6
2009-10-18 00:00:00.000 car 1 1 0 3
2009-10-18 00:00:00.000 bus 0 0 3 4
2009-10-18 00:00:00.000 truck 0 0 3 6

DECLARE @intFlag INT
DECLARE @dt DATETIME
SET @intFlag = 1
SET @dt = getdate()
WHILE (@intFlag <=30)
BEGIN
SELECT * FROM
( SELECT
CONVERT(datetime, CONVERT(varchar(10), @dt, 111), 111)[reference date],
d.sym [fuel_type],
a.type [vehicle_type]
FROM call_req a
left outer join mdb..fuel d on a.fuel = d.enum
left outer join mdb..contact c on a.group = c.contact
where
dateadd (hh,-7,dateAdd(ss, a.open_date, '19700101')) <= @dt - @intFlag
and (dateadd (hh,-7,dateAdd(ss, a.close_date, '19700101')) > @dt
or a.status != 'filled')
and c.last_name like 'BMW'
) AS abc
PIVOT
(
COUNT(fuel_type)
FOR fuel_type IN([petrol],[diesel],[natural],[hybrid])
) AS Pvt

SET @intFlag = @intFlag + 1
END

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-10-21 : 11:41:18

I think i made it too complicated to suit my needs. I have simplified it now. It prints the results of each loop separately. How do I get the results in a single table.. mabe be in a temp table

DECLARE @intFlag INT
DECLARE @intFlag2 INT
DECLARE @dt DATETIME
DECLARE @ref_date DATETIME
SET @intFlag = 0
SET @intFlag2 = 0
SET @dt = getdate()
SET @ref_date = @date - @intflag
WHILE (@intFlag <=30)
BEGIN
SELECT col1,col2,col3 from testtable
where
dateadd (hh,-7,dateAdd(ss, open_date, '19700101')) <= @dt - @intFlag
and (dateadd (hh,-7,dateAdd(ss, close_date, '19700101')) > @dt - intflag
SET @intFlag = @intFlag + 1
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 11:48:31

where do you get first and second result from? are they different queries?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources