09-20-2012 02:55 AM
I'd appreciate good advice on a problem I'm faced with in DI Studio.
My job includes a source table and an extract transformation. The source table has a datetime variable called "Last_Changed". If today is Monday, the Extract must get all rows where the Last_Changed weekday is between Fri-Sun the previous week. If today to Tue-Fri, the Extract must get all rows from yesterday. I've figured out some of the code:
If weekday(&sysdate"d) = 2 (Monday), extract all rows where "Last_Changed" between "&sysdate"d-3 and "&sysdate"d-1 (previous Friday-Sunday). Else, if weekday(&sysdate"d) in (3,4,5,6) (Tuesday-Friday), extract all rows where "&sysdate"d = today()-1.
My problem is that I can't quite figure out how to set this up in the Extract precode and where-statement.
Thanks for your time.
09-20-2012 03:25 AM
I guess you already figured a valid where expression
(weekday("&sysdate"d) = 2 and ("&sysdate"d-1) <= LAST_CHANGED between <= ("&sysdate"d-3)) or
(weekday("&sysdate"d) in (3,4,5,6) and LAST_CHANGED = "&sysdate"d-1)
This should extract everything you need.
If LAST_CHANGED is in a date/time form, just apply the DATEPART function to extract the Date portion of it, as the "&SYSDATE" var is represented with a Date only format (DATE9.)
More on DATEPART function here:
Cheers from Portugal.
Daniel Santos @ www.cgd.pt
09-20-2012 05:42 AM
You can try this code...not tested in DI...
%if &dd in 3,4,5,6 %then %do;
where date between "&sysdate"d-3 and "&sysdate"d-1 ;
%else %if &dd =2 %then %do;
where date between "&sysdate"d and "&sysdate"d-1 ;