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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not very clear on what you are asking. Perhaps some sample data and the expected results would help.
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_T YPE,PAID_A MT, CLAIMED_AMT,DATE_PAID,ITST G_CREAT_DT ,GROUP_NBR ,SUBS_NBR, SERVICE_Da te
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -
0000123456|01|O|1576.22|0. 01|2007021 4|9/27/200 7 4:01:00 PM|93000|003246000|2007010 3
0000123456|01|A|-1460.95|0 .00|200710 03|10/11/2 007 9:57:00 AM|93000|003246000|2007010 3
0000456789|01|O|1188.44|0. 01|2007022 1|9/27/200 7 4:01:00 PM|93000|016096265|2007011 2
0000456789|01|A|-1188.44|- 0.01|20070 221|9/27/2 007 4:33:00 PM|93000|016096265|2007011 2
0000135791|01|O|1441.05|0. 01|2007022 1|9/27/200 7 4:01:00 PM|93000|899836049|2007010 4
0000135791|01|A|-1441.05|- 0.01|20070 221|9/27/2 007 4:33:00 PM|93000|899836049|2007010 4
-------------------------- ---------- ---------- ---------- ---------- ---------- ----------
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,SERVIC E_Date)
Expected Result:
---------------
0000123456|01|O|115.7|0.01 |20071003| 10/11/2007 9:57:00 AM|93000|003246000|2007010 3
Order by AUDIT_NBR, CLM_TYPE DESC, DETAIL_NBR, DATE_PAID
Respective Columns for the above data
AUDIT_NBR,DETAIL_NBR,CLM_T
--------------------------
0000123456|01|O|1576.22|0.
0000123456|01|A|-1460.95|0
0000456789|01|O|1188.44|0.
0000456789|01|A|-1188.44|-
0000135791|01|O|1441.05|0.
0000135791|01|A|-1441.05|-
--------------------------
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,SERVIC
Expected Result:
---------------
0000123456|01|O|115.7|0.01
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry all the amounts should be summed. It should be summed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks awking00 here is the email. cospring...gmail....com
ASKER
Thanks very much awking00 got the query. I will get back with my results.
ASKER
Thanks awking00