SQL Query to find the sum between intervals

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)

SQL Query to find the sum between intervals

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-10 : 08:01:24

Deepa writes "Problem:
I have a table with headcount as one field and modification date as another field. I want to find the headcount at every 2 months interval . Inputs will be the start date, end date, no of intervals ( every 2 months between the start date and the end date).

We tried using dateadd but still it was not giving the output properly.

Can anyone suggest me with a good solution?"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-10 : 09:18:45

Can you provide examples of data and desired output?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-10 : 09:20:54

You could try using something like

@startdate, @enddate ---date parameters

SELECT (DATEDIFF(m, @startdate, <date column in table>)/2), sum(headcount)
from mytable
where <date column> between @startdate and @enddate
group by (DATEDIFF(m, @startdate, <date column in table>)/2)

The only issue I see here is your remainder time after the last interval but before the end date.

theGrimm
Starting Member

4 Posts

Posted - 2004-02-13 : 03:34:41

CREATE TABLE #Dates(
theDate datetime)

DECLARE @CurrentDate datetime
SET @CurrentDate = @StartDate

WHILE @CurrentDate < @EndDate
BEGIN
INSERT INTO #Dates VALUES(@CurrentDate)
SET @CurrentDate = dateadd("m", 2, @CurrentDate)
END

...

SELECT * FROM HeadcountTable hc
JOIN #Dates d
ON d.theDate = hc.theDate

This might not be the most elegant way to do it, though. Note that this won't work properly unless you have an entry for every day.

If given an input of a number of intervals (say five intervals between the given periods), then
you could use

SET @CurrentDate = dateadd("m", (DATEDIFF("M", @StartDate, @EndDate) / @NumIntervals), @CurrentDate)

theGrimm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources