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: 1415

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272096

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: 1269633

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