Oracle SQL Query Help for scenario

Question

I have following query that gives data as follows

SELECT NO, PRODUCT_CODE, DATE, QTY
FROM DAILY_QAUNTITY
WHERE CODE = '10' ;



NO      CODE      DATE        QTY 

6750    105581   5-Feb-14    57 

6750    105581  12-Feb-14    46

6750    105581  17-Feb-14    41 
6751    11028   18-feb-14    40
6751    11028   19-feb-14    38
6751    11028   20-feb-14    35
6751    11028   21-feb-14    30

BUT I NEED OUTPUT AS

NO       CODE    DATE      QTY

6750    105581  5-Feb-14    57 

6750    105581  6-Feb-14    57 

6750    105581  7-Feb-14    57 

6750    105581  8-Feb-14    57 

6750    105581  9-Feb-14    57 

6750    105581  10-Feb-14   57 

6750    105581  11-Feb-14   57 

6750    105581  12-Feb-14   46 

6750    105581  13-Feb-14   46 

6750    105581  14-Feb-14   46 

6750    105581  15-Feb-14   46 

6750    105581  16-Feb-14   46 

6750    105581  17-Feb-14    41 
6751    11028   18-feb-14    40
6751    11028   19-feb-14    38
6751    11028   20-feb-14    35
6751    11028   21-feb-14    30

WE CAN SEE , IN 1st output after 5th feb it jumped to 12th feb. you can observe in 2nd output the dates are sequence from 5th to 11th with same data. How to write sql query to bring this output.

initially you tagged SqlSever also. Why you removed now that? (: — RNA Team, Oct 27 '15 at 10:19

score 2 · Answer 1 · edited May 23 '17 at 12:15

@Venkat, since your problem lies majorly in date generation, so the Query has focused mainly on that. Here is the SQL Server version for the same. It uses recursive CTE.

;WITH Dates as
 (
    SELECT
        [Date] = CAST('2014-02-05' AS DATE) 
    UNION ALL 

    SELECT      
        DATEADD(DAY , 1, [Date]) 
    FROM Dates
    WHERE DATEADD (DAY, 1, [Date]) <= '2014-02-17'
 )

SELECT 
    [NO] = 6750 
    ,[CODE] = 105581    
    ,[Date] = REPLACE(CONVERT(NVARCHAR, [Date], 106),' ','-')
    ,[QTY] = CASE WHEN [Date] >= '2014-02-05' AND [Date] <='2014-02-11' THEN 57
                  WHEN [Date] >= '2014-02-12' AND [Date] <='2014-02-16' THEN 46
                  WHEN [Date] >= '2014-02-17' THEN 41
            END
 FROM Dates
 OPTION (MAXRECURSION 0);

You can perform the Date Calendar generation in other ways also.

Here are some nice examples

a) How to create a Calender table for 100 years in Sql

b) How to generate a range of dates in SQL Server

In Oracle, you can follow the below approach

WITH DatesCTE AS
(
        SELECT
                (to_date('05-02-2014','DD-MM-YYYY') - level + 1) AS day
        FROM dual
        CONNECT BY LEVEL <= (to_date('05-02-2014','DD-MM-YYYY') - to_date('17-02-2014','DD-MM-YYYY') + 1)
)

SELECT
    [NO] = 6750 
    ,[CODE] = 105581
    ,[Date]  = day
FROM DatesCTE

Please refer this SO discussion for more ways to generate date ranges in Oracle.

Update (Date generation without WITH Clause)

SELECT
    (to_date('05-02-2014','DD-MM-YYYY') - level + 1) AS day
FROM dual
CONNECT BY LEVEL <= (to_date('05-02-2014','DD-MM-YYYY') - to_date('17-02-2014','DD-MM-YYYY') + 1)

Hope this helps.

Thanks for the reply. We should not hardcode the values here. While selecting data from DAILY_QAUNTITY table we have to compare if the date difference is there or not, If there the dates has to displayed as shown in second query result. Please help me with this in oracle SQ. — Ven Raj, Oct 27 '15 at 10:48
It's just for the demonstration purpose. However, the dates can be passed at runtime to the query. And use the '-' arithmetic operator to get the date difference. like select DATE1-DATE2 from table. If your date is in string format you can use the to_date function to convert string to date first like to_date('2014-02-01', 'YYYY-MM-DD') and then perform the difference. If the difference is more than 0, then generate the dates using the techniques shown above. All the best !!! — RNA Team, Oct 27 '15 at 11:15

score 0 · Answer 2 · edited Oct 28 '15 at 10:24

0

Try to use ORDER BY

SELECT NO, PRODUCT_CODE, DATE, QTY
FROM DAILY_QAUNTITY
WHERE CODE = '10' 
ORDER BY DATE;

edited Oct 28 '15 at 10:24

Pang

9,564
146
81
122

answered Oct 27 '15 at 15:30

Sabe

176
1
7

score 0 · Answer 3 · edited Nov 14 '15 at 11:44

SELECT    
      (
        SELECT "no" 
        FROM 
            (SELECT 
                "no"
                , code
                , "date" currdate
                , qty
                , lead("date") over (order by "date") leaddate 
              FROM daily_quantity) dq 
       WHERE date1 BETWEEN dq.currdate AND NVL(dq.leaddate-1,dq.currdate)
      ) "no",  
      (SELECT code 
      FROM 
        (SELECT 
                "no"
                , code
                , "date" currdate
                , qty
                , lead("date") over (order by "date") leaddate  
         FROM daily_quantity) dq
       WHERE date1 BETWEEN dq.currdate AND NVL(dq.leaddate-1,dq.currdate)
      ) code,     
      date1,    
      (SELECT qty 
      FROM 
        (SELECT "no"
                , code
                , "date" currdate
                , qty
                , lead("date") over (order by "date") leaddate 
          FROM daily_quantity) dq
       WHERE date1 BETWEEN dq.currdate AND NVL(dq.leaddate-1,dq.currdate)
      ) qty   
    FROM   
      ( 
            SELECT 
            (
                (SELECT MIN("date") 
                FROM daily_quantity) + level -1
            ) date1
            FROM dual   
            CONNECT BY level <= 
            (SELECT MAX("date") FROM daily_quantity) - (SELECT MIN("date") 
            FROM daily_quantity) + 1
      );

I tried adding filter to this query like getting last 3 months data but it is taking long time. Do I have to keep filter to every select statement in the above query? Where can I add extra filters like getting last 3 months and code=45654 etc — Ven Raj, Nov 16 '15 at 09:43
Hi Venkat, Sorry for late reply. Due to some personal issues could not come online. Sending you the optimized query again in another answer to handle the case when your table has billions of row. Please feel free to revert if you case any performance issue — Gaurav Rana, Dec 02 '15 at 06:12

score 0 · Answer 4 · edited Dec 02 '15 at 06:39

SELECT  inn2.date1,    
        LAST_VALUE(inn2.numbers IGNORE NULLS) OVER (ORDER BY date1 ) "no",    
        LAST_VALUE(inn2.code IGNORE NULLS) OVER (ORDER BY date1 ) code,    
        LAST_VALUE(inn2.qty IGNORE NULLS) OVER (ORDER BY date1 ) qty    
FROM    
   (    SELECT inn1.date1,    
            MAX(DQ."no") numbers,    
            MAX(DQ.code) code,    
            MAX(DQ.qty) qty    
        FROM (  SELECT ( (SELECT MIN("date") FROM daily_quantity) + level -1 ) date1     
                FROM dual    
                CONNECT BY level <= (SELECT MAX("date") FROM daily_quantity) - (SELECT MIN("date") FROM daily_quantity) + 1    
    ) inn1, daily_quantity DQ    
WHERE inn1.date1 = DQ."date"(+)    
GROUP BY DQ."no",
        DQ.code,
        DQ.qty,
        inn1.date1
) inn2 ;

score -2 · Answer 5 · edited Oct 28 '15 at 08:46

-2

Hope this may work for you.... Although same can be optimized further

with dq as
( 
select "no", code, "date" currdate, qty, lead("date") over (order by "date") leaddate 
from daily_quantity
)
select  
(select "no" from dq where date1 between dq.currdate and nvl(dq.leaddate-1,dq.currdate)) "no",
(select code from dq where date1 between dq.currdate and nvl(dq.leaddate-1,dq.currdate)) code,
 date1,
(select qty from dq where date1 between dq.currdate and nvl(dq.leaddate-1,dq.currdate)) qty
from 
(select ((select min("date") from daily_quantity) + level -1) date1
from dual
connect by level <= (select max("date") from daily_quantity) - (select min("date") from daily_quantity) + 1);

edited Oct 28 '15 at 08:46

RNA Team

269
1
6

answered Oct 28 '15 at 07:17

Gaurav Rana

54
3

Thx much. Instead of using with caluse.. Is there any way we can do within single sql query. I want to use this in informatica. – Ven Raj Nov 11 '15 at 12:34
Hi Venkat, As you know WITH clause is used to simplify the query. Where we are using redundant query we can use WITH. If you really need to remove WITH clause, you can replace dq with the query with in with clause. I am attaching the modified code in another answer. Please reply if you want me to further simplify the query. – Gaurav Rana Nov 13 '15 at 06:24
Thank Gaurav for ur kind reply. Basically I need a single select query instead of WITH clause because I have almost 20 million records and even am using subquery to get some columns within "with" clause which is taking forever time. Kindly give a thought. – Ven Raj Nov 13 '15 at 16:50
@Venkat Rajam, check my update (without the WITH clause) – RNA Team Nov 14 '15 at 10:56
Hi Gaurav, I tried adding filter to this query like getting last 3 months data but it is taking long time. Do I have to keep filter to every select statement in the above query? Where can I add extra filters like getting last 3 months and code=45654 etc. There are about 10 million records in daily_quantity table. Can you kindly help me with this ASAP – Ven Raj Nov 17 '15 at 06:54
I have modified the question. If there is difference in dates, lead function is taking lead date and there by it is giving data correctly. If there is no difference, if you observe the question, it should display the same data. basically there are distributors who are submitting inventory details correctly on daily basis like 6751. There are some distributors who are not submitting inventory details like 6750. we need to fill the gaps between the dates when they are not providing. Kindly help me with this logic and I have to get data from table for last 3 months. – Ven Raj Nov 17 '15 at 08:34
Is there any way without using "levels" we can do this. Am getting issue with level here because it is taking max date and min date and retrieving only 90 records even though lot of transactions are there. – Ven Raj Nov 17 '15 at 13:09

Oracle SQL Query Help for scenario

5 Answers5