SQL - Aggregation between two string rows

Question

I have a following table in which I have employee's transactions having salary as Code 'S' in it:

Id | Code | Amount  | Date
1  | B    | 40      | 2017-01-01
1  | S    | 45000   | 2017-01-02
1  | D    | 30000   | 2017-01-15
1  | B    | 15000   | 2017-01-20
1  | S    | 45000   | 2017-02-02
1  | B    | -20000  | 2017-02-04
1  | B    | -10000  | 2017-02-05

My objective is to see how many days does it take for the employee to drain all his salary

Here is the output I want:

Id | Code | Amount | Month | # days when all salary drains
1  | S    | 45000  | 01    | 20
1  | S    | 45000  | 01    | 0

I have followed this and tried following SQL query:

with cte as
     (
       select *,
        --CASE WHEN Transaction_Code in ('521', '522') then Transaction_Amt else null end as Last_V_ID
          -- find the latest 'V' ID per ArtNo
          max(case when Transaction_Code in ('521', '522') then Transaction_Amt end) 
          over (partition by INTERNAL_ACCT_NUM order by value_date) as Last_V_ID
       from [10.16.42.25].[Cross_Sell_PL].[dbo].[PL_NONPL_TRANS]
    WHERE INTERNAL_ACCT_NUM = '0103PBS8T6001'
    --order by value_date
     )
    select *,
     case when Transaction_Code in ('521', '522') then Transaction_Amt else
      ( lag(Last_V_ID,1,0) OVER (PARTITION BY INTERNAL_ACCT_NUM ORDER BY VALUE_DATE) ) 
      + Transaction_Amt end as running_balance,

       sum(case when Transaction_Amt < Last_V_ID then null else Transaction_Amt end)
       over (partition by INTERNAL_ACCT_NUM order by VALUE_DATE rows unbounded preceding)
    from cte
    order by Internal_Acct_num, value_date

The problem is I am not able to restrict the calculation between the code and also unable to calculate days.

@user1584253 . . . Somehow I suspect that the codes are related to what you want to do, but the data is not described and the calculation is not clear. — Gordon Linoff, Mar 01 '19 at 11:41
Your code doesn't seem to relate to the question. It uses `Transaction_Code`, which is nowhere in the example you cited above. Your desired output is also wrong. The second line seems to relate to the second month, yet, you have it listed as Month 01. Is the number of days in the second row because the salary hasn't been fully spent yet? Lastly, I hope this is some kind of home assignment, because if you have insight into your employees' spending patterns, I sure wouldn't want to work for you ;-) — SchmitzIT, Mar 11 '19 at 07:09

Dwight Reynoldson · Accepted Answer · 2019-03-04T15:29:04.870

Revised my answer based on your feedback.

The query now uses a common table expression to build a derived table for the salary amount and the dates per month on which they are payed, followed by another derived table that compiles a list of all of the transactions summed per calendar month.

The summed per month transactions are compared with the salaried amount and only where there is an amount that crosses the salary threshold do the summed days show (+1 is included as this includes the day the salary is paid as day 1).

The query as such shows 3 rows as the sample data provided includes what would be Decembers wage month, however there is no salaried amount to compare to from December hence the extra 0. I hope this helps.

declare @employee_transaction table (
    Id int,
    Code nvarchar(1),
    Amount int,
    Date Date
);

insert into @employee_transaction (id, Code, Amount, Date)
values
(1  , 'B'    , 40      , '2017-01-01'),
(1  , 'S'    , 45000   , '2017-01-02'),
(1  , 'D'    , 30000   , '2017-01-15'),
(1  , 'B'    , 15000   , '2017-01-20'),
(1  , 'S'    , 45000   , '2017-02-02'),
(1  , 'B'    , -20000  , '2017-02-04'),
(1  , 'B'    , -10000  , '2017-02-05');


with Salaries as (Select INTERNAL_ACCT_NUM, Transaction_Code, Value_Date, 
Transaction_Amt as Salary_Per_Month from #test1 e where 
Transaction_Code='S'), 
Deductions as (select sum(case when Transaction_Amt<0 then Transaction_Amt 
else 0 end) as Amount, Max(Value_Date) as Drain_date from #TEST1 e2 
where Transaction_Code <>'S' and Value_Date>(select Value_Date from #TEST1 
e3 where Transaction_Code='S' and 
DATEPART(month,e2.Value_Date)=DATEPART(month,e3.Value_Date) 
and e3.Internal_Acct_Num=e2.Internal_Acct_Num) 
group by DATEPART(month,e2.Value_Date)) 

select distinct s.INTERNAL_ACCT_NUM, s.Transaction_Code, s.Salary_Per_Month, 
DATEPART(Month,e.Value_Date) as Month, 
coalesce(DATEDIFF(Day,S.Value_Date,(select d.Drain_Date from Deductions d 
where d.Amount+S.Salary_Per_Month<=0 and 
DatePart(month,d.Drain_Date)=DatePart(month,e.Value_Date) and 
d.drain_date>=s.value_date))+1,0) 
from #TEST1 e 
inner join Salaries s on e.Transaction_Code=s.Transaction_Code 
and e.INTERNAL_ACCT_NUM=s.INTERNAL_ACCT_NUM and s.Value_Date>=e.Value_Date 
and DATEPART(month, s.Value_Date)=DATEPART(month, e.Value_Date) 
order by Month;

S, B and D are the transaction codes. S refers to salary. My objective is to see how many does it take a customer take to drain his salary. If amount of withdrawal is same or above the salary amount then we calculate number of days, but if amount of withdrawal is less than salary before next salary then it shows 0 days — user1584253, Mar 03 '19 at 11:47
@user1584253 please see my revised answer and let me know if you have any further troubles adapting this for your purpose. — Dwight Reynoldson, Mar 03 '19 at 20:08
In deductions cte, it gives error subquery returned more than 1 value — user1584253, Mar 04 '19 at 08:08
I've edited the deductions cte to now include the id, if the employee is paid more than once in a month then this might cause an issue otherwise it should work fine. — Dwight Reynoldson, Mar 04 '19 at 08:22
@user1584253, I'm getting 3 rows returned just running that query. The first row represents the "40" transaction from the 1st of Jan before user Id 1 got paid. Hence 0 days because there is no matching salary for December. The second row shows 19 days as the user was paid on the 2nd of January and then salary reaches 0 on the 20th of January. Finally a third row is shown for February as there are only 2 negative transactions for February which do not bring the Salary to 0. Hence 0 days are shown. — Dwight Reynoldson, Mar 04 '19 at 11:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189390/discussion-between-dwight-reynoldson-and-user1584253). — Dwight Reynoldson, Mar 04 '19 at 11:30

SQL - Aggregation between two string rows

1 Answers1