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