none
TOP 5 ve Diğerlerin toplamı RRS feed

  • Soru

  • Merhabalar,

    Aşağıdaki gibi bir sorgum var ve gelen sonucu da yine aşağıda ekledim.

    Sorguda en çok harcama yapılan işyerleri bulunuyor, benim amacım en çok harcama yapılan 5 işyeri ve bir alt satırda da diğerlerinin toplamı olacak şekilde sonucun gelmesini istiyorum. Yan toplam 6 satır olacak, ilk 5 satırda en çok alış veril yapılan merchant olacak, en alt satırda da ilk 5 harici diğer merchantların toplamı olacak.

    Bunu yapabilmem mümkün mü? 

    Teşekkürler.

    SELECT 
    C.MERCHANT_NAME,
    COUNT(DISTINCT C.CARD_REF_NUMBER) AS 'Tekil Kart Sayısı',
    SUM(C.LOCAL_AMOUNT)  AS 'Harcama Tutarı',
    SUM(A.PRIZE_AMOUNT) AS 'Ödül Tutarı'
    
    FROM CampaignManagement.CRM.SCENARIO_PRIZE_LOG A (NOLOCK)
    LEFT JOIN CampaignManagement.CRD.CARD_TRANSACTION B (NOLOCK) ON B.TRANSACTION_ID=A.TRAN_ID 
    INNER JOIN CardPayment.PRP.PREPAID_TRANSACTION C (NOLOCK) ON  A.TRAN_ID=C.MAIN_TRN_ID
    INNER JOIN CardPayment.PRP.DAILY_TRANSACTION_POOL T (NOLOCK) ON C.CARD_REF_NUMBER=T.CARD_REF_NUMBER
    
    WHERE A.PRIZE_DATETIME BETWEEN convert(varchar(10), getdate()-1,121) and convert(varchar(10), getdate(),121)
    AND A.CAMPAIGN_ID='11'
    AND A.SCENARIO_ID IN ('18','20')
    AND (C.TRN_DESCRIPTION NOT LIKE 'Diğer Ücret' OR C.TRN_DESCRIPTION IS NULL)
    AND A.STATUS='1'
    AND T.PROCESS_STATUS='C'
    AND T.TRN_DESCRIPTION NOT LIKE '%iptal%'
    
    GROUP BY C.MERCHANT_NAME
    ORDER BY count(C.CARD_REF_NUMBER) DESC

    4 Aralık 2019 Çarşamba 10:21

