Add per column of months per client

Question

Add per column of months per client

angelreynosog

Mr or Mrs. 500

Points: 595
More actions
January 23, 2019 at 7:40 am

#416645

I have this table that should be adding up each column of the months, this is a dynamics pivot, which if the date is changing the months are rotating.
I will have a column that will be the balance of the client (this will change according to the client's debt), if what I want is to add the months until and stop until the months that have already reached the balance of the client.
see example in excel box, for more explanation.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

angelreynosog - Wednesday, January 23, 2019 7:40 AM
I have this table that should be adding up each column of the months, this is a dynamics pivot, which if the date is changing the months are rotating.
I will have a column that will be the balance of the client (this will change according to the client's debt), if what I want is to add the months until and stop until the months that have already reached the balance of the client.
see example in excel box, for more explanation.
Thanks

What's your question?
Where is your query?
Where are the DDL and INSERT scripts to support your post?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

angelreynosog Mr or Mrs. 500 Points: 595 More actions · Answer 2

Phil Parkin - Wednesday, January 23, 2019 9:08 AM
angelreynosog - Wednesday, January 23, 2019 7:40 AM
I have this table that should be adding up each column of the months, this is a dynamics pivot, which if the date is changing the months are rotating.
I will have a column that will be the balance of the client (this will change according to the client's debt), if what I want is to add the months until and stop until the months that have already reached the balance of the client.
see example in excel box, for more explanation.
Thanks
What's your question?
Where is your query?
Where are the DDL and INSERT scripts to support your post?

DECLARE @cols AS VARCHAR(MAX)
DECLARE @query AS VARCHAR(MAX)

;with mylist as (
  select DATEADD(month,-12, DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) ) as [mnth]
  union all
  select DATEADD(month,1,[mnth])
  from mylist
  where [mnth] < DATEADD(month,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) )
  )
select [mnth]
into #mylist
from mylist
order by [mnth] desc

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(format([mnth],'MMM yyyy'))
        FROM #mylist
                     order by [mnth] desc
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
      ,1,1,'')

SET @query = 'SELECT CUSTNMBR, ' + @cols + ' FROM
      (
       select
         format([mnth],''MMM yyyy'') colname
        , b.CUSTNMBR
        , case a.SOPTYPE
                         when 3 then a.DOCAMNT
                         when 4 then a.DOCAMNT*-1
                         end Sales
       from #mylist
       cross join two..RM00101 b
       left join two..SOP30200 a on #mylist.mnth = DATEADD(month, DATEDIFF(month,0,a.DOCDATE), 0)
           and b.CUSTNMBR = a.CUSTNMBR

     ) sourcedata
      pivot
      (
       SUM([Sales])
       FOR [colname] IN (' + @cols + ')
      ) p '

--select @query -- use select to inspect the generated sql
execute(@query) -- once satisfied that sql is OK, use execute