SQLTeam.com | Weblogs | Forums

Combining case when with common table expression and sum

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

please cleanup and repost? hard to make heads or tails out of it.

Okay thanks. I will try to make it easier to understand, I dont know why it went all weird when I posted it.

FIRST BIT OF CODE

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

SECOND BIT OF CODE

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

I HOPE THIS HELPS TO MAKE IT EASIER TO READ

what is the final result you want to see