Solved

SQL or PL/SQL

Posted on 2014-03-23
5
185 Views
Last Modified: 2014-03-24
Hi guys,

Attached you can find an xls file with my problem and necessary explanations on worksheet 1 and the desired result on worksheet 2.
I tried to build an SQL but without success and my knowledge of pl/sql are minimum.
I need your help.
Sorry for my poor english.

Thank you
PL.SQL.xls
0
Comment
Question by:marian68
[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
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39949255
The data type used to store SENT and RECEIVED will influence the SQL used for this.

i.e. are those fields DATE or TIMESTAMP?

Additionally in your Excel sheet you have DIFFERENCE_DAYS showing as a decimal result, is that required or would a result like this be acceptable:
|  PRODUCT |               SENT |          RECEIVED | DIFF_DAYS |
|----------|--------------------|-------------------|-----------|
| product1 |       May, 13 2005 |   August, 11 2005 |        90 |
| product2 | September, 22 2005 |  January, 05 2006 |       105 |
| product3 |       May, 29 2007 |   August, 08 2007 |        71 |
| product4 |    August, 28 2007 | December, 28 2007 |       122 |
| product4 |   January, 14 2008 |      May, 14 2008 |       121 |
| product5 |  February, 15 2008 |     June, 16 2008 |       122 |
| product5 |  November, 05 2008 |    March, 04 2009 |       119 |

Open in new window

That result was produce from this query, where SENT & RECEIVED are timestamps:
SELECT
      t1.product
    , t1.sent
    , min(t2.received) as Received
    , trunc(min(t2.received)) - trunc(t1.sent) as diff_days
FROM (
      SELECT
            product
          , sent
          , lead(sent,1) over (partition BY product ORDER BY sent) next_sent
      FROM table1
     ) t1
INNER JOIN table2 t2 ON t1.product = t2.product
                 AND (
                       (
                             t1.next_sent IS NOT NULL
                         AND t2.received BETWEEN t1.sent AND t1.next_sent
                       )
                     OR
                       (
                             t1.next_sent IS NULL
                         AND t2.received >= t1.sent
                       )
                     )
GROUP BY
      t1.product
    , t1.sent
ORDER BY
      t1.product
    , t1.sent
;

http://sqlfiddle.com/#!4/1b688/18

Open in new window

0
 

Author Comment

by:marian68
ID: 39949299
Thank you for your answer.
The both field, SENT and RECEIVED are date type.
No I don't need decimals.
Thank you
0
 

Author Comment

by:marian68
ID: 39949318
Will you  sent me the SQL where SENT & RECEIVED are date types ?
Thank you,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39949451
That SQL should work for DATE too
0
 

Author Closing Comment

by:marian68
ID: 39950187
It worked like a charm.
Thank you
0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The viewer will learn how to implement Singleton Design Pattern in Java.
This video teaches viewers about errors in exception handling.

735 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