Hi everyone,

I need to combine the following two sections bit I am having trouble. Can someone please assist?

with cte as(

select userid,tenantid,transactionid,created,submission,methodid,transactiontype,months,statusid,

```
ROW_NUMBER() over (partition by userid,tenantid order by created desc) RowNumber
```

from tbltransactionledger with (nolock)

where userid>1

```
and statusid=3
```

)

select sum (case when methodid=8 then 1 else 0 end) as FFL,

```
sum (case when methodid=7 then 1 else 0 end) as SHOPRITE,
sum (case when methodid in (1,2) then 1 else 0 end) as FREE,
sum (case when transactiontype=2 and months=1 and methodid<>7 then 1 else 0 end) as PayAsYouGoFixed,
sum (case when transactiontype=2 and months>1 then 1 else 0 end) as PremiumFixed,
sum (case when transactiontype=3 then 1 else 0 end) as PremiumDebitOrder
```

from cte

where [rownumber]=1 and tenantid=3

select

case when methodid=8 then 'Free for life'

when methodid=7 then 'Shoprite'

when methodid in (1,2) then 'Free'

when transactiontype=2 and months=1 then 'Pay-as-you-go/Fixed'

when transactiontype=2 and months>1 then 'Premium/Fixed'

when transactiontype=3 then 'Premium/Debit order'

else '' end as Method,

case

when tbltransactionledger.statusid<>3 then 'Status: ' + tbltransactionstatus.TransactionStatus

when methodid=8 and Created<DATEADD(day,-30,getdate()) then 'Expired'

when methodid=7 and Created<DATEADD(day,-30,getdate()) then 'Expired'

when methodid in (1,2) and Created<DATEADD(day,-30,getdate()) then 'Expired'

when transactiontype=2 and months=1 and Created<DATEADD(day,-30,getdate()) then 'Expired'

when transactiontype=2 and months>1 and Created<DATEADD(day,-30*months,getdate()) then 'Expired'

when transactiontype=3 then 'Debit order'

else 'Not Expired' end as Method2,

from tbltransactionledger

inner join tbltransactionstatus on tbltransactionstatus.StatusId=tbltransactionledger.StatusId

order by created desc