I am trying to join a table with itself and then sum over 4 weeks. Below is the sample data

When I am trying to join the table with itself to sum all the amounts for same TIN within 4 weeks. I am getting a duplicate row. As per the table sum should have been 17500 but the sum I am getting is 26250. Below is the code that I developed

Select 
    sub.TIN as TIN,
    sub.Direction as Direction,
    sum(sub.amount) as Sum_Amt,
    min(sub.B_Date) as From_Date,
    max(sub.B_Date) as To_Date
from ind_cash_trnx_cond_tin main
join ind_cash_trnx_cond_tin sub on main.TIN = sub.TIN
    and main.B_Date <= sub.B_Date
    and DATEDIFF(wk,main.B_Date,sub.B_Date) <=4
    and sub.TIN = '10627503'
group by sub.TIN, sub.Direction

The Output

I tired to check and found there is a duplicate row. Below is the code

Select 
    sub.TIN as TIN,
    sub.Direction as Direction,
    sub.amount,
    sub.B_Date,
    sub.TransactionID
from ind_cash_trnx_cond_tin main
join ind_cash_trnx_cond_tin sub on main.TIN = sub.TIN
    and main.B_Date <= sub.B_Date
    and DATEDIFF(wk,main.B_Date,sub.B_Date) <=4

The output

The desired output is as below

Thanks!

CodePudding user response：

Should be that?

Input and Output

Code:

Select 
    sub.TIN as TIN,
    sub.Direction as Direction,
    sum(sub.amount) as Sum_Amt,
    min(sub.B_Date) as From_Date,
    max(sub.B_Date) as To_Date
from ind_cash_trnx_cond_tin main
INNER join ind_cash_trnx_cond_tin sub on main.TIN = sub.TIN
    and main.B_Date = sub.B_Date
    and DATEDIFF(wk,main.B_Date,sub.B_Date) <=4
   -- and sub.TIN = '10627503'
group by sub.TIN,
    sub.Direction,
    sub.amount

CodePudding user response：

If by "within 4 weeks" you meant "last 4 weeks from now" then the following query would do:

select TIN as TIN,
       Direction as Direction,
       sum(amount) as Sum_Amt,
       min(B_Date) as From_Date,
       max(B_Date) as To_Date
  from ind_cash_trnx_cond_tin
 where DATEDIFF(wk, B_Date, getdate()) > 4

or, if you need a rolling total then you'd use the following technique