CODEWITHSUNDEEP
sqlsql-server-2008
Howard Hee
Howard Hee

Reputation: 929

Sum the different date total

I have following data in my sql table: 

 Tran_Date          |  Amount 
2013-05-01 20:09:49 | 50.00
2013-05-02 04:09:49 | 50.00
2013-05-02 20:09:49 | 500.00

I want sum the amount before next day 5am. The result should as below. 

Amount 
100.00 
500.00

I have try the following code,but the result is wrong:

SELECT DATEADD(hh, 5, DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd,1,f.TRAN_DATE)) AS sDate, 
       SUM(Amount) 
FROM TRAN_TABLE 
GROUP BY sDate

How do acheive this? thanks

Upvotes: 2

Views: 160

Answers (2)

Bohemian
Bohemian

Reputation: 425438

You need to extract a DATE after subtracting 5 hours and group by that:

SELECT
    CAST(DATEADD(hour, -5, TRAN_DATE) AS DATE) AS sDate,
    SUM(Amount)
FROM TRAN_TABLE
GROUP BY CAST(DATEADD(hour, -5, TRAN_DATE) AS DATE)

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

For any SQL Server version

select [Date]=DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR,-5,Tran_Date)),0),
       Total=SUM(Amount)
from tbl
group by DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR,-5,Tran_Date)),0)
order by [Date];

For SQL Server 2008+, you can use the DATE data type

select [Date]=CAST(DATEADD(HOUR,-5,Tran_Date) as date),
       Total=SUM(Amount)
from tbl
group by CAST(DATEADD(HOUR,-5,Tran_Date) as date)
order by [Date];

Upvotes: 4

Related Questions