?
Solved

Oracle SQL Merge Rows based on Dates

Posted on 2014-01-13
7
Medium Priority
?
743 Views
Last Modified: 2014-01-13
I have a unique problem that I have no idea how to solve.  I have a set of data that is used for tracking customer charges.  When adjustments are made, the dates for a billing period on the adjustment do not match the dates for the billing period due to a constraint in the billing system.  What I am looking to do is combine all of the charges for a billing period where the billing period start date and billing period end dates are within a day of each other, + or - 1 day on either side of the actual billing period.

In the attached file, which is an example of the source billing data and the desired query result, I know what the actual billing period is supposed to be based on the Line Item "LI9" having a value populated in the "Usage" column.  The desired output on the second tab shows all of these charges on 1 single line, only showing the correct billing period.

If this isn't clear, please let me know.  Thank you so much in advance.
Example-Source-Data.xlsx
0
Comment
Question by:pmcd2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39776770
I think I see how your desired results are derived. However, I'm not sure how to tie the bill amounts for Line item LI6 of -663.59 and -134.09 with the others. Is it because the billing period is within one day of the Bill End period in one case and within one day of the Bill Start period in the other case where LI9 has a value? I also assume this data is for a single customer, but how can that be verified? Can there be a bill start and end period of 9/19/2013 and 10/22/2013 for another customer for, say, invoice # 4?
0
 

Author Comment

by:pmcd2010
ID: 39776787
Yes, since the billing period start or end date is within 1 day of the billing period start or end of the correct period, those charges are tied.  And yes, there is further distinction of which customer this belongs to but I removed that information because if I can do it for one customer/billing period, I should be able to do it for all, keying off the identification number for each customer.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39776830
Since the information will need to be "grouped by" a customer id, I'll just add that attribute to your sample data to use as a test case and see what I can come up with.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39777182
try this...

    SELECT u.customer,
         MIN(d.billstart) billstart,
         MAX(d.billend) billend,
         u.usage,
         SUM(CASE WHEN d.lineitem = 'LI5' THEN NVL(NULLIF(d.othercharge, 0), d.tax2) END) li5,
         SUM(CASE WHEN d.lineitem = 'LI3' THEN NVL(NULLIF(d.othercharge, 0), d.tax2) END) li3,
         SUM(CASE WHEN d.lineitem = 'LI4' THEN NVL(NULLIF(d.othercharge, 0), d.tax2) END) li4,
         SUM(CASE WHEN d.lineitem = 'LI8' THEN NVL(NULLIF(d.othercharge, 0), d.tax2) END) li8,
         SUM(CASE WHEN d.lineitem = 'LI7' THEN NVL(NULLIF(d.othercharge, 0), d.tax2) END) li7,
         SUM(CASE WHEN d.lineitem = 'LI6' THEN NVL(NULLIF(d.othercharge, 0), d.tax2) END) li6,
         SUM(d.tax) tax,
         SUM(d.totalbill)
    FROM yourdata d,
         (SELECT customer,
                 billstart,
                 billend,
                 usage
            FROM yourdata
           WHERE usage != 0) u
   WHERE d.customer = u.customer
     AND (d.billstart BETWEEN u.billstart - 1 AND u.billend + 1
       OR d.billend BETWEEN u.billstart - 1 AND u.billend + 1)
     AND d.usage = 0
GROUP BY u.customer,
         u.usage,
         u.billstart,
         u.billend

Open in new window

0
 

Author Comment

by:pmcd2010
ID: 39777199
Thanks. Let me try it out and see what happens.
0
 

Author Comment

by:pmcd2010
ID: 39777309
And now I begin kicking myself for not thinking of this sooner. Thank you so much for your time on this.  I was having a major brain block after looking at the same code for a month now.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39777469
As usual sdstuber easily beat me to the punch (should not have gone to lunch). The only difference I came up with compared to his was adding the condition "and lineitem = LI9" to the "where usage != 0" clause. I was also concerned that there could be another customer with a lineitem of LI2 (e.g.) that had Total Bill values.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question