Exclude weekends from DateAdd function in SQL server

What I am building is an application that is gonna help manage periodical maintenance of some equipements.

I have 3 tables: Equipements, TypesEntretiens (maintenance types)  and Entretiens (Maintenances instances) (see diagram in attached file)

What I want to find every type of maintenance for every equipement for which the last maintenance is older than the required maintenance periode.

It works well with the following query except that I need to exclude weekends (i don't really care about holidays)



How can I modify my query so it exclude the weekend from the calculations?

thanks
SELECT     dbo.tbl_ENT_EQUIPEMENTS.idEQ, dbo.tbl_ENT_EQUIPEMENTS.nameEQ, dbo.tbl_ENT_TYPES_ENTRETIENS.idTypeEN, MAX(dbo.tbl_ENT_ENTRETIENS.dateEN) AS LastEN, DATEADD([day],    dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN / dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ, dbo.tbl_ENT_ENTRETIENS.dateEN) AS NextEN, dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN, dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ
FROM         dbo.tbl_ENT_EQUIPEMENTS INNER JOIN dbo.tbl_ENT_TYPES_ENTRETIENS ON dbo.tbl_ENT_EQUIPEMENTS._idTypeEQ = dbo.tbl_ENT_TYPES_ENTRETIENS._idTypeEQ LEFT OUTER JOIN
             dbo.tbl_ENT_ENTRETIENS ON dbo.tbl_ENT_EQUIPEMENTS.idEQ = dbo.tbl_ENT_ENTRETIENS._idEQ 
GROUP BY dbo.tbl_ENT_EQUIPEMENTS.idEQ, dbo.tbl_ENT_EQUIPEMENTS.nameEQ, dbo.tbl_ENT_TYPES_ENTRETIENS.idTypeEN, 
                      dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN, DATEADD([day], 
                      dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN / dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ, dbo.tbl_ENT_ENTRETIENS.dateEN), 
                      dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ
HAVING      (DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN / dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ, 
                      dbo.tbl_ENT_ENTRETIENS.dateEN) < GETDATE() OR
                      DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN / dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ, 
                      dbo.tbl_ENT_ENTRETIENS.dateEN) IS NULL)

Open in new window

query.jpg
LVL 7
aboredmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

folderolCommented:
Am I correct, you are taking the number of hours run on an equipment, and dividing it by some factor to get the elapsed days from last service?  You need to compare this number to the elapsed time between the start and end (today), and not compare it to the date value for getdate().

To do this,  you need a HAVING formula something like this

 dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN / dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ > 
                      datediff(day, dbo.tbl_ENT_ENTRETIENS.dateEN, GETDATE() ) -
                     floor(datediff(day, dbo.tbl_ENT_ENTRETIENS.dateEN, GETDATE() ) / 7) * 2   -- count weekends

This will give a close approximation.
0
aboredmanAuthor Commented:
Not exactly.

Right now what I check is:

DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.HoursBetweenEN / dbo.tbl_ENT_EQUIPEMENTS.DailyUseEQ, dbo.tbl_ENT_ENTRETIENS.dateEN) < GETDATE()

Which basicly adds the following:
recommanded numbers of hours between maintenances / daily use of this device (which gives me the number of day that should be elapsed between maintenaces)
to the date of the last maintenance.

So LastMaintenanceDate + recommanded number of days between maintenance = when next maintenance should occur.

And I compare this with today. If NextMaintenanceDate < getDate() then the maintenance is required. otherwise it ain't.

What I want to do is:
instead of having: LastMaintenanceDate + recommanded number of days between maintenance = when next maintenance should occur.

I want: LastMaintenanceDate + recommanded number of WORKING (Monday-friday) days between maintenance = when next maintenance should occur.
0
aboredmanAuthor Commented:
Ok this is not perfect but this query give me all the NestMaintenanceDates for every possible configuration of Equipement / Type of maintenance.

The algorithm is not perfect when spanned over several weeks (more than 8) an can lead to a maintenance date recommandation on a weekend but it's good enough for what I need to do.

If anyone else can come up with a better query I will leave the question open for a few days.

