Using SUM to add values within multiple date range

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)

Using SUM to add values within multiple date range

Author

Topic

Airhead315
Starting Member

1 Post

Posted - 2009-01-12 : 22:51:01

The Subject makes this seem easier than it is.

I have two tables like this:

Agreements (Id, AdvertisementId, AgreementBeginDate, AgreementEndDate)

Ads (Id, NumberOfSlots, AdvertisementId, OccuredAt)

There are other columns, but I ommitted them since they are not neccessay.

Customers are given unlimited advertisements that occur within an agreement. All ads not within an allotted agreement need to have thier NumberOfSlots summed up so they can be billed on a per slot basis.

Of course you can build a query like this:

Select SUM(NumberOfSlots) from Ads A where (OccurredAt > 'somedate' and OccuredAt < 'some-later-date') OR (OccurredAt > 'someotherdate' and OccuredAt < 'someother-later-date')...which I can do using PHP, but I would like if I could use a single query to do this for me.

I could also have a counter count up each time an ad is added, but it is better to have the query so I dont have to account for situations where an Ad needs to be removed due to entry error...etc...

Any Ideas?

Thanks,
Aaron

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-12 : 23:12:26

try this to get the out of range sum:

select a.AdvertisementId, sum(isnull(ad.NumberOfSlot, 0))
from  dbo.Agreements ag
left
join  dbo.Ads ad on 
      (ag.AdvertisementId = ad.AdvertisementId) and 
      (ad.OccuredAt between ag.AgreementBeginDate and AgreementEndDate)
group
by    a.AdvertisementId

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources