1

I currently have this query that I would like to have the DATEDIFF to just include "working days" So It'll only include Monday - Friday (the hours of operation doesn't matter)

Select 
a.SiteID,
DATEDIFF(d,a.Mino,a.Maxo)
FROM
(Select 
sn.SiteID,
MAX(sn.CreationDate) Maxo,
MIN(sn.CreationDate) Mino
From SiteNotifications sn
Group By sn.SiteID) a

So far it's just doing ALL days - which is to be expected but is there anyway I can capture just Monday - Friday?

BTW for further clarification here is a sample of what the query outputs (note that each SiteID also has it's own Min and Max times - they aren't all the same date range). 

SiteID | Days
-------------
123456 | 0
-------------
789012 | 0
------------
654321 | 31

So the 0's are min and max dates that are the same (or the same date I should say) so that's fine. The 31 days - since I know thats about a month and the month has about 8 weekdays - should really be '23'

StayPuft
  • 125
  • 1
  • 1
  • 13
  • This is super useful, but I'm unsure how I would combine/use the info in the link with my query. Since there are various rows of various dates rather than one solid date. – StayPuft Jul 14 '14 at 13:38

1 Answers1

0

Try this

Select 
a.SiteID,
DATEDIFF(d,a.Mino,a.Maxo)-2*Datediff(wk,a.mino,a.maxo)
FROM
(Select 
sn.SiteID,
MAX(sn.CreationDate) Maxo,
MIN(sn.CreationDate) Mino
From SiteNotifications sn
Group By sn.SiteID) a

This is jst for u r basic idea u have to write case statement to see that start and end dates do not fall in weekends.

Azar
  • 1,852
  • 15
  • 17
  • Can i know why this is not working.. – Azar Jul 14 '14 at 13:41
  • Actually this works fine for me - thanks! Though I'm not being particularly accurate either. Which is fine for my needs. Example, 16 days - which is about 2 weeks. Which include about 4 days of weekends. Is now '12' days. I' currently not including things such as if the start date is on a weekend or the end date is on a weekend etc. – StayPuft Jul 14 '14 at 13:50
  • BTW I didn't down vote it >.> – StayPuft Jul 14 '14 at 13:50