asked on

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

folderol

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.

aboredman

ASKER

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.

aboredman

ASKER

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

ASKER CERTIFIED SOLUTION

aboredman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

folderol

I'll take a look, I think I'm clear, I don't have time right now.