Exclude Sundays

[robcarr]

Dear All,

I am using this query to calculate Total items completed within a date range and then the AHT and SLA for those items, how can i get the query to remove 1440 mins from the total if the date range contains a Sunday.

SELECT tblMailItems.UniqueID, tblMailItems.ItemType, tblItemTypes.ItemAHT, tblItemTypes.ItemSLA, tblMailItems.CaseStatus, tblMailItems.DateLogged, tblMailItems.DateClosed, DateDiff("n",[datelogged],[dateclosed]) AS AHTITems, IIf(DateDiff("n",[datelogged],[dateclosed])<[itemSLA],100,0) AS Totals
FROM tblItemTypes INNER JOIN tblMailItems ON tblItemTypes.ItemType = tblMailItems.ItemType
WHERE (((tblMailItems.CaseStatus)="Closed"));

Hope this is of use, Rob.

 

[lupins46]

?
Your 'Totals' value is only allowed to be 0 or 100.
How are you proposing to subtract 1440 from it?

 

[robcarr]

we either hit SLA or we dont so it is either 0 or 100,
this works fine I just need to know how to find out how many sundays are within the date range [datelogged] and[dateclosed].

thanks

Hope this is of use, Rob.

 

[lupins46]

You would have to create a function to count the Sundays.

Function GetSundays(Date1 As Date, date2 As Date)
Dim x As Date
Dim y As Integer
y = 0

For x = Date1 To date2
If Weekday(x) = 1 Then y = y + 1
Next x
GetSundays = y

End Function


?getsundays("2004-09-01","2004-09-23")
3

So you would use:
getsundays(datelogged,dateclosed)