summation problem - 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 2005 Forums

Transact-SQL (2005)

summation problem

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2008-01-08 : 15:26:05

I have table a
amount startdate enddate
23 12/1/2005 1/2/2006
45 12/20/2005 1/6/3007
---

---
the earliest start date is 12/1/2005 and latest enddate is 1/10/2006

I WANT TO GET sum of amount of each day from 12/1/2005 to 1/10/2006

the result should like
date sumamount
12/1/2005 2346
12/2/2005 3456
---
12/20/2005 654
----

for 12/1/2005 the sumamount is sum of amount for all records with startdate<12/1/2005 and enddate >12/1/2005

for 12/20/2005 the sumamount is sum of amount for all records with startdate<12/20/2005 and enddate >12/20/2005

How can i have a query to do that?
Thank you very much for any suggestions
Jeff

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-08 : 18:03:59

I'm not sure I got all thsoe rules, but I think this might work:

DECLARE @TableA TABLE (Amount INT, StartDate DATETIME, EndDate DATETIME)
INSERT @TableA
SELECT 23, '2005-12-01', '2006-01-02'
UNION ALL SELECT 45, '2005-12-20', '2007-01-06'


SELECT 
	B.Date,
	(SELECT SUM(COALESCE(Amount, 0)) FROM @TableA WHERE StartDate <> B.Date) AS SumAmount
FROM
	(
		SELECT DISTINCT 
			DATEADD(DAY, number, '2005-12-01') AS Date
		FROM 
			Master.dbo.spt_values
		WHERE 
			number between 0 AND DATEDIFF(DAY, '2005-12-01', '2006-01-10')
	) AS B

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-08 : 18:13:00

declare @yourtable table ( amount int, startdate datetime, enddate datetime )

insert @yourtable ( amount, startdate, enddate )
select 23, '12/1/2005', '1/2/2006' union all
select 45, '12/20/2005', '1/6/2006' union all
select 15, '12/1/2005', '12/5/2005' union all
select 40, '12/10/2005', '1/2/2006' union all
select 53, '12/15/2005', '1/1/2006'

declare @maxdate datetime, @mindate datetime

select @maxdate = max(enddate), @mindate = min(startdate) from @yourtable

declare @datedim table ( date datetime )

while @maxdate >= @mindate
begin
insert @datedim ( date )
select @mindate

set @mindate = dateadd(day, 1, @mindate)
end

select dd.date, sum(amount) from @yourtable yt
join @datedim dd on yt.startdate < dd.date
and yt.enddate > dd.date
group by dd.date

jeff06
Posting Yak Master

166 Posts

Posted - 2008-01-09 : 10:24:51

Thank you very much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources