Solved

Find missed day & store

Posted on 2014-11-10
43
152 Views
Last Modified: 2014-12-04
i previously asked this question
http://www.experts-exchange.com/Database/Oracle/Q_28229246.html

it was works well to get the missed days for data of one store
now if i want to add store number to the result , like this


declare
cursor c_stores is 
select a.str_no
 from cr_store a
where a.opdate > '28-dec-13';

BEGIN
FOR i in c_stores
   LOOP
  
SELECT i,tc.DATE_TIME_START
FROM calendar_table tc
where tc.date_time_start between '01-jan-14' and sysdate
and tc.date_time_start
not IN
(select ds.dob from 
cr_gnditem ds
where ds.unit = i 
and ds.dob between '01-jan-14' and sysdate
group by ds.dob)
order by tc.DATE_TIME_START;
end loop;
end;

Open in new window


but it sent me error message that data wrong of type
0
Comment
Question by:NiceMan331
  • 19
  • 15
  • 4
  • +1
43 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I see from the previous question that you changed the proposed NOT EXISTS() into NOT IN()
I would prefer to use NOT EXISTS, but here I would simply use a left outer join then locate those records that have no match. Also: I don't see why you are using a cursor now.

Requested:
All dates from '01-jan-14' until and including sysdate without a matching birthday in cr_gnditem

SELECT
      TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT
                  dob
            FROM cr_gnditem
      ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN '01-jan-14' AND sysdate)
AND CG.dob IS NULL
;

Open in new window


There MIGHT be an advantage in using DISTINCT for the subquery:
SELECT
      TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT DISTINCT
                  dob
            FROM cr_gnditem
      ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN '01-jan-14' AND sysdate)
AND CG.dob IS NULL
;

Open in new window

0
 

Author Comment

by:NiceMan331
Comment Utility
ok , but i still not get the  "i" in the query , which refere to store number
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Your cursor for store number is the equivalent of a cross join as far as I can see
SELECT
        S.str_no
      , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem
                  ) CG ON TC.DATE_TIME_START = CG.dob
      CROSS JOIN (
                  SELECT
                        str_no
                  FROM cr_store
                  WHERE opdate > to_date('28-dec-13','DD-MMM-YY')
                  ) S
WHERE (TC.date_time_start BETWEEN to_date('01-jan-14','DD-MMM-YY') AND sysdate)
AND CG.dob IS NULL
;

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
The variable i that you are referencing is a record, not a single value.  You would need to reference it in your query as i.str_no.
0
 

Author Comment

by:NiceMan331
Comment Utility
error at select , after loop
 an INTO clause is expected in this SELECT statement

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
NiceMan331 that is coming from elsewhere, not the query itself. I have no idea how you are using it.

I am not online at this time typically so I won't be able to debug the extra code.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
The message is coming from your original code.  You have a select statement in a PL/SQL block.  You need to select that into something.  Variables should be declared to hold the values you are selecting.
0
 

Author Comment

by:NiceMan331
Comment Utility
johnson , may you please correct my sql
actually i want to print out the result of the missed days by stores , how i select it into something ?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
You do not need PL/SQL for this, just run the SQL query I provided
SELECT
        S.str_no
      , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem
                  ) CG ON TC.DATE_TIME_START = CG.dob
      CROSS JOIN (
                  SELECT
                        str_no
                  FROM cr_store
                  WHERE opdate > to_date('28-dec-13','DD-MMM-YY')
                  ) S
WHERE (TC.date_time_start BETWEEN to_date('01-jan-14','DD-MMM-YY') AND sysdate)
AND CG.dob IS NULL
;

Open in new window

0
 

Author Comment

by:NiceMan331
Comment Utility
error message
date format not recognized
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
change the the date literals you use to a much safer and more sensible YYYY-MM-DD

e.g.
28-dec-13 becomes
2013-12-28

WHERE opdate > to_date('2013-12-28','YYYY-MM-DD')

& treat the other date literal the same way
0
 

Author Comment

by:NiceMan331
Comment Utility
yes , it works
but it gives no rows selected , which means something adjustment needed
because actually there is some data missing
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
without the ability to work against your tables it is not feasible to suggest why there are no rows.

You should have by now the experience to troubleshoot this. basically you start "piece by piece" until a problem is identified then figure out why that is a problem

try this to start with, do those tables join at all? (note the inner join!)

SELECT
        TC.DATE_TIME_START
FROM calendar_table TC
      INNER JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem
                  ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN to_date('01-jan-14','DD-MMM-YY') AND sysdate) /*FIX THIS DATE ISSUE */

Open in new window

Please do not rely on me being online all night (it is evening for me)
0
 

Author Comment

by:NiceMan331
Comment Utility
yes it gives a lot of rows as result  , but we have to consider that might be a couple of transactions per store per day
what we have to do is : to summarize the transactions per store / per day first , then to get the missing days by comparing it with calendar table
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Very good now make that a left join and add this to the where clause:

AND CG.dob IS NULL

in other words, does it find the missing dates?
0
 

Author Comment

by:NiceMan331
Comment Utility
make that a left join

sorry , i don't know how to do it
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
yes you do....

simply change INNER to LEFT

this you definitely should know how to do, you should learn by doing, getting answers fed to you does not make you learn
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , good , it gives correct result for one store number (unit)
now i added to get unit number ( equal store_no )
but it gives error that s.unit is invalid identifier
SELECT
 S.unit,
        TC.DATE_TIME_START
FROM calendar_table TC
      left JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem s where s.unit in
                  (SELECT
                        str_no
                  FROM cr_store 
                  WHERE opdate > to_date('2013-12-28','YYYY-MM-DD'))
                  ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN to_date('2014-03-06','YYYY-MM-DD') AND sysdate-1) /*FIX THIS DATE ISSUE */
AND CG.dob IS NULL
order by cg.dob;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Well the reason for the error should be obvious to you

If you are asked to go to the shops for bread and milk but come home with bread only
Then: When you are asked to pass the milk

Your error message is: "ooops I forgot the milk"

It is the same here, you have not put s.unit into the shopping bag so it cannot be provided

SELECT  S.unit,         TC.DATE_TIME_STARTFROM calendar_table TC       left JOIN (
SELECT           dob --<< here is the shopping bag
...
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
But there is a deeper problem. You are seeking dates where there is NO MATCH to dob. So if there is NO MATCH to dob there is also NO MATCH to s.unit

Look at the last line of the where clause again "AND CG.dob IS NULL" hence:
if CG.dob is NULL then CG.unit will also be ____________ ?

So, please try this:
WIH CTE 
AS (
                  SELECT
                        str_no
                  FROM cr_store
                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD')
   )
SELECT
      CJ.str_no
    , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT
                  dob
            FROM cr_gnditem S
            WHERE S.unit IN ( SELECT str_no FROM CTE )
      ) CG ON TC.DATE_TIME_START = CG.dob
CROSS JOIN ( SELECT str_no FROM CTE ) CJ
WHERE (TC.date_time_start BETWEEN to_date('2014-03-06', 'YYYY-MM-DD') AND sysdate - 1) 
AND CG.dob IS NULL
ORDER BY CG.dob;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
oh, and also think about how the query will sort by CG.dob if CG.dob IS NULL
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:NiceMan331
Comment Utility
sorry for delay
i just attend the office
i try it , but it gives invalid error at the second line "AS"
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I misspelled WITH
line 1
0
 

Author Comment

by:NiceMan331
Comment Utility
so , shall i remove "WITH" ?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
No. Coŕrect the spelling.
0
 

Author Comment

by:NiceMan331
Comment Utility
i correct it
then it ask to start with "BEGIN"
i added begin at the beggining of the statement and END to its end
then it sent this error
line 2 cloumn 1
 an INTO clause is expected in this SELECT statement
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Why do you need a BEGIN?  From what I understand of the query given by PortletPaul, I don't see a need for one.
0
 

Author Comment

by:NiceMan331
Comment Utility
because it run (using Toad) giving now raw selected , labeling red colour underline word "with" , suggesting to use word "begin" instead
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Changing WITH to BEGIN is going to make it worse.  You need WITH for the query to run correctly.

Also, if it is returning no rows, you should be looking at the subqueries as already suggested to be sure they are returning what you expect.

Do you have a really old version of TOAD?  Support of WITH is a more or less recent thing in Oracle and a really old version of TOAD may not know what that is.  Have you tried running the query with SQL*Plus?
0
 

Author Comment

by:NiceMan331
Comment Utility
i'm using the latest version of Toad , 12.5
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
Comment Utility
WITH .... AS ( .... )

In Oracle documentation this is known as "subquery factoring"; to most of the SQL world however it is known as "Common Table Expression" (CTE)

It is a standard feature in Oracle SQL (it does NOT require PL/SQL)

The REASON for suggesting it was to avoid some repetition.

This will be my final attempt. Please try solving any issues this may produce as I am currently unwell and won't be monitoring.
SELECT
      CJ.str_no
    , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT
                  dob
            FROM cr_gnditem S
            WHERE S.unit IN ( 
                                  /* WITH WOULD SOLVE THIS REPETITION */
                                  SELECT
                                        str_no
                                  FROM cr_store
                                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD')
                            )
      ) CG ON TC.DATE_TIME_START = CG.dob
CROSS JOIN ( 
                                  /* WITH WOULD SOLVE THIS REPETITION */
                                  SELECT
                                        str_no
                                  FROM cr_store
                                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD')
           ) CJ
WHERE (TC.date_time_start BETWEEN to_date('2014-03-06', 'YYYY-MM-DD') AND sysdate - 1) 
AND CG.dob IS NULL
ORDER BY CG.dob
; 

Open in new window

0
 

Author Comment

by:NiceMan331
Comment Utility
it also gives no result
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Without any data I can access myself, I simply cannot resolve "no data" - only you can do that

I have been unwell for several weeks and truly: I am not monitoring - sorry. You can use the "Request Attention" to ask for more assistance - but my advice would be to provide some sample data and an expected result that relates to that data
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , purtel , i appriciate
thanx alot
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I concur with this suggestion:

provide some sample data and an expected result that relates to that data

without a test case, we're all just shooting in the dark.

give us data to work with and a clear answer to target and the process will move a long MUCH faster
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
also, what is your database version?  I'm still curious as to why the WITH clause caused you troubles.

WITH clauses were introduced in 9.2  (2002) and Toad supported it immediately for execution although it's formatter took a little longer.

What is the exact error message you get?  ORA-xxxxx  something?
0
 

Author Comment

by:NiceMan331
Comment Utility
i'm out of office now , i just see your post
actually it is so simple , i should solve it my self , but my mind is out of range
let simplefy it :
if i say :
SELECT
                  unit , dob 
            FROM cr_gnditem 
group by unit , dob

Open in new window


this should give me every day transactions per unit ( dob = trans_date)
in fact , every unit should have at least one transaction every day
ok , till now it is clear , right ?
now , i just want to know if any unit has no transaction in between range of dates
that is all
if we success , we will just add a small condition
where unit in(SELECT
                                        str_no
                                  FROM cr_store
                                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD'))

Open in new window

because the units is too much , we just want a small catigory of it
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> ok , till now it is clear , right ?

no, sample data will help a lot though to clear things up.

I think this is what you "might" be looking for in your missing dates

this will return units that do not have DOB values between your min and max dates

  SELECT unit
    FROM cr_gnditem
GROUP BY unit
  HAVING COUNT(CASE WHEN dob >= :min_date AND dob <= :max_date THEN 1 END) = 0


again though, please provide sample data and expected output.
You only have 3 tables, just create a few rows for each along with the expected output.
It should take very little time on your part and will GREATLY expedite getting you a working answer
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , look at the attached file
i post some sample transactions
for units between 1 to 3
and trans_date between 01-03-2014 and 09-03-2014
the expected result is
day 07-03-2014 for unit 01
and day 09-03-2014 for unit 03
unit 02 has no missing data
sample.xlsx
0
 

Author Comment

by:NiceMan331
Comment Utility
hi
actually i done
1st of all , the simple statement like this
SELECT st.str_no,tc.DATE_TIME_START
FROM calendar_table tc, cr_store st
  and st.str_no||tc.DATE_TIME_START
not IN
(select b.unit||b.dob from cr_gnditem b
group by b.unit,b.dob)
order by st.str_no,tc.DATE_TIME_START;

Open in new window

then , i added the specific conditions to the selected units & dates like this



SELECT st.str_no,tc.DATE_TIME_START
FROM calendar_table tc, cr_store st
 WHERE 
 st.str_no in(select de.str_no
 from cr_store de where de.opdate > to_date('2013-12-28', 'YYYY-MM-DD'))
 and  tc.date_time_start between ST.OPDATE and sysdate-1
 and st.str_no||tc.DATE_TIME_START
not IN
(select b.unit||b.dob from cr_gnditem b where
b.unit in(select de.str_no
 from cr_store de where de.opdate > to_date('2013-12-28', 'YYYY-MM-DD'))
 and b.dob between '01-jan-14' and sysdate-1
group by b.unit,b.dob
having sum(quantity * price) > 0
)
order by st.str_no,tc.DATE_TIME_START;

Open in new window


it gives correct result
now for closing the question , i really appreciate the effort of paurtuel
i can accept his solution as i benefit from his post
but is it ok for EE , and for other members who may search for this question , if i accept a solution which not really the correct one
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
Comment Utility
don't do this...

 and st.str_no||tc.DATE_TIME_START
not IN
(select b.unit||b.dob from cr_gnditem b
group by b.unit,b.dob)

Open in new window


do this instead

 and (st.str_no, tc.DATE_TIME_START)
not IN
(select b.unit, b.dob from cr_gnditem b)

Open in new window



use sets instead of string concatenation - it's more effecient, cleaner and allows for index usage where it can
also, don't use GROUP BY when you only need a DISTINCT,  use GROUP BY when you have aggregates (like count, max,min,avg, etc)

In this case neither GROUP BY nor DISTINCT were correct though, because you had an IN clause which makes the DISTINCT superfluous,  GROUP BY on the other hand, might incur extra work for zero functionality.

In other words,  trying to make  a big IN-list with duplicates smaller by making it unique doesn't help.
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , thanx
in this regard , i would give substubar 100 point
thanx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Passing list of object to Oracle Database Procedure 3 40
PL/SQL - Leading zeros 7 39
Oracle DATE Column Space 11 40
sql query 9 18
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

763 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

11 Experts available now in Live!

Get 1:1 Help Now