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
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
ASKER
I mean to say it returns 1 which is not correct
ASKER
sorry please wait
ASKER
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
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
ASKER
on the dates 26,27, and 28 it will be duplcated otehrwise
>>. 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.
I don't understand what this means.
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.
- 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.
ASKER
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"
- 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"
- 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.
".. 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.
ASKER
Sorry for confusion
".. I want it to return 1 since it falls within date range though it may not fall within time range."
".. I want it to return 1 since it falls within date range though it may not fall within time range."
- 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.
- 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
- 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)- 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
ASKER
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
other condition DOES NOT MATTER SINCE FOR TEST I AM NOT REFFERRING TO THOSE AND USING ONLY ONE TABLE SCHEME
- have you try with TO_DATE() on EFFECTIVEDATEFROM AND EFFECTIVEDATETO column as i show you on the previous question?
ASKER
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,
OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,
returns 0 again
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,
OR (TO_DATE ('29/04/2011 2:38:38 PM', 'DD/MM/YYYY hh:mi:ss PM')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,
returns 0 again
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
"--- 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
ASKER
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
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
- 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.
- 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'))ASKER
Some error... date format picture end...
ASKER
SELECT COUNT (1)
FROM SCHEME
WHERE (TO_DATE ('25/04/2011', 'DD/MM/YYYY')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,
OR (TO_DATE ('29/04/2011', 'DD/MM/YYYY')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,
With this still it returns 0 instead of 1
FROM SCHEME
WHERE (TO_DATE ('25/04/2011', 'DD/MM/YYYY')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,
OR (TO_DATE ('29/04/2011', 'DD/MM/YYYY')
BETWEEN TO_DATE(EFFECTIVEDATEFROM,
With this still it returns 0 instead of 1
- can you do SELECT TO_DATE(EFFECTIVEDATEFROM,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes this returns 1
and i see you are passing 3 diff dates.
In my case what should I pass
and i see you are passing 3 diff dates.
In my case what should I pass
- 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.
- check also the datatype for EFFECTIVEDATEFROM and EFFECTIVEDATETO must be DATE
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