Solved

Oracle SQL Merge Rows based on Dates

Posted on 2014-01-13
7
730 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
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!

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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

Technology Partners: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

717 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