SQL Server query results sort order with totals
-
I am trying to sort by the ID with the highest total. I can sort by ID and then get the total for each member but I cannot seem to get it to sort by id and total.
Below is what I have and what I want. On what I have I am doing
Any suggestions on what I should do or if it is possible? Thanks.
WITH F3 AS
(
SELECT F2.ID
,F2.DIAGN1
,F2.PROVIDER_NAME
,F2.FROM_DATE
,F2.DATE_TO
,F2.UNITS
,F2.TOTAL_PAID
,F2.PER_DIEM
,F2.DW_PAID_DATE
FROM F2
UNION ALL
SELECT ID
,NULL DIAGN1
,NULL PROVIDER_NAME
,NULL FROM_DATE
,NULL DATE_TO
,SUM(UNITS) AS UNITS
,SUM(TOTAL_PAID) AS TOTAL_PAID
,NULL PER_DIEM
,NULL DW_PAID_DATE
FROM F2
GROUP BY ID
)
,F4 AS
(
SELECT F3.ID
,CASE WHEN F3.DIAGN1 IS NULL THEN 'TOTAL' ELSE F3.DIAGN1 END AS DIAGN1
,F3.PROVIDER_NAME
,F3.FROM_DATE
,F3.DATE_TO
,F3.UNITS
,FORMAT(F3.TOTAL_PAID,'C','EN-US') AS TOTAL_PAID
,FORMAT(F3.PER_DIEM,'C','EN-US') AS PER_DIEM
,F3.DW_PAID_DATE
FROM F3
)
SELECT F4.ID
,F4.DIAGN1
,F4.PROVIDER_NAME
,F4.FROM_DATE
,F4.DATE_TO
,F4.UNITS
,F4.TOTAL_PAID
,F4.PER_DIEM
,F4.DW_PAID_DATE
FROM F4
ORDER BY 1, CASE WHEN DIAGN1 = 'TOTAL' THEN 'Z' END,3,4 -
It is likely that you will get more responses if you post your sample data in consumable format (ie, with DDL and INSERT statements).
-
This really should be done in the presentation layer.
With that said - not having any consumable data to test/validate, what I would do is include a column with the total for each group using SUM(TOTAL_PAID) OVER(PARTITION BY ID) and use that column as the first column in the ORDER BY. The second column in the ORDER BY will be the ID, then PROVIDER and FROM_DATE.
I would also get rid of the ordering by column position and use specific column names and eliminate one of the CTE's. No reason to have a CTE if the only thing you are doing is formatting the data for output.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply