• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

SQL or PL/SQL

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
marian68
Asked:
marian68
  • 3
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
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
 
marian68Author Commented:
Thank you for your answer.
The both field, SENT and RECEIVED are date type.
No I don't need decimals.
Thank you
0
 
marian68Author Commented:
Will you  sent me the SQL where SENT & RECEIVED are date types ?
Thank you,
0
 
PortletPaulfreelancerCommented:
That SQL should work for DATE too
0
 
marian68Author Commented:
It worked like a charm.
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now