Scheduling issue 1

[elsolo21]

I have a report that has two date prompts that needs to be scheduled Mon-fri, but the dates are different based on what day of the week it is. My chart is (Date1 and Date2 are referring to the day in the previous week):

Today Date1 Date2

Monday Wed Thurs
Tuesday Thurs Sun
Wednesday Sun Monday
Thursday Monday Tues
Friday Tues Wed

I know I have to say if the day of the week is "whatever today is" then date1=x and date2=y but I'm not very familiar with date functions to be able to go back and get the previous weekday on the chart above.

Anyone have a slick way to do this?

thanks,
-Eric

 

[drlex]

You don't mention what application you're using, so I'll be general here.
Looking at what you've posted, I see that on a Monday or Tuesday, you need to go back 5 days and 4 days for dates 1 and 2; Wednesday to Friday is only 3 and 2 days respectively.

If your database enumerates days of the week as 1 = Sunday and 7 = Saturday, then you could work on

For date1
IF DATEPART(dw,(now()) IN (2,3) THEN DATEADD(dd, -4, now()) ELSE DATEADD(dd,-2,now())

DATEPART(dw, <date>) returns 1-7; you might have DAYOFWEEK(<date>). Similarly DATEADD(dd, int, <date>) adds int days to the date supplied.

soi là, soi carré

 

[elsolo21]

Thanks, that gave me the general idea of what to do. I had to use different functions but the idea was the same. I'm using Cognos Report Studio 8.3. I apologize for leaving that out before. The statement I used for one of the dates was:

(IF (_day_of_week(current_date,1) IN (1,2)) THEN (_add_days(current_date,-5)) ELSE (_add_days(current_date,-3)))

thanks again,
-Eric

 

[drlex]

Eric,
You're welcome; thanks for the accolade.
Bear in mind that if your dates include a time component, then adding days to the current datetime may result in required data being missed.
e.g. A report run at 10am that references a day prior using an add_days of -1 will exclude anything prior to 10am on the previous day.

soi là, soi carré

 

[elsolo21]

Since our tables are updated nightly, I made sure that the report is scheduled mid-morning to avoid time issues. The only time I will have problems is if there are technical problems on the backend and the database isn't updated correctly. But that's always an issue with whatever you do.

thanks again,
-Eric