Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to left join to a table and only show the records from the Product table that meet the criteria:
Active_Date >= Effective_Date and Active_Date<Term_Date
I am struggling with the getting the proper code/syntax
Example code withouth the above crtieria:
Table_1:
Load
Product ,
Active_Date
Resident Table;
Left Join (Table_1)
Load
Product,
Type_Flag,
Effective_Date,
Term_Date
Resident Product;
for date field
ex : 20101031 use this expression
makedate(left(20101031,4),mid(20101031,5,2),right(20101031,2))
for ex: 40369 use date(40369)
HOPE IT HELPS
You'll have to do the join first and then filter the records with a where clause.
Table_1:
Load
Product ,
Active_Date
Resident Table;
Left Join (Table_1)
Load
Product,
Type_Flag,
Effective_Date,
Term_Date
Resident Product;
Result:
noconcatenate
load * resident Table_1
where Active_Date >= Effective_Date and Active_Date<Term_Date;
drop table Table_1;
Make sure the date fields are all real dates and not strings.
You could also try to use the intervalmatch function. See this blog post: IntervalMatch
Thanks!
The date fields are screwing me up
Effective data and term date are in this format (ex 20101031) while the service date is a number (ex. 40369)
I always struggle knowing the formats and getting them into the same format for evaluation purposes. Any expertise you can provide?
for date field
ex : 20101031 use this expression
makedate(left(20101031,4),mid(20101031,5,2),right(20101031,2))
for ex: 40369 use date(40369)
HOPE IT HELPS
Yes this did help -
Is it best in situations like this to get it back to a date field or try to convert to a number if there is a need to compare these fields?
it really depend on the script.........
i usally change everything into number format like '40369' and in the front end at chart
i use date in number tab