last_value not null in row SQL

[mari_outtaleb22]

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

[mari_outtaleb22]

Hi Johan, I want to show last sold in day by month, year and account,

the second solution dosen't work it will retorn 0 for nulls.

regards, have a good day.

[Johan_Hakkesteegt]

Hi,

Hard to say what the problem is. This query appears to work.

You could try using ISNULL on the numbered columns in the first line:

ISNULL([1], 0) AS [1], ISNULL([2], 0) AS [2], ISNULL([3], 0) AS [3] etc.

instead of:

[1], [2], [3] etc.

Regards,

Johan

[mari_outtaleb22]

Hi Johan, thanks for reapling,

I tried this solution it works just if the last value not null but if it was null, retourn null.

Regards.

[Johan_Hakkesteegt]

Hi,

Please try adding an else option to the case clause:

case when day(RefDate) between 1 and 31 then (... ...) else 0 end as 'OB'

Regards,

Johan

[Johan_Hakkesteegt]

Hi mari_outtaleb22,

"...the second solution dosen't work it will retorn 0 for nulls..."

The error is that the last value is not allowed to be NULL, so this is the only solution really. The only thing you can do is to make the query return a set value in case it is otherwise null. Of course this value does not have to be 0. It could be -1 or you could even cast everything to nvarchar and return '-' or '' (empty string).

Regards,

Johan

[mari_outtaleb22]

Hi Johan,

Okey then, thanks for your help.

Regards, have a good day.