Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Checking for duplicate

I have a query as follows

SELECT COUNT ( 1 )
  FROM  SCHEME INNER JOIN  SCHEMEDETAIL
          ON  SCHEME.SCHEME_ID =  SCHEMEDETAIL.SCHEME_ID
 WHERE      SCHEME.POINTOFSALE_ID = 1
       AND  SCHEMEDETAIL.PRODUCT_ID = 1
       AND  SCHEME.SCHEME_ID = 0
       AND  SCHEMEDETAIL.FLAG = 0
       and (TO_DATE ('25/04/2011 2:35:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM
                                                                        AND  EFFECTIVEDATETO)
       OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM
                                                                           AND  EFFECTIVEDATETO)

in my DB the values are
EFFECTIVEDATEFROM 25/04/2011 2:36:38 PM
EFFECTIVEDATETO 29/04/2011 2:36:38 PM

Since the query i have written falls within the range, it should actually return count as 1 record. but it returns 0
0
GRChandrashekar
Asked:
GRChandrashekar
  • 12
  • 10
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Unless I mis-reading this, the dates you provided are not between the other dates so 0 is correct.

25/04/2011 2:35:38 PM is not between 25/04/2011 2:36:38 PM and 29/04/2011 2:36:38 PM
and
neither is:  29/04/2011 2:38:38 PM
0
 
GRChandrashekarAuthor Commented:
I mean to say it returns 1 which is not correct
0
 
GRChandrashekarAuthor Commented:
sorry please wait
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
GRChandrashekarAuthor Commented:
the result i wanted is

and (TO_DATE ('25/04/2011 2:35:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM
                                                                        AND  EFFECTIVEDATETO)
       OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM
                                                                           AND  EFFECTIVEDATETO)


in my DB the values are
EFFECTIVEDATEFROM 25/04/2011 2:36:38 PM
EFFECTIVEDATETO 29/04/2011 2:36:38 PM

the query may be wrong. I want it to return 0 since it falls within date range though it may not fall within time range
0
 
GRChandrashekarAuthor Commented:
on the dates 26,27, and 28 it will be duplcated otehrwise
0
 
slightwv (䄆 Netminder) Commented:
>>. I want it to return 0 since it falls within date range though it may not fall within time range

I don't understand what this means.
0
 
OP_ZaharinCommented:
GRChandrashekar,
- when you use BETWEEN - AND statement for a date, the COUNT() will return 1 or more records because that given date is falls between the date in your database. so your program must test it against =1 (falls date found) not =0 (no falls date found). and if falls date found, give error message "date exist" and tell user to change the date.

-  however if you want it to be the other way around, you can use NOT BETWEEN - AND. which will return =0 (falls date found) but that will be confusing for other programmer later on.

0
 
GRChandrashekarAuthor Commented:
How do i do this

- when you use BETWEEN - AND statement for a date, the COUNT() will return 1 or more records because that given date is falls between the date in your database. so your program must test it against =1 (falls date found) not =0 (no falls date found). and if falls date found, give error message "date exist"
0
 
OP_ZaharinCommented:
- my explanation above is actually answering to your following statement:
".. I want it to return 0 since it falls within date range though it may not fall within time range."
my explanation is, the sql you run return count()=1 because there is data exist within the date range. so why not use the result =1?

- if you want the result to be =0 for data exist within the date range then change your sql to NOT BETWEEN-AND instead of BETWEEN-AND.
0
 
GRChandrashekarAuthor Commented:
Sorry for confusion

".. I want it to return 1 since it falls within date range though it may not fall within time range."
0
 
OP_ZaharinCommented:
- ok, lets do a simple test. if you run the following sql, does it return 0 or 1?
- replace the tblName with your actual tablename, i'm not sure whether its SCHEME or SCHEMEDETAIL.

SELECT COUNT (1)
FROM  tblName
WHERE (TO_DATE ('25/04/2011 2:35:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM AND  EFFECTIVEDATETO)
OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM  AND  EFFECTIVEDATETO)

Open in new window


- does the query above return the correct value as it should? if it does return the correct value, then you have to look into the other condition does it meet or not:
SCHEME.POINTOFSALE_ID = 1
AND  SCHEMEDETAIL.PRODUCT_ID = 1
AND  SCHEME.SCHEME_ID = 0
AND  SCHEMEDETAIL.FLAG = 0
0
 
GRChandrashekarAuthor Commented:
THE QUERY RETURNS 0 RECORD INSTEAD OF 1

other condition DOES NOT MATTER SINCE FOR TEST I AM NOT REFFERRING TO THOSE AND USING ONLY ONE TABLE SCHEME
0
 
OP_ZaharinCommented:
- have you try with TO_DATE() on EFFECTIVEDATEFROM  AND  EFFECTIVEDATETO column as i show you on the previous question?
0
 
GRChandrashekarAuthor Commented:
Tried Like this
SELECT COUNT (1)
FROM  SCHEME
WHERE (TO_DATE ('25/04/2011 2:35:38 PM', 'DD/MM/YYYY hh:mi:ss PM')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY hh:mi:ss PM') AND  TO_DATE(EFFECTIVEDATETO,'DD/MM/YYYY hh:mi:ss PM'))
OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY hh:mi:ss PM')  AND TO_DATE( EFFECTIVEDATETO,'DD/MM/YYYY hh:mi:ss PM'))

returns 0 again
0
 
OP_ZaharinCommented:
i just realize from your earlier posting:

"--- and (TO_DATE ('25/04/2011 2:35:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM AND  EFFECTIVEDATETO) OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM') BETWEEN EFFECTIVEDATEFROM AND  EFFECTIVEDATETO)

in my DB the values are
EFFECTIVEDATEFROM 25/04/2011 2:36:38 PM
EFFECTIVEDATETO 29/04/2011 2:36:38 PM --- "

- of course the result will be 0 because the datetime are outside the range. check carefully the time. i have tested on my side, if i change as follows, it will return 1
: '25/04/2011 2:35:38 PM' to '25/04/2011 2:37:38 PM'
: '29/04/2011 2:38:38 PM' to '29/04/2011 2:36:38 PM

0
 
GRChandrashekarAuthor Commented:
Right
But.... only time is different. I mean to say it is still within range because there are dates in between (26,27, & 28 ) ! so it is still a duplicate record
0
 
OP_ZaharinCommented:
- i think we are confusing ourself here :)

- you DB value is:
EFFECTIVEDATEFROM 25/04/2011 2:36:38 PM
EFFECTIVEDATETO 29/04/2011 2:36:38 PM

- using your initial data, there will be no duplicate so it return value 0 which is CORRECT. no duplicate because both is not in the range of the above:
25/04/2011 2:35:38 PM
29/04/2011 2:38:38 PM

- if you were saying, you want to compare only dates NOT the time then use TO_DATE (datevalue, 'DD/MM/YYYY') even though there is a time in your value and the database, it will compare only the date. you cannot use the date+time format to compare only on the date. it will not work.

SELECT COUNT (1)
FROM  SCHEME
WHERE (TO_DATE ('25/04/2011 2:35:38 PM', 'DD/MM/YYYY') 
BETWEEN TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY') AND  TO_DATE(EFFECTIVEDATETO,'DD/MM/YYYY'))
OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY') 
BETWEEN TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY')  AND TO_DATE( EFFECTIVEDATETO,'DD/MM/YYYY'))

Open in new window


0
 
GRChandrashekarAuthor Commented:
Some error... date format picture end...
0
 
GRChandrashekarAuthor Commented:
SELECT COUNT (1)
FROM  SCHEME
WHERE (TO_DATE ('25/04/2011', 'DD/MM/YYYY')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY') AND  TO_DATE(EFFECTIVEDATETO,'DD/MM/YYYY'))
OR (TO_DATE ('29/04/2011', 'DD/MM/YYYY')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY')  AND TO_DATE( EFFECTIVEDATETO,'DD/MM/YYYY'))

With this still it returns 0 instead of 1
0
 
OP_ZaharinCommented:
- can you do SELECT TO_DATE(EFFECTIVEDATEFROM,'DD/MM/YYYY') from SCHEME. how does the output look like?
0
 
OP_ZaharinCommented:
- i'm running a simple test below and it return 1. will be going home now, we shall continue in 1 hour time hopefully :)

SELECT COUNT (1)
FROM  dual
WHERE (TO_DATE('26/04/2011', 'DD/MM/YYYY') 
BETWEEN TO_DATE('26/04/2011','DD/MM/YYYY') AND  TO_DATE('28/04/2011','DD/MM/YYYY'))
OR (TO_DATE('29/04/2011', 'DD/MM/YYYY') 
BETWEEN TO_DATE('26/04/2011','DD/MM/YYYY')  AND TO_DATE('28/04/2011','DD/MM/YYYY'))

Open in new window

0
 
GRChandrashekarAuthor Commented:
Yes this returns 1

and i see you are passing 3 diff dates.

In my case what should I pass
0
 
OP_ZaharinCommented:
- since your db contain 25/04/2011 2:35:38 PM and 29/04/2011 2:38:38 PM, try to pass 27/04/2011 or  28/04/2011. it should return 1 because it within the 25 - 29/04/2011.
0
 
OP_ZaharinCommented:
- check also the datatype for EFFECTIVEDATEFROM and EFFECTIVEDATETO must be DATE
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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