I have looked on a few dozen web pages and cannot find the answer to this one.
Here is what I am trying to do:
My company would like a report stating how many shipments were "On-Time" in the last week.
This report will show if a shipment is early (w -1), On-Time (w 0), 1 week late (w +1), 2 weeks late (w +2), 3+ weeks late (w +3...)
They anchor these numbers to the calendar week.
For instance, if the due date was 06/18/14 they consider it "On-Time" if it is shipped any time between 06/15/14 - 06/21/14. The week starts on Sundays.
Now, on to what I have tried.
I created a column named "DateDiff" (Not very original) and used this code:
I am running into an issue because it seems that SSRS calculates a week as 7 days from the duedate.
For instance, a shipment with a due date of 06/17/14 which was shipped on 06/11/14 is showing as "On-Time" ( w 0) instead of "Early" (w -1) even though it is in a previous calendar week.
Can someone direct me on how to resolve this?
P.S. I cannot alter the query code at the source because I am connecting to a very old PROGRESS db that doesn't allow logic like this.