• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4035
  • Last Modified:

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
0
aboredman
Asked:
aboredman
  • 3
  • 2
1 Solution
 
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
 
folderolCommented:
I'll take a look, I think I'm clear, I don't have time right now.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now