sum function on basis of system date

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

SQL Server Development (2000)

sum function on basis of system date

Author

Topic

somsahi
Starting Member

23 Posts

Posted - 2006-10-02 : 14:34:42

Hi I have a column "TOTAL" which is adding numbers in it rows.I am using SUM Function for that.
Problem-I dont want the sum of all rows .I want sum of only last 5 days before system date.
for example if today is 29 october as per my computer.I want summantion of 23+25+26+27+28+29(24 not included as it is sunday)

is that possible if yes how?
present query SUM(TOTAL)

REGARDS

sa
Yak Posting Veteran

77 Posts

Posted - 2006-10-02 : 19:42:51

SELECT SUM(TOTAL) FROM YOURTABLE
WHERE YOURDATEFIELDHERE IN('10/23/2006','10/25/2006','10/26/2006','10/27/2006','10/28/2006','10/29/2006')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-02 : 21:39:57

[code]
declare @from datetime,
@to datetime

select @to = dateadd(day, datediff(day, 0, getdate()), 0)
select @from = dateadd(day, -6, @to)

select sum(TOTAL)
from TABLE t
inner join
(
select TOP 5 DATE
from dbo.F_TABLE_DATE(@from, @to)
where WEEKDAY_NAME <> 'Sun'
order by DATE DESC
) d
on t.DATECOL = d.DATE
[/code]

using my one of my favourate functino F_TABLE_DATE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 09:07:58

quote:
Originally posted by sa

SELECT SUM(TOTAL) FROM YOURTABLE
WHERE YOURDATEFIELDHERE IN('10/23/2006','10/25/2006','10/26/2006','10/27/2006','10/28/2006','10/29/2006')

You cant make it dynamic in this way

Madhivanan

Failing to plan is Planning to fail

sa
Yak Posting Veteran

77 Posts

Posted - 2006-10-03 : 20:13:44

use northwind

select ords.orderid,sum(orddtl.unitprice * orddtl.quantity) as Total from [order details] orddtl
inner join orders ords on orddtl.orderid = ords.orderid
where ords.orderdate between '1996-07-01 00:00:00.000' and '1996-07-31 00:00:00.000'
group by ords.orderdate,ords.orderid

how about this one? this doesnt exactly answer the question but
surely it will gives him the exact hint.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources