Skip to Content
0
Nov 24 at 10:28 AM

Pivot and last value not null

65 Views

   Hi can you help me please to add select last value in this query, just for nulls results 


select AcctCode,  YEAR, MONTH,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31] 
from (

select ShortName as 'AcctCode',  DAY, MONTH,YEAR,  val
 from (


 Select ShortName,  DAY, MONTH, YEAR,  sum(isnull([Solde ],0)+ isnull(ob, 0))  as 'Solde day'
 from (



		select a.ShortName,   day(RefDate) 'DAY', month(refdate) 'MONTH', year(refdate) 'YEAR',  sum(a.Debit-a.Credit) 'Solde ', 
		case when day(RefDate) between 1 and 31 then (select  sum(Debit-Credit) 'Solde'
														from JDT1 b
														where  b.RefDate<a.RefDate and [Account] like '51411002' and b.ShortName=a.ShortName	
														
			) end as 'OB'

		from JDT1 a
		inner join OACT c on a.ShortName=c.AcctCode
				where [Account] like '51411002'
		GROUP BY  a.ShortName, c.AcctName, day(RefDate) , month(refdate) , year(refdate), a.RefDate
) t

group by ShortName,  DAY, MONTH, YEAR

) s

CROSS APPLY (
  VALUES ([ShortName],isnull([Solde day] ,0
			)))CS
(Document,VAL)

)Q
 PIVOT
   (
   max(val ) FOR [DAY]
IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
   ) AS tPIVOT

    
   order by acctcode,YEAR, MONTH

Attachments

tt.png (15.9 kB)