Link to home
Start Free TrialLog in
Avatar of cutie_smily
cutie_smily

asked on

SQL to get first and last records from the recordset

I need some quick help in writing the query. This should be strictly done in SQL and cannot be done in PL/SQL or cannot create sequence number (Only select access).

The query should be order by
- AUDIT_NBR
- CLAIM_TYPE descending
- DETAIL_NBR
- DATE_PAID

After the data is sorted/ordered base on the above, summarize the financial fields:
GROUP BY:
- AUDIT_NBR
- DETAIL_NBR
- SUM(paid_AMT)
- SUM(CLM_AMT)

For the below records, use the values in the LAST record within the unique key for the following fields:
- DATE_PAID
- ITSTG_CREAT_DT      

For the remaining non-Financial fields, use the values in the FIRST record within the unique key.  These fields include:
- GROUP_NUMBER
- SUBS_NBR
- SERVICE_DATE
- FORMAT
- TYPE_OF_BILL
- PATIENT_STATUS
- PRIMARY_CODE
--SECOND_CODE

Below is the initial query to get the exact totals:
SELECT
   AUDIT_NBR,
   DETAIL_NBR,
   SUM(PAID_AMT) SUM_PAID,
   SUM(CLM_AMT) SUM_CLM
FROM CL_WRK
GROUP BY AUDIT_NBR, DETAIL_NBR)

The final select statement should include all the above mentioned columns.
Please let me know if anything is not clear or need additional information.

I am giving the maximum points.
SOLUTION
Avatar of sventhan
sventhan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of awking00
I'm not very clear on what you are asking. Perhaps some sample data and the expected results would help.
Avatar of cutie_smily
cutie_smily

ASKER

Below is test data in pipe delimited format:
Order by AUDIT_NBR, CLM_TYPE DESC, DETAIL_NBR, DATE_PAID

Respective Columns for the above data
AUDIT_NBR,DETAIL_NBR,CLM_TYPE,PAID_AMT, CLAIMED_AMT,DATE_PAID,ITSTG_CREAT_DT,GROUP_NBR,SUBS_NBR,SERVICE_Date
---------------------------------------------------------------------------------------
0000123456|01|O|1576.22|0.01|20070214|9/27/2007  4:01:00 PM|93000|003246000|20070103
0000123456|01|A|-1460.95|0.00|20071003|10/11/2007 9:57:00 AM|93000|003246000|20070103
0000456789|01|O|1188.44|0.01|20070221|9/27/2007  4:01:00 PM|93000|016096265|20070112
0000456789|01|A|-1188.44|-0.01|20070221|9/27/2007  4:33:00 PM|93000|016096265|20070112
0000135791|01|O|1441.05|0.01|20070221|9/27/2007  4:01:00 PM|93000|899836049|20070104
0000135791|01|A|-1441.05|-0.01|20070221|9/27/2007  4:33:00 PM|93000|899836049|20070104
--------------------------------------------------------------------------------------
Below is what i want from the first record:
------------------------------------------
group by AUDIT_NBR,DETAIL_NBR (0000123456|01)
Sum (PAID_AMT) (1576.22, -1460.95)
take the last record date (10/11/2007)
take first record values (GROUP_NBR,SUBS_NBR,SERVICE_Date)

Expected Result:
---------------
0000123456|01|O|115.7|0.01|20071003|10/11/2007 9:57:00 AM|93000|003246000|20070103
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry all the amounts should be summed. It should be summed.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks awking00 here is the email. cospring...gmail....com
Thanks very much awking00 got  the query. I will get back with my results.
Thanks awking00