Solved

SQL or PL/SQL

Posted on 2014-03-23
5
182 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
This video teaches viewers about errors in exception handling.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now