Issue with DATEDIFF "w"

  • Hello All,

    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:

    =DateDiff("w",Fields!duedate.Value,Fields!shipdate.Value)

    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.

  • All,

    I was able to resolve this issue using this code:

    = DatePart("ww",Fields!duedate.Value,IIF(year(Fields!duedate.Value) > 2010,vbSunday,vbSaturday))

    This provided the week number and I was able to go from there.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply