We have a need for a report to run from the MOST RECENT Wednesday back one week.
00:00:00 Wednesday back to the PREVIOUS
00:00:00 Wednesday
What the best way to get that most recent week, w/out manually scheduling?
You need to calculate the most recent 00:00:00 Wednesday. From there, you can calculate BACK some period (in our case, 7 days).
I cheat a little, as I under-account for a second in my final select. I'll fix that if they fuss about it.
My solution:
DayOfWeek will let you choose what day that the week starts on. I use the reserved work crWednesday, but I could use the number 4 also.
Start w/
DayOfWeek(today, crWednesday)
This will give you the correct reference. From there, we add (subtract) days to get BACK to Wednesday:
DateAdd("d", -(DayOfWeek(today, crWednesday) - 1), today)
So, if TODAY _is_ Wednesday, we get DoW=1, then wind up subtracting DoW - 1 (= 0) to Today. If today were Friday, we'd get 3 - 1 = 2, and subtract 2 days from Friday -- back to Wednesday.
To go back in time 7 days, we keep the same reference, and just subtract an additional 7 days:
DateAdd("d", -(DayOfWeek(today, crWednesday) - 8), today)
I use these lower and upper bounds (after converting to epoch time) as the basis of my Where clause. The reason I don't make these into @formulas is, I want the whole Where statement to parse. If I use @formulas, the date ranges drops from the select and I pull back additional records which are then processed locally.
The beauty is, if they change the period they want to run the report on -- say they want to run it Saturday, then I change two values, and I'm "golden". If they decide they want to go back TWO weeks, I modify the "8" (which was 1 + 7 days) to be 15 (1 + 14 days) and I'm golden again.
So... how does it look Is this the easiest way?
Marc
--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports
00:00:00 Wednesday back to the PREVIOUS
00:00:00 Wednesday
What the best way to get that most recent week, w/out manually scheduling?
You need to calculate the most recent 00:00:00 Wednesday. From there, you can calculate BACK some period (in our case, 7 days).
I cheat a little, as I under-account for a second in my final select. I'll fix that if they fuss about it.
My solution:
DayOfWeek will let you choose what day that the week starts on. I use the reserved work crWednesday, but I could use the number 4 also.
Start w/
DayOfWeek(today, crWednesday)
This will give you the correct reference. From there, we add (subtract) days to get BACK to Wednesday:
DateAdd("d", -(DayOfWeek(today, crWednesday) - 1), today)
So, if TODAY _is_ Wednesday, we get DoW=1, then wind up subtracting DoW - 1 (= 0) to Today. If today were Friday, we'd get 3 - 1 = 2, and subtract 2 days from Friday -- back to Wednesday.
To go back in time 7 days, we keep the same reference, and just subtract an additional 7 days:
DateAdd("d", -(DayOfWeek(today, crWednesday) - 8), today)
I use these lower and upper bounds (after converting to epoch time) as the basis of my Where clause. The reason I don't make these into @formulas is, I want the whole Where statement to parse. If I use @formulas, the date ranges drops from the select and I pull back additional records which are then processed locally.
The beauty is, if they change the period they want to run the report on -- say they want to run it Saturday, then I change two values, and I'm "golden". If they decide they want to go back TWO weeks, I modify the "8" (which was 1 + 7 days) to be 15 (1 + 14 days) and I'm golden again.
So... how does it look Is this the easiest way?
Marc
--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports