• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4539
  • Last Modified:

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.
0
cutie_smily
Asked:
cutie_smily
  • 5
  • 3
3 Solutions
 
awking00Commented:
I'm not very clear on what you are asking. Perhaps some sample data and the expected results would help.
0
 
cutie_smilyAuthor Commented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
Is the 0.01 claimed_amt value in the expected results due to the fact that is in the record with the earlier date_paid or is it the sum of the claimed_amt for the given audit_nbr, detail_nbr group?
0
 
cutie_smilyAuthor Commented:
Sorry all the amounts should be summed. It should be summed.
0
 
awking00Commented:
I have had lots of trouble trying to post to EE ever since the site was updated. I have a solution for your problem that I've tried to post numerous times without success. I don't know what the ethics are but, if the monitors will allow it, I can try to post my email address and get you an answer in that manner.
0
 
cutie_smilyAuthor Commented:
Thanks awking00 here is the email. cospring...gmail....com
0
 
cutie_smilyAuthor Commented:
Thanks very much awking00 got  the query. I will get back with my results.
0
 
cutie_smilyAuthor Commented:
Thanks awking00
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now