How do I omit weekends from a day count?

[ira4th]

I have created a report using Report Builder for SQL Reporting Services which is used with our Service Desk software. The report shows all incidents that have been open for 3 days or more and the number of days the incidents have been open. I need to be able to omit weekends from the filter and the number of days the incidents have been open, so the report can show the number of work days an incident has been open. Is this possible and if so, how? Thanks in advance.

[RedSon]

Couldn't you just subtract a multiple of two days for every item that is more than a multiple of 7 days old?

[ira4th]

@RedSon
Yes & no. The report is for incidents 3 days or older and its run on Tuesdays (I know, weird for a weekly report) so there's the weekend to deal with there. Also, I do not have much experience with SQL, so I would have to do it manually and I am trying to avoid that. I came to this site because I was searching for help with SQL queries/coding.

[ck9663]

Check the dataname of the weekday to exclude whatever day you want to exclude.

Happy Coding!!!

~~ CK

[Delerna]

I do not have much experience with SQL

I come here to lend a helping hand to those who want to improve their experience.

I came to this site because I was searching for help with SQL queries/coding

So if you really are looking for guidance to help you solve the problem and not a ready made solution done for you......then

There is no "magic" way to do this. At least ... there isn't in SQL Server 2000 that I am aware of.
You will have to perform math in your query to calculate it

getdate returns the current date
weekday returns the day of the week for a date. 0=Sun,1=Mon .... etc

using those to functions as well as the NumDaysIncidentOpen in your query you can use math to get a precise NumWorkDaysIncidentOpen in the query.

It can get a bit hairy if you have partial weekends but since you call it "Number Of Workdays" I assume you won't have any.

Something like this
Find out how many workdays for the current week to today
Subtract that from NumDaysIncidentOpen
If remainder is positive then divide the remainder of days by 7 to get the number of weekends
Subtract NumWeekends * 2 from NumDaysIncidentOpen to get the Number of workdays.