CODEWITHSUNDEEP
sqlsql-servert-sql
PJLG
PJLG

Reputation: 105

Balance for each month by type

I have a table, in SQL-Server, with several records of input and output values with columns for type and date.

Something like that: 

DATE       |INPUT  |OUTPUT |TYPE
2018-01-10 | 256.35|       |A
2018-02-05 |       |  35.00|B
2018-02-15 |  65.30|       |A
2018-03-20 | 158.00|       |B
2018-04-02 |       |  63.32|B
2018-05-12 |       | 128.12|A
2018-06-20 |       |   7.35|B

I need help to make a query to returns the sum of inputs and outputs (as balance), per type, but it should return that sum at the end of each month, that is:

YEAR|MONTH|TYPE|BALANCE
2018|    1|A   | 256.35
2018|    1|B   |   0.00
2018|    2|A   | 321.65
2018|    2|B   | -35.00
2018|    3|A   | 321.65
2018|    3|B   | 123.00
2018|    4|A   | 321.65
2018|    4|B   |  59.68
2018|    5|A   | 193.53
2018|    5|B   |  59.68
2018|    6|A   | 193.53
2018|    6|B   |  52.33
2018|    7|A   | 193.53
2018|    7|B   |  52.33

Don't forget that the balance of each month is affected by the balance of the previous month, or in other words, the balance of each month is not only the movements of that month but of all the previous months also.

It should also be noted that it should include a record for each month of the year/type (up to the current date), even if a given month/type don't have movements, starting at the first month/year of the oldest movement and ending at actual date (in this case 2018 July).

Upvotes: 0

Views: 852

Answers (3)

iamdave
iamdave

Reputation: 12243

Assuming your source data is in the structure you have initially provided (ie: this is not the result of another query), this is a fairly straightforward transformation using a table of dates and a running total via an ordered sum.

If you already have a dates table, you can remove the first 2 ctes in this script:

declare @t table(DateValue date,InputAmount decimal(8,2),OutputAmount decimal(8,2),ProdType nvarchar(1));
insert into @t values
 ('2018-01-10',256.35,null,'A')
,('2018-02-05',null, 35.00,'B')
,('2018-02-15', 65.30,null,'A')
,('2018-03-20',158.00,null,'B')
,('2018-04-02',null, 63.32,'B')
,('2018-05-12',null,128.12,'A')
,('2018-06-20',null,  7.35,'B')
;

-- Min date can just be min date in the source table, but the max date should be the month end of the max date in the source table0
declare @MinDate date = (select min(DateValue) from @t);
declare @MaxDate date = (select max(dateadd(day,-1,dateadd(month,datediff(month,0,DateValue)+1,0))) from @t);

with n(n) as (select * from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(t))   -- Using a tally table, built a table of dates
    ,d(d) as (select top(select datediff(day,@MinDate,@MaxDate)+1) dateadd(day,row_number() over (order by (select null))-1,@MinDate) from n n1,n n2,n n3, n n4)
    ,m as (select p.ProdType                                                        -- Then join to the source data to create a date value for each posible day for each product type
                    ,d.d
                    ,dateadd(day,-1,dateadd(month,datediff(month,0,d)+1,0)) as m    -- And calculate a running total using a windowed aggregate
                    ,sum(isnull(t.InputAmount,0) - isnull(t.OutputAmount,0)) over (partition by p.ProdType order by d.d) as RunningTotal
            from d
                cross join (select distinct ProdType
                            from @t
                            ) as p
                left join @t as t
                    on d.d = t.DateValue
                        and p.ProdType = t.ProdType
            )
select m
        ,ProdType
        ,RunningTotal as Balance
from m
where m = d
order by m.d
        ,m.ProdType;

Output:

+-------------------------+----------+---------+
|            m            | ProdType | Balance |
+-------------------------+----------+---------+
| 2018-01-31 00:00:00.000 | A        |  256.35 |
| 2018-01-31 00:00:00.000 | B        |    0.00 |
| 2018-02-28 00:00:00.000 | A        |  321.65 |
| 2018-02-28 00:00:00.000 | B        |  -35.00 |
| 2018-03-31 00:00:00.000 | A        |  321.65 |
| 2018-03-31 00:00:00.000 | B        |  123.00 |
| 2018-04-30 00:00:00.000 | A        |  321.65 |
| 2018-04-30 00:00:00.000 | B        |   59.68 |
| 2018-05-31 00:00:00.000 | A        |  193.53 |
| 2018-05-31 00:00:00.000 | B        |   59.68 |
| 2018-06-30 00:00:00.000 | A        |  193.53 |
| 2018-06-30 00:00:00.000 | B        |   52.33 |
+-------------------------+----------+---------+

Upvotes: 0

George Menoutis
George Menoutis

Reputation: 7240

Result achieved, there you go:

declare @min_month datetime=(select dateadd(month,datediff(month,0,min([DATE])),0) from _yourtable)
declare @max_month datetime=(select dateadd(month,datediff(month,0,max([DATE])),0) from _yourtable)

;WITH months(d) AS (
  select @min_month
  UNION ALL
  SELECT dateadd(month,1,d) -- Recursion
  FROM months
  where dateadd(month,1,d)<=getdate()
)
select distinct
    year(m.d) as YEAR,
    month(m.d) as MONTH,
    types.v as [TYPE]
    ,sum(isnull(t.[INPUT],0)-isnull(t.[OUTPUT],0)) over (partition by types.v order by m.d)
from months m
cross join (select distinct type from _yourtable)types(v)
left join _yourtable t on dateadd(month,datediff(month,0,t.[DATE]),0)=m.d and types.v=t.TYPE
order by m.d,type
option(maxrecursion 0)

Upvotes: 2

Alankrit Agrawal
Alankrit Agrawal

Reputation: 109

You can use Lag function, below code might help:

select year(date), month(date), type
, sum(input-output) + isnull(lag(sum(input-output),1,0) over(order by year(date), month(date), type), 0)
from test group by year(date), month(date), type

Upvotes: 0

Related Questions