Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL or PL/SQL

Posted on 2014-03-23
5
Medium Priority
?
190 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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

916 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