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
Solved

Oracle SQL Merge Rows based on Dates

Posted on 2014-01-13
7
716 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

856 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