Issue with DATEDIFF "w"

• jeichelber

  Old Hand

  Points: 326

  More actions

  June 24, 2014 at 9:18 am

  #295892

  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.

• jeichelber

  Old Hand

  Points: 326

  More actions

  June 30, 2014 at 8:33 am

  #1725880

  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.