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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply