Oracle SQL Merge Rows based on Dates

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
pmcd2010Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
awking00Commented:
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
 
pmcd2010Author Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
awking00Commented:
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
 
pmcd2010Author Commented:
Thanks. Let me try it out and see what happens.
0
 
pmcd2010Author Commented:
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
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.