Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle SQL Merge Rows based on Dates

Posted on 2014-01-13
7
Medium Priority
?
770 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

636 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