|
View previous topic :: View next topic
|
| Author |
Message |
Benchwarmer
New User
Joined: 16 Jul 2016
Posts: 22
Location: desk
|
|
|
|
I am working on a logic which is to assign a unique ID for records matched using a date and flag (Y/N).
It has following rules to assign a unique number
1. If date match
2. If Year in the date match with either month or day along with flag
3. If month and day match with difference in year is 1 along with flag
Initially we assigned unique numbers for a set of records. Now any population comes after need to be matched against the existing population to determine the unique ID.
But am unable to determine how to handle certain situations
e.g., Record from set1 with date 01/17/2017 and flag 'N' is assigned a unique value 008
New population comes in with date and flag as.
| Code: |
01/01/2017 Y
01/17/2017 Y
|
Based on the sequence and matching logic record 1 is not matching with any previous record and assigned a unique value 009, when record 2 is processed it matched with two unique value 008 & 009.
Ideally both the records should have to match with 008.
Any pointers would be helpful.
Thanks |
|
| Back to top |
|
 |
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Quote: |
| Based on the sequence and matching logic record 1 is not matching with any previous record and assigned a unique value 009, when record 2 is processed it matched with two unique value 008 & 009. |
According to your 3 rules, yes record 2 matches both -- so why do you think it should only match record 8?
Since you did not post any of the logic you are using, how can we help you? Are the rules prioritized? |
|
| Back to top |
|
|
Benchwarmer
New User
Joined: 16 Jul 2016
Posts: 22
Location: desk
|
|
|
|
| Quote: |
| According to your 3 rules, yes record 2 matches both -- so why do you think it should only match record 8? |
The ultimate goal of establishing this logic is to assign unique ID based on specified rules and it becomes failure in this case.
| Quote: |
Since you did not post any of the logic you are using, how can we help you? Are the rules prioritized? |
Apologies for that, Record ID is unique to each record.
| Code: |
WS-DATE = '2017-01-01'
WS-FLAG = 'Y'
SELECT UNIQUE_ID
FROM RECORD_TABLE
INNER
JOIN UNIQUE_ID_TABLE B
ON A.RECORD_ID = B.RECORD_ID
WHERE (A.DATE = WS-DATE --> Rule 1
OR (YEAR(DATE) = YEAR(WS-DATE) AND (MONTH(DATE) = MONTH(WS-DATE) OR DAY(DATE) = DAY(WS-DATE)) AND A.FLAG = WS-FLAG) --> Rule 2
OR (YEAR(DATE) BETWEEN YEAR(WS-DATE) - 1 AND YEAR(WS-DATE) + 1 AND (MONTH(DATE) = MONTH(WS-DATE) AND DAY(DATE) = DAY(WS-DATE)) AND A.FLAG = WS-FLAG)) --> Rule 3 |
Even if I prioritize the rules, it is not going to help since the record 1 from previous post does not match for unique number '008' until the record 2 is going to be processed first. I considered using some sort sequence and I don't think it is going to help either.
If you need more info, please let me know. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Make the conditions mutually exclusive, you can only get (at most) a match on one condition.
| Code: |
( condition 1 )
or ( ( condition 2 ) and ( not condition 1 ) )
or ( ( condition 3 ) and ( ( not condition 2 ) and ( not condition 1 ) ) )
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Quote: |
| Ideally both the records should have to match with 008 |
Why will 01/01/2017 be a match as it has 'Y' flag instead of 'N' to get matched? |
|
| Back to top |
|
|
|
|