thanks
SELECT     dbo.tbl_ENT_EQUIPEMENTS.idEQ, dbo.tbl_ENT_EQUIPEMENTS.nomEQ, dbo.tbl_ENT_TYPES_ENTRETIENS.idTypeEN, 
                      dbo.tbl_ENT_ENTRETIENS.dateEN, dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN, dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, 
                      dateadd([day], 2 * (DATEDIFF([week], dbo.tbl_ENT_ENTRETIENS.dateEN, DATEADD([day], 
                      (dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ), dbo.tbl_ENT_ENTRETIENS.dateEN))) 
                      + (CASE WHEN DATENAME(dw, DATEADD([day], 
                      (dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ), dbo.tbl_ENT_ENTRETIENS.dateEN)) 
                      = 'Saturday' AND (2 * (DATEDIFF([week], dbo.tbl_ENT_ENTRETIENS.dateEN, DATEADD([day], 
                      (dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ), dbo.tbl_ENT_ENTRETIENS.dateEN)))) 
                      > 1 THEN 2 ELSE 0 END) + (CASE WHEN DATENAME(dw, dbo.tbl_ENT_ENTRETIENS.dateEN) = 'Sunday' THEN 1 ELSE 0 END) 
                      + (CASE WHEN DATENAME(dw, (DATEADD([day], 
                      dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, dbo.tbl_ENT_ENTRETIENS.dateEN) 
                      + 2 * (DATEDIFF([week], dbo.tbl_ENT_ENTRETIENS.dateEN, DATEADD([day], 
                      dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, dbo.tbl_ENT_ENTRETIENS.dateEN))) 
                      + (CASE WHEN DATENAME(dw, dbo.tbl_ENT_ENTRETIENS.dateEN) = 'Sunday' THEN 1 ELSE 0 END))) = 'Saturday' THEN 2 ELSE 0 END), 
                      DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, 
                      dbo.tbl_ENT_ENTRETIENS.dateEN)) AS dateNextEN
FROM         dbo.tbl_ENT_EQUIPEMENTS INNER JOIN
                      dbo.tbl_ENT_TYPES_ENTRETIENS ON dbo.tbl_ENT_EQUIPEMENTS._idTypeEQ = dbo.tbl_ENT_TYPES_ENTRETIENS._idTypeEQ LEFT OUTER JOIN
                      dbo.tbl_ENT_ENTRETIENS ON dbo.tbl_ENT_EQUIPEMENTS.idEQ = dbo.tbl_ENT_ENTRETIENS._idEQ

Open in new window

0
aboredmanAuthor Commented:
Here's commented query
SELECT     dbo.tbl_ENT_EQUIPEMENTS.idEQ, 
dbo.tbl_ENT_EQUIPEMENTS.nomEQ, 
dbo.tbl_ENT_TYPES_ENTRETIENS.idTypeEN,
dbo.tbl_ENT_ENTRETIENS.dateEN,
dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN, 
dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ,
 
 
-- add days to get next maintenance date
dateadd([day],  
-- @@@ number of days 
2*(DATEDIFF([week], dbo.tbl_ENT_ENTRETIENS.dateEN,DATEADD([day], (dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ), dbo.tbl_ENT_ENTRETIENS.dateEN)))
-- date lastEN + nbJourSuggere : saturday && nb semaine > 1 = +2 days
+(CASE WHEN DATENAME(dw,DATEADD([day],(dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ), dbo.tbl_ENT_ENTRETIENS.dateEN))= 'Saturday' AND (2*(DATEDIFF([week], dbo.tbl_ENT_ENTRETIENS.dateEN, DATEADD([day], (dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ), dbo.tbl_ENT_ENTRETIENS.dateEN)))) >1 THEN 2 ELSE 0 END)
-- Date lastEN : sunday  = +1 day
+(CASE WHEN DATENAME(dw, dbo.tbl_ENT_ENTRETIENS.dateEN) = 'Sunday' THEN 1 ELSE 0 END) 
-- date lastEN + jourSuggere + offsets : saturday = +2 days
+(CASE WHEN DATENAME(dw, (DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, dbo.tbl_ENT_ENTRETIENS.dateEN) +2*(DATEDIFF([week], dbo.tbl_ENT_ENTRETIENS.dateEN, DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, dbo.tbl_ENT_ENTRETIENS.dateEN)))+(CASE WHEN DATENAME(dw, dbo.tbl_ENT_ENTRETIENS.dateEN) = 'Sunday' THEN 1 ELSE 0 END))) = 'Saturday' THEN 2 ELSE 0 END),
-- @@@ end number of days
-- date nextEN 
DATEADD([day], dbo.tbl_ENT_TYPES_ENTRETIENS.nbHeuresEntreTypeEN / dbo.tbl_ENT_EQUIPEMENTS.utilisationQuotiEQ, dbo.tbl_ENT_ENTRETIENS.dateEN)
)  as dateNextEN
 
 
FROM         dbo.tbl_ENT_EQUIPEMENTS INNER JOIN
                      dbo.tbl_ENT_TYPES_ENTRETIENS ON dbo.tbl_ENT_EQUIPEMENTS._idTypeEQ = dbo.tbl_ENT_TYPES_ENTRETIENS._idTypeEQ LEFT OUTER JOIN
                      dbo.tbl_ENT_ENTRETIENS ON dbo.tbl_ENT_EQUIPEMENTS.idEQ = dbo.tbl_ENT_ENTRETIENS._idEQ

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
folderolCommented:
I'll take a look, I think I'm clear, I don't have time right now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.