Solved

SQL or PL/SQL

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

710 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