Better solution for 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)

Better solution for query

Author

Topic

bilderbach
Starting Member

1 Post

Posted - 2005-03-30 : 18:45:35

Now I am passing in the interval and the begindate and hard coding 7 days for a weeks worth of data.

Looking for generic way to do this query rather than hard coding the 7 days. Would like to come up with a way to loop though from a begin_date to an end_date.

Now I have to do something similar for another report and want to reuse, but not sure on best way to make this part reusable...

sequentialvalues is just a view that returns 1-1000 numbers.

SELECT dt = dateadd(minute, id * @inInterval, @dtBeg )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, @dtBeg) BETWEEN @dtBeg AND @dtEnd

union all

SELECT dt = dateadd(minute, id * @inInterval, dateadd(day,1,@dtBeg) )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, dateadd(day,1,@dtBeg) ) BETWEEN dateadd(day,1,@dtBeg) AND dateadd(day,1,@dtEnd)

union all

SELECT dt = dateadd(minute, id * @inInterval, dateadd(day,2,@dtBeg) )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, dateadd(day,2,@dtBeg) ) BETWEEN dateadd(day,2,@dtBeg) AND dateadd(day,2,@dtEnd)

union all

SELECT dt = dateadd(minute, id * @inInterval, dateadd(day,3,@dtBeg) )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, dateadd(day,3,@dtBeg) ) BETWEEN dateadd(day,3,@dtBeg) AND dateadd(day,3,@dtEnd)

union all

SELECT dt = dateadd(minute, id * @inInterval, dateadd(day,4,@dtBeg) )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, dateadd(day,4,@dtBeg) ) BETWEEN dateadd(day,4,@dtBeg) AND dateadd(day,4,@dtEnd)

union all

SELECT dt = dateadd(minute, id * @inInterval, dateadd(day,5,@dtBeg) )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, dateadd(day,5,@dtBeg) ) BETWEEN dateadd(day,5,@dtBeg) AND dateadd(day,5,@dtEnd)

union all

SELECT dt = dateadd(minute, id * @inInterval, dateadd(day,6,@dtBeg) )
FROM sequentialvalues
WHERE dateadd(minute, id * @inInterval, dateadd(day,6,@dtBeg) ) BETWEEN dateadd(day,6,@dtBeg) AND dateadd(day,6,@dtEnd)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-30 : 23:20:06

Why don't you just add more numbers to your sequential table so that you'd only need the first SELECT statement?

andy8979
Starting Member

36 Posts

Posted - 2005-03-31 : 19:23:46

I would suggest that you probably try using the table variable or use a temp table to store the dates and join it to your query. This would make the query a bit simpler

Declare @TblDates Table (dt as datetime)
Insert Into @TblDates( @dtBeg )
.......... Insert all the seven days

And then join this @TblDates variable to your query and check the output.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-31 : 20:25:30

SELECT dt = dateadd(minute, s.id * @inInterval, dateadd(day,s2.id-1,@dtBeg) )
FROM sequentialvalues s, sequentialvalues s2
WHERE dateadd(minute, s.id * @inInterval, dateadd(day,s2.id-1,@dtBeg) ) BETWEEN dateadd(day,s2.id-1,@dtBeg) AND dateadd(day,s2.id-1,@dtEnd)
and s2.id between 1 and 7

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources