how to sum dialy transactions in SQL in a query

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)

how to sum dialy transactions in SQL in a query

Author

Topic

roykauf
Starting Member

36 Posts

Posted - 2008-02-05 : 04:34:20

Hi,

how can I sum daily transactions in an SQL Query?
each row should print the sum of daily transactions (from 00:00 to 23:59). the result should be: sum(transactions of this date),date
Isucceeded to do this -but with while loop and many selects.

for example - how can i make this query in one Select ?

declare @MaxDate dateTime,
@MinDate dateTime

set @MaxDate = convert(dateTime,Left(GETDATE(),11)) + '23:59:59' - 20
set @MinDate = convert(DateTime,Left(GETDATE(),11)) - 20

while (@mindate > '2007-11-01')
begin
select Sum(resellerSubnetPrice), @MinDate
from transactions
where ResellerSubnetID = 10762
AND initTime > @MinDate and initTime < @MaxDate
AND status = 1
set @minDate = @minDate - 1
set @MaxDate = @MaxDate - 1

end

your help will be much appreciated,

Roy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 04:37:19

select Sum(resellerSubnetPrice), DATEADD(d,DATEDIFF(d,0,initTime),0)
from transactions
where ResellerSubnetID = 10762
AND status = 1
GROUP BY DATEADD(d,DATEDIFF(d,0,initTime),0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 05:01:46

[code]CREATE PROCEDURE dbo.uspGetMyDailyStatistics
(
@ResellerSubnetID INT,
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

SELECT @MinDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @MinDate), '19000101'),
@MaxDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @MaxDate), '19000101')

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', InitTime), '19000101')
SUM(ResellerSubnetPrice) AS ResellerSubnetPrice
FROM Transactions
WHERE ResellerSubnetID = @ResellerSubnetID
AND Status = 1
AND InitTime >= @MinDate
AND InitTime < @MaxDate
GROUP BY DATEADD(DAY, DATEDIFF(DAY, '19000101', InitTime), '19000101')
ORDER BY DATEADD(DAY, DATEDIFF(DAY, '19000101', InitTime), '19000101')[/code]
Call with EXEC uspGetMyDailyStatistics 10762, '20071101', '20080205'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources