The maximum date when the contract number is entered in the accounting

1 
  select distinct r.[VBEL2] as contractno ,max (o.budat) as BookingDay, o.BELNR as voucher_number

FROM [STG_SAP].[dbo].[tb_stg_BSEG_KirjanpidonRivitasonKirjaukset] r
join [STG_SAP].[dbo].[tb_stg_BKPF_KirjanpidonTositetiedot] o on o.BELNR = r.BELNR
where o.budat > '2019-01-01'
and
r.[VBEL2] in ('0001000003')
group by o.budat, o.BELNR, r.[VBEL2]
order by r.[VBEL2]

Result now

image

I would like the largest posting date, when the contract number was posted and this voucher number

2

hi

hope this helps

; WITH CTE AS 
(
SELECT 
      r.[VBEL2] as contractno , o.budat as BookingDay	, o.BELNR as voucher_number
		, ROW_NUMBER() OVER( ORDER BY o.budat DESC ) as RN 
FROM  [STG_SAP].[dbo].[tb_stg_BSEG_KirjanpidonRivitasonKirjaukset] r
         JOIN 
      [STG_SAP].[dbo].[tb_stg_BKPF_KirjanpidonTositetiedot] o on o.BELNR = r.BELNR
WHERE o.budat > '2019-01-01'    AND   r.[VBEL2] IN ('0001000003')
)
SELECT * FROM CTE WHERE RN = 1
3

Hi

It helped, but if I want to search for the largest booking day in vouceher number from several contract numbers. This does not work.

; WITH CTE AS
(
SELECT
r.[VBEL2] as contractno , o.budat as BookingDay , o.BELNR as voucher_number
, ROW_NUMBER() OVER( ORDER BY o.budat DESC ) as RN
FROM [STG_SAP].[dbo].[tb_stg_BSEG_KirjanpidonRivitasonKirjaukset] r
JOIN
[STG_SAP].[dbo].[tb_stg_BKPF_KirjanpidonTositetiedot] o on o.BELNR = r.BELNR
WHERE o.budat > '2019-01-01' AND r.[VBEL2] IN (
'0005069078',
'0005386462',
'0005390024',
'0001004278',
'0001003912',
'0001003921',
'0001003924',
'0001005435',
'0001005486',
'0008209285',
'0001006262',
'0001007083',
'0008206774',
'0008206775',
'0008209043',
'0008209046',
'0001010606',
'0001000063',
'0001000094',
'0001000179',
'0001000935',
)
SELECT * FROM CTE WHERE RN = 1

image

4

hi

try this

; WITH CTE AS
(
SELECT
    o.budat as BookingDay , o.BELNR as voucher_number , ROW_NUMBER() OVER( ORDER BY o.budat DESC ) as RN
FROM [STG_SAP].[dbo].[tb_stg_BSEG_KirjanpidonRivitasonKirjaukset] r
JOIN
[STG_SAP].[dbo].[tb_stg_BKPF_KirjanpidonTositetiedot] o on o.BELNR = r.BELNR
WHERE o.budat > '2019-01-01' AND r.[VBEL2] IN (
'0005069078',
'0005386462',
'0005390024',
'0001004278',
'0001003912',
'0001003921',
'0001003924',
'0001005435',
'0001005486',
'0008209285',
'0001006262',
'0001007083',
'0008206774',
'0008206775',
'0008209043',
'0008209046',
'0001010606',
'0001000063',
'0001000094',
'0001000179',
'0001000935',
)
SELECT * FROM CTE WHERE RN = 1