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.

     

    SORT SAMPLE

    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).

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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