I need to pull the last week end date where my week stat day is 'monday' and END day is 'Sunday'. When i am running below query it is working fine for all days except for Monday. Please find the results. I am using TD 12.0.0.03 ver
select distinct date '2013-12-23'-dayofweek(date '2013-12-23'-2)-1 from fact;
ans : 12/15/2013
select distinct date '2013-12-24'-dayofweek(date '2013-12-24'-2)-1 from fact;
select distinct date '2013-12-25'-dayofweek(date '2013-12-25'-2)-1 from fact;
12/22/2013 is the last week end date .
Please help me to right a query which will works for all the days to go last week end.
Thanks in advance !
Did you reading/answer the old post instead of opening a new one?
Why do you use a very old version, 12.0.0.03, which is definitely no production system?
dayofweek is based on a week starting on sunday not monday, you have to adjust your logic based on this (i already posted a SQL UDF with a calculation to get a begin of a week based on ISO).
As i wrote, since 13.10 you could easily do a SQL UDF and for 14 there's a DAYNUMBER_OF_WEEK:
SELECT current_date as d,
d - DAYNUMBER_OF_WEEK(d,'iso'),
d - d-((dayofweek(d) + 5) MOD 7 + 1)