locked
Need total of amount in next column RRS feed

  • Question

  • User1052662409 posted

    Hi All,

    Below is my query

    SELECT 
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date) = 1
    THEN imp_name ELSE '' END AS Name,

    convert(varchar, [imp_fin_appr_date], 103) AS Date,
    imp_amount_approved_by_finance AS 'Paid Amount'
    FROM [Landmark].[dbo].[tbl_emp_imprest]
    WHERE imp_amount_approved_by_finance >0
    ORDER BY imp_name, imp_fin_appr_date

    the output is like below

    Untitled

    I want the total amount of an employee to be placed in the next column and every employee should have an index number (1,2,3...so on) like below

    Untitled1

    Please suggest

    Sunday, June 28, 2020 5:24 AM

Answers

  • User452040443 posted

    Hi,

    Try:

    with CTE_RN as
    (
        SELECT 
            ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date) as RN_A,
            ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date DESC) as RN_D,
            DENSE_RANK() OVER (ORDER BY imp_name) as DR,
            imp_name,
            imp_fin_appr_date,
    	imp_amount_approved_by_finance,
    	SUM(imp_amount_approved_by_finance) OVER(PARTITION BY imp_name) as TotalAmount
        FROM [Landmark].[dbo].[tbl_emp_imprest]
        WHERE 
            imp_amount_approved_by_finance > 0
    )
    
    SELECT 
        CASE WHEN RN_A = 1 THEN DR END AS DR,
        CASE WHEN RN_A = 1 THEN imp_name ELSE '' END AS Name,
        convert(varchar, [imp_fin_appr_date], 103) AS Date,
        imp_amount_approved_by_finance AS 'Paid Amount',
        CASE WHEN RN_D = 1 THEN TotalAmount end as TotalAmount
    FROM CTE_RN
    ORDER BY 
        imp_name, 
        imp_fin_appr_date

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 28, 2020 1:02 PM

All replies

  • User452040443 posted

    Hi,

    Try:

    with CTE_RN as
    (
        SELECT 
            ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date) as RN_A,
            ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date DESC) as RN_D,
            DENSE_RANK() OVER (ORDER BY imp_name) as DR,
            imp_name,
            imp_fin_appr_date,
    	imp_amount_approved_by_finance,
    	SUM(imp_amount_approved_by_finance) OVER(PARTITION BY imp_name) as TotalAmount
        FROM [Landmark].[dbo].[tbl_emp_imprest]
        WHERE 
            imp_amount_approved_by_finance > 0
    )
    
    SELECT 
        CASE WHEN RN_A = 1 THEN DR END AS DR,
        CASE WHEN RN_A = 1 THEN imp_name ELSE '' END AS Name,
        convert(varchar, [imp_fin_appr_date], 103) AS Date,
        imp_amount_approved_by_finance AS 'Paid Amount',
        CASE WHEN RN_D = 1 THEN TotalAmount end as TotalAmount
    FROM CTE_RN
    ORDER BY 
        imp_name, 
        imp_fin_appr_date

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 28, 2020 1:02 PM
  • User1052662409 posted

    with CTE_RN as
    (
        SELECT 
            ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date) as RN_A,
            ROW_NUMBER() OVER(PARTITION BY imp_name ORDER BY imp_fin_appr_date DESC) as RN_D,
            DENSE_RANK() OVER (ORDER BY imp_name) as DR,
            imp_name,
            imp_fin_appr_date,
    	imp_amount_approved_by_finance,
    	SUM(imp_amount_approved_by_finance) OVER(PARTITION BY imp_name) as TotalAmount
        FROM [Landmark].[dbo].[tbl_emp_imprest]
        WHERE 
            imp_amount_approved_by_finance > 0
    )
    
    SELECT 
        CASE WHEN RN_A = 1 THEN DR END AS DR,
        CASE WHEN RN_A = 1 THEN imp_name ELSE '' END AS Name,
        convert(varchar, [imp_fin_appr_date], 103) AS Date,
        imp_amount_approved_by_finance AS 'Paid Amount',
        CASE WHEN RN_D = 1 THEN TotalAmount end as TotalAmount
    FROM CTE_RN
    ORDER BY 
        imp_name, 
        imp_fin_appr_date

    Perfect !!

    Thanks a lot

    Monday, June 29, 2020 4:14 AM