Answered by:
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_datethe 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
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