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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-18 : 08:48:50
|
Sequeler writes "Hi,
My question may seem naive, nevertheless....I need to create a report in ms sql 7.0 (via stored procs) for a date range among other conditions. This date range should then be grouped by a) daily b) weekly c)monthly d)quarterly e)yearly based on user input.
The stored procedure, breaks down the daterange as per the user input, and then loops through some other procedures as many times as required. Evidently the execution times is proportional to the number of 'periods' within the date range. (If the dates fall 01-01-1999 to 01-10-1999, then there would be ten calls! I would find the date diff and since it is a daily report, a counter is set to 10.... )
sample code ...
:
:
@fromDate = @date1
@toDate = dateadd(@date1, -1, @fromdate)
while (@counter >=1)
begin
@fromDate = dateadd(day, 1,@todate)
/* for to date call a proc that will add the number of days necessary to @fromdate to get @todate */
exec set_date_proc
@fromDate, @freq,@toDate=@date2
/* then execute the required procs*/
execute proc_1 @fromDate, @toDate
execute proc_2 @fromDate, @toDate
:
:
end
And the procedures do nothing but query the tables for date that falls within @fromDate and @toDate .... as close to what follows as possible
select @fromDate as date1, @toDate as date2, count(distinct field_1) as field1
from view1
where date_1 between @fromDate and @toDate ..(and some other conditions not dependent on the date field)
result:
date1 date2 field1
-------- -------- -----------
01-01-99 01-10-99 660
Is there a faster, single query method that would accomplish this than n number of calls ? Something on the lines..
SELECT (format the fromdate) as date1, (format the todate) as date2, count(distinct field_1) as field1
FROM view1
WHERE date_1 between @fromDate and @toDate ..(and some other conditions not dependent on the date field)
GROUP BY .... having
order by....
I have tried some date functions without luck! Maybe there is a function I am not aware of that could help me in this query.\
TIA.
" |
|
|
|
|
|
|
|
Grouping by Dates for a ms sql 7 report