CODEWITHSUNDEEP
sql-servert-sqljoingroup-by
daniel charlesworth
daniel charlesworth

Reputation: 23

Sum column based on date range from another table

I have two tables.

Transactions

Transactions

Date time range

Date time range

I'm trying to sum Col1 for the transactions between (and including) each start and end time in table 2.

I'm expecting:

range 1 = 3
range 2 = 2
range 3 = 1

Upvotes: 0

Views: 1416

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272386

You can use a simple JOIN and a GROUP BY:

SELECT ranges.id, ranges.start, ranges.end, SUM(transactions.col1)
FROM ranges
LEFT JOIN transactions ON transactions.datetime >= ranges.start AND
                          transactions.datetime <= ranges.end
GROUP BY ranges.id, ranges.start, ranges.end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271031

One method is a correlated subquery:

select dr.*,
       (select sum(t.col1)
        from transactions t
        where t.datetime >= dr.start and
              t.datetime <= dr.end
       ) as cnt
from daterange dr;

Upvotes: 2

James946
James946

Reputation: 26

You could try the following, replacing the LEFT join if required.

select d.id,min(start),max(end), sum(isnull(t.col1,0.00)) 
from DateRange d
left join transactions t on 1=1 and t.dateime between d.start and d.end
group by d.id

Upvotes: 0

zip
zip

Reputation: 4061

try this:

select Start, End, sum(Col1) from Transactions t inner join [Date time range] d on [datetime] between Start and End
group by Start, End

Upvotes: 1

Related Questions