jknj72
asked on
Logic with dates
I have 3 dates. The 3 dates are eff_dt, exp_dt and ins_dt and I want to create a function and the logic would be as follows
1. Check for valid dates and move on to step 2 after checks are complete
a. eff_dt check for valid date and no null
b. exp_dt check for valid date and no null
c. ins_dt check for valid date and null is ok
2. Check ins_dt >= exp_dt (if True then Function --> function returns Good is done)
3. Check ins_dt = eff_dt (if True then Function --> function returns BAD and is done)
4. Check Todays Date > ins_dt (if True then Function --> function returns BAD and is done)
If all meet criteria then return GOOD
I have a CASE statement but it seems sloppy to me. How would you go about designing this function?
1. Check for valid dates and move on to step 2 after checks are complete
a. eff_dt check for valid date and no null
b. exp_dt check for valid date and no null
c. ins_dt check for valid date and null is ok
2. Check ins_dt >= exp_dt (if True then Function --> function returns Good is done)
3. Check ins_dt = eff_dt (if True then Function --> function returns BAD and is done)
4. Check Todays Date > ins_dt (if True then Function --> function returns BAD and is done)
If all meet criteria then return GOOD
I have a CASE statement but it seems sloppy to me. How would you go about designing this function?
Eyeballeth thy article on T-SQL: Identify bad dates in a time series and let me know how close it comes to what you're trying to pull off here. If I'm interpreting this correctly you can modify the big honkin' nested Common Table Expression (CTE) to meet your needs, and pull this off as a set instead of a single row / single value function.
First thing to me is define a valid date. If these are stored in fields that are of DATE datatype, then they are valid.
What is the end goal here? I believe that you could create constraints to handle these and then there is no need for checking.
What is the end goal here? I believe that you could create constraints to handle these and then there is no need for checking.
ASKER
Jim - I will check the article out ..Thanks
Johnsone - Data comes from tables that allow Nulls and data types are Date but I cant edit tables.. I have to check to make sure there are dates in the first 2 fields and Nulls are not acceptable in my logic and the 3rd field check for a date and a Null will be acceptable to move to the next step..
Im doing this in Oracle(SQL Developer) sdstuber?
Johnsone - Data comes from tables that allow Nulls and data types are Date but I cant edit tables.. I have to check to make sure there are dates in the first 2 fields and Nulls are not acceptable in my logic and the 3rd field check for a date and a Null will be acceptable to move to the next step..
Im doing this in Oracle(SQL Developer) sdstuber?
If the data type is Date, then it must be a valid date.
The tool doesn't matter. The database does. All SQL Developer is doing is sending a query to SQL Server over a connection. It really isn't involved.
The tool doesn't matter. The database does. All SQL Developer is doing is sending a query to SQL Server over a connection. It really isn't involved.
ASKER
thanks sdstuber!!
johnsone, I guess that makes sense and doesn't need to check for a VALID date. I do however need to check the rest of the logic. How would you do it? I just want a function to return (true or false) or (0 or 1) or whatever to let me know if it passes the checks. I guess im just gonna keep the CASE statement I have, or maybe have an If elsif statement, I just thought maybe Id get some other opinions...
johnsone, I guess that makes sense and doesn't need to check for a VALID date. I do however need to check the rest of the logic. How would you do it? I just want a function to return (true or false) or (0 or 1) or whatever to let me know if it passes the checks. I guess im just gonna keep the CASE statement I have, or maybe have an If elsif statement, I just thought maybe Id get some other opinions...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
pretty much what I have sdstuber and im testing now and so far all looks good. Thanks for the help
ASKER
Thanks
ASKER