Need total of amount in next column

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

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

Please suggest

Answer 1

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

Answer 2

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