Yanıtlar

  • with rawdata as 
    (
    SELECT 
    count(C.CARD_REF_NUMBER) as crefcount,
    C.MERCHANT_NAME,
    COUNT(DISTINCT C.CARD_REF_NUMBER) AS 'Tekil Kart Sayısı',
    SUM(C.LOCAL_AMOUNT)  AS 'Harcama Tutarı',
    SUM(A.PRIZE_AMOUNT) AS 'Ödül Tutarı'
    
    FROM CampaignManagement.CRM.SCENARIO_PRIZE_LOG A (NOLOCK)
    LEFT JOIN CampaignManagement.CRD.CARD_TRANSACTION B (NOLOCK) ON B.TRANSACTION_ID=A.TRAN_ID 
    INNER JOIN CardPayment.PRP.PREPAID_TRANSACTION C (NOLOCK) ON  A.TRAN_ID=C.MAIN_TRN_ID
    INNER JOIN CardPayment.PRP.DAILY_TRANSACTION_POOL T (NOLOCK) ON C.CARD_REF_NUMBER=T.CARD_REF_NUMBER
    
    WHERE A.PRIZE_DATETIME BETWEEN convert(varchar(10), getdate()-1,121) and convert(varchar(10), getdate(),121)
    AND A.CAMPAIGN_ID='11'
    AND A.SCENARIO_ID IN ('18','20')
    AND (C.TRN_DESCRIPTION NOT LIKE 'Diğer Ücret' OR C.TRN_DESCRIPTION IS NULL)
    AND A.STATUS='1'
    AND T.PROCESS_STATUS='C'
    AND T.TRN_DESCRIPTION NOT LIKE '%iptal%'
    GROUP BY C.MERCHANT_NAME
    ),
    sirali as
    (
    SELECT
    select case when row_number() over (order by cRefCount desc) < 6 then row_number() over (order by cRefCount desc) else 6 end as groupNo,
    case when row_number() over (order by cRefCount desc) < 6 then MERCHANT_NAME else 'Diger' end as MERCHANT_NAME,
    [Tekil Kart Sayısı],
    [Harcama Tutarı],
    [Ödül Tutarı]
    from rawdata
    )
    Select  groupNo, MERCHANT_NAME,
    Sum([Tekil Kart Sayısı]) as [Tekil Kart Sayısı],
    Sum([Harcama Tutarı]) as [Harcama Tutarı],
    Sum([Ödül Tutarı]) as [Ödül Tutarı]
    from sirali
    group by groupNo, MERCHANT_NAME
    order by groupNo;



    How to create a Minimal, Reproducible Example
    The way to Go.
    World's most advanced open source (object-) relational Database.
    Flutter (for mobile, for web & desktop.

    • Yanıt Olarak İşaretleyen ErsinBicer 4 Aralık 2019 Çarşamba 11:35
    4 Aralık 2019 Çarşamba 11:30

Tüm Yanıtlar

  • with rawdata as 
    (
    SELECT 
    count(C.CARD_REF_NUMBER) as crefcount,
    C.MERCHANT_NAME,
    COUNT(DISTINCT C.CARD_REF_NUMBER) AS 'Tekil Kart Sayısı',
    SUM(C.LOCAL_AMOUNT)  AS 'Harcama Tutarı',
    SUM(A.PRIZE_AMOUNT) AS 'Ödül Tutarı'
    
    FROM CampaignManagement.CRM.SCENARIO_PRIZE_LOG A (NOLOCK)
    LEFT JOIN CampaignManagement.CRD.CARD_TRANSACTION B (NOLOCK) ON B.TRANSACTION_ID=A.TRAN_ID 
    INNER JOIN CardPayment.PRP.PREPAID_TRANSACTION C (NOLOCK) ON  A.TRAN_ID=C.MAIN_TRN_ID
    INNER JOIN CardPayment.PRP.DAILY_TRANSACTION_POOL T (NOLOCK) ON C.CARD_REF_NUMBER=T.CARD_REF_NUMBER
    
    WHERE A.PRIZE_DATETIME BETWEEN convert(varchar(10), getdate()-1,121) and convert(varchar(10), getdate(),121)
    AND A.CAMPAIGN_ID='11'
    AND A.SCENARIO_ID IN ('18','20')
    AND (C.TRN_DESCRIPTION NOT LIKE 'Diğer Ücret' OR C.TRN_DESCRIPTION IS NULL)
    AND A.STATUS='1'
    AND T.PROCESS_STATUS='C'
    AND T.TRN_DESCRIPTION NOT LIKE '%iptal%'
    GROUP BY C.MERCHANT_NAME
    ),
    sirali as
    (
    SELECT
    select case when row_number() over (order by cRefCount desc) < 6 then row_number() over (order by cRefCount desc) else 6 end as groupNo,
    case when row_number() over (order by cRefCount desc) < 6 then MERCHANT_NAME else 'Diger' end as MERCHANT_NAME,
    [Tekil Kart Sayısı],
    [Harcama Tutarı],
    [Ödül Tutarı]
    from rawdata
    )
    Select  groupNo, MERCHANT_NAME,
    Sum([Tekil Kart Sayısı]) as [Tekil Kart Sayısı],
    Sum([Harcama Tutarı]) as [Harcama Tutarı],
    Sum([Ödül Tutarı]) as [Ödül Tutarı]
    from sirali
    group by groupNo, MERCHANT_NAME
    order by groupNo;



    How to create a Minimal, Reproducible Example
    The way to Go.
    World's most advanced open source (object-) relational Database.
    Flutter (for mobile, for web & desktop.

    • Yanıt Olarak İşaretleyen ErsinBicer 4 Aralık 2019 Çarşamba 11:35
    4 Aralık 2019 Çarşamba 11:30
  • Çok teşekkür ederim Çetin bey.

    İyi çalışmalar.

    4 Aralık 2019 Çarşamba 11:36