Daily totals in sql - 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 2000 Forums

SQL Server Development (2000)

Daily totals in sql

Author

Topic

spartus1057
Starting Member

4 Posts

Posted - 2005-07-29 : 19:28:49

Hello -
I am having some trouble calculating the daily totals of a value. The table is updated once per day and i need to subtract todays value from the previous day in its own coloum. I am also just trying to display the total for the last 30 days. that is no problem, but calculating the daily total is where i run into problems.

thanks in advance.

Spart

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-29 : 21:35:48

Got sample data?

spartus1057
Starting Member

4 Posts

Posted - 2005-07-30 : 00:45:42

Here is some sample data with the desired result
Log_Time XTO_SHIP_24H Desired
7/14/2005 23:59 251763008
7/15/2005 23:59 252937168 252937168-251763008 = 1174160
7/16/2005 23:59 253911408 253911408-252937168 = 974240
7/17/2005 23:59 254939264 254939264-253911408 = 1027856

Thanks,
Spart

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-30 : 01:30:40

As long as your log_time values all have the same time:

SELECT A.log_time, A.XTO_SHIP_24H, (A.XTO_SHIP_24H - B.XTO_SHIP_24H) AS Desired
FROM YourTable A LEFT JOIN YourTable B
ON A.log_time -1 = B.log_time
ORDER BY A.log_time

spartus1057
Starting Member

4 Posts

Posted - 2005-09-12 : 15:06:03

Thanks, the problem is that the times are different so the row that it brings back are all null. the time is close, is there any way around that?

Thanks!

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-09-12 : 18:59:37

Here are two approaches:

OK approach - works as long as dates are exactly one day apart (only if there was a previous day):
[CODE]SELECT DATEADD(d,DATEDIFF(d,0,A.log_time),0) AS LogDate,
A.XTO_SHIP_24H,
(A.XTO_SHIP_24H - B.XTO_SHIP_24H) AS DailyValue
FROM dbo.YourTable
AS A
JOIN dbo.YourTable
AS B
ON DATEADD(d,DATEDIFF(d,0,B.log_time)-1,0)
= DATEADD(d,DATEDIFF(d,0,A.log_time),0)
ORDER BY A.log_time[/CODE]

Better, but slower - works when dates are any amount of time appart
[CODE]SELECT A.log_time,
A.XTO_SHIP_24H,
(A.XTO_SHIP_24H - B.XTO_SHIP_24H) AS DailyValue
FROM dbo.YourTable
AS A
JOIN (
SELECT t1.log_time,
MAX(t2.log_time) AS ClosestPreviousLogTime
FROM dbo.YourTable
AS t1
JOIN dbo.YourTable
AS t2
ON t1.log_time
> t2.log_time
)
AS z
ON A.log_time
= z.log_time
JOIN dbo.YourTable
AS B
ON B.log_time
= z.ClosestPreviousLogTime
ORDER BY A.log_time[/CODE]

spartus1057
Starting Member

4 Posts

Posted - 2005-09-20 : 20:39:05

Thanks lazerath, the second one worked great. I am still new to this, I am looking to get the data for the last 30 days, and i am thinking that i should just add another row to my table and use this calc in a stored procedure? or just do the calcs every time i want them returned in the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources