CODEWITHSUNDEEP
sqlsql-server
Saravanan
Saravanan

Reputation: 57

Monthly transactions in a row

In SQL server, I want to achieve sum of transactions spent on a monthly basis for the past 6 months. Below is the query that I have written

with filter_6month_transactions as (
    SELECT 
        emplid, 
        format(transactiondate, 'yyyyMM') transactionmonth,
        expenseamountreimbursementcurrency,
        reimbursementcurrency,
        CAST(DATEADD(m, -6, GetDate()) AS date) report_start_date,
        DENSE_RANK() OVER(ORDER BY format(transactiondate, 'yyyyMM')) Rank,
        er.exchangerate
    FROM Concur_Expenses_CC_HighLimit_UDA e
     LEFT join exchange_rates er
     ON e.ReimbursementCurrency = er.FromCurrency
    WHERE paymenttype LIKE '%CBCP%' 
    AND concurrecordtype IN ('Approved', 'Unassigned', 'Not approved')
    AND cast(transactiondate as date) >= CAST(DATEADD(m, -6, GetDate()) AS date) 
    AND emplid IS NOT NULL
    AND employeeemailaddress NOT LIKE 'inact_%'
),
convert_transactions_toUSD AS (
    SELECT
        emplid, 
        transactionmonth,
        (CASE 
            WHEN reimbursementcurrency = 'USD' THEN 1 * expenseamountreimbursementcurrency
            ELSE fmt.exchangerate * expenseamountreimbursementcurrency
        END) transaction_amount_usd,
        rank
        
        
    FROM filter_6month_transactions fmt
),
calc_total_spend_monthly AS (
    SELECT 
        emplid, 
        transactionmonth,
        (CASE 
            WHEN rank = 1 THEN SUM(transaction_amount_usd) OVER (PARTITION BY emplid, transactionmonth )
        END) AS first_month,
        (CASE 
            WHEN rank = 2 THEN SUM(transaction_amount_usd) OVER (PARTITION BY emplid, transactionmonth )
        END) AS second_month,
        (CASE 
            WHEN rank = 3 THEN SUM(transaction_amount_usd) OVER (PARTITION BY emplid, transactionmonth )
        END) AS third_month,
        (CASE 
            WHEN rank = 4 THEN SUM(transaction_amount_usd) OVER (PARTITION BY emplid, transactionmonth )
        END) AS fourth_month,
        (CASE 
            WHEN rank = 5 THEN SUM(transaction_amount_usd) OVER (PARTITION BY emplid, transactionmonth )
        END) AS fifth_month,
        (CASE 
            WHEN rank = 6 THEN SUM(transaction_amount_usd) OVER (PARTITION BY emplid, rank )
        END) AS sixth_month
        
    FROM convert_transactions_toUSD
)

SELECT distinct * FROM calc_total_spend_monthly

The output received is that with duplications of rows

emplid transactionmonth first_month second_month third_month fourth_month fifth_month
233737 202204 NULL 190.183636 NULL NULL NULL
233737 202204 NULL 190.183636 NULL NULL NULL
233737 202205 NULL NULL 29.7352 NULL NULL
233737 202205 NULL NULL 29.7352 NULL NULL
233737 202206 NULL NULL NULL 51.05669000 NULL
233737 202206 NULL NULL NULL 51.05669000 NULL
233737 202206 NULL NULL NULL 51.05669000 NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202203 47898.78 NULL NULL NULL NULL
056908 202204 NULL 12.16440000 NULL NULL NULL
056908 202205 NULL NULL -81.562302 NULL NULL
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200
056908 202207 NULL NULL NULL NULL 587.12828200

But the expected output is,

emplid first_month second_month third_month fourth_month fifth_month
233737 NULL 190.183636 29.7352 51.05669000 NULL
056908 47898.78 12.16440000 -81.562302 NULL 587.12828200

basically pivot of all month for an employee should show in a row. What changes in the query, will get the desired output. Please correct me.

Upvotes: 0

Views: 75

Answers (2)

Ananth MK
Ananth MK

Reputation: 392

Try this CTE code for calc_total_spend_monthly with GROUP BY emplid

SELECT 
    emplid,
    SUM(CASE WHEN rank = 1 THEN transaction_amount_usd ELSE 0 END) AS first_month,
    SUM(CASE WHEN rank = 2 THEN transaction_amount_usd ELSE 0 END) AS second_month,
    SUM(CASE WHEN rank = 3 THEN transaction_amount_usd ELSE 0 END) AS third_month,
    SUM(CASE WHEN rank = 4 THEN transaction_amount_usd ELSE 0 END) AS fourth_month,
    SUM(CASE WHEN rank = 5 THEN transaction_amount_usd ELSE 0 END) AS fifth_month,
    SUM(CASE WHEN rank = 6 THEN transaction_amount_usd ELSE 0 END) AS sixth_month        
FROM convert_transactions_toUSD
GROUP BY
    emplid

Upvotes: 1

Squirrel
Squirrel

Reputation: 24792

As Dale has mention in comment, you need to use GROUP BY with SUM()

Change your final query to

SELECT emplid, 
       first_month  = sum(first_month),
       second_month = sum(second_month),
       third_month  = sum(third_month),
       fourth_month = sum(fourth_month),
       fifth_month  = sum(fifth_month),
       sixth_month  = sum(sixth_month)
FROM   calc_total_spend_monthly
group by emplid

Actually, your entire query may be simplified to (after removing unnecessary columns and cte)

with filter_6month_transactions as (
    SELECT 
        emplid, 
        DENSE_RANK() OVER(ORDER BY format(transactiondate, 'yyyyMM')) as [Rank],
        CASE WHEN reimbursementcurrency = 'USD' 
             THEN 1 * expenseamountreimbursementcurrency
             ELSE er.exchangerate * expenseamountreimbursementcurrency
             END as transaction_amount_usd
    FROM Concur_Expenses_CC_HighLimit_UDA e
     LEFT join exchange_rates er
     ON e.ReimbursementCurrency = er.FromCurrency
    WHERE paymenttype LIKE '%CBCP%' 
    AND concurrecordtype IN ('Approved', 'Unassigned', 'Not approved')
    AND cast(transactiondate as date) >= CAST(DATEADD(m, -6, GetDate()) AS date) 
    AND emplid IS NOT NULL
    AND employeeemailaddress NOT LIKE 'inact_%'
)
SELECT emplid, 
       first_month  = sum(case when [Rank] = 1 then transaction_amount_usd end),
       second_month = sum(case when [Rank] = 2 then transaction_amount_usd end),
       third_month  = sum(case when [Rank] = 3 then transaction_amount_usd end),
       fourth_month = sum(case when [Rank] = 4 then transaction_amount_usd end),
       fifth_month  = sum(case when [Rank] = 5 then transaction_amount_usd end),
       sixth_month  = sum(case when [Rank] = 6 then transaction_amount_usd end)
FROM   filter_6month_transactions
group by emplid

OR using the PIVOT operator

SELECT emplid, 
       first_month  = [1],
       second_month = [2],
       third_month  = [3],
       fourth_month = [4],
       fifth_month  = [5],
       sixth_month  = [6]
FROM   filter_6month_transactions
       PIVOT
       (
           sum(transaction_amount_usd)
           for [Rank] in ([1], [2], [3], [4], [5], [6])
       ) p

Upvotes: 1

Related Questions