Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with DATEDIFF "w" Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:02 AM
Points: 14, Visits: 67
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.

Post #1585559
Posted Monday, June 30, 2014 8:33 AM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:02 AM
Points: 14, Visits: 67
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.
Post #1587660
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse