SQL Datediff Query

[AnnieRider]

I have a SSRS report that includes the following expression to show the amount of working days between two dates.

=DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbMonday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbTuesday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbWednesday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbThursday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbFriday)

I want to make this selection within a view so that I can get the report to select only those where the above value is over 5.

So far I have got the following:

SELECT dbo.CR_HISTORY.Client_Ref, dbo.CR_CLIENTS.NameFirst, dbo.CR_CLIENTS.Surname, dbo.CONTACTS.DisplayName, dbo.SCC_TEAM.Description,
dbo.CR_HISTORY.HistoryType_Ref, dbo.CR_HISTORY.DateEntered, dbo.CR_HISTORY.DateStart, dbo.CR_HISTORY.Notes, dbo.CONTACTS.Org_Ref,
(DateDiff ("ww", dbo.CR_HISTORY.DateStart, dbo.CR_HISTORY.DateEntered, vbMonday) +
DateDiff ("ww", dbo.CR_HISTORY.DateStart, dbo.CR_HISTORY.DateEntered, vbTuesday) +
DateDiff ("ww", dbo.CR_HISTORY.DateStart, dbo.CR_HISTORY.DateEntered, vbWednesday) +
DateDiff ("ww", dbo.CR_HISTORY.DateStart, dbo.CR_HISTORY.DateEntered, vbThursday) +
DateDiff ("ww", dbo.CR_HISTORY.DateStart, dbo.CR_HISTORY.DateEntered, vbFriday)) as Difference
FROM dbo.CONTACTS INNER JOIN
dbo.CR_HISTORY ON dbo.CONTACTS.ID = dbo.CR_HISTORY.ContactID INNER JOIN
dbo.SCC_TEAM ON dbo.CONTACTS.Team = dbo.SCC_TEAM.Key_Ref INNER JOIN
dbo.SCH_TYPES ON dbo.CR_HISTORY.HistoryType_Ref = dbo.SCH_TYPES.Key_Ref LEFT OUTER JOIN
dbo.CR_CLIENTS ON dbo.CR_HISTORY.Client_Ref = dbo.CR_CLIENTS.Client_Ref

but am told that

The datediff function requires 3 argument(s).

I confess that I'm not totally sure how the datediff for counting week days works and realise that there is also probably some incorrect syntax - I've only changed the field references as my knowledge kind of stops there.

Does anyone know how alter the above so that it works in SQL?

Many Thanks
Annie

 

[LKBrwnDBA]

Check the T-SQL reference



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb