on 11-24-2022 10:28 AM
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
User | Count |
---|---|
86 | |
9 | |
7 | |
6 | |
6 | |
3 | |
3 | |
3 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.