Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Maverick_
Posting Yak Master
107 Posts |
Posted - 2014-01-07 : 06:01:32
|
Hi guys,I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:ce.enquiry_time represents when the enquiry was logged (DATEDIFF(dd, ce.enquiry_time, getdate()) + 1) -(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2) -(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together. Any kind soul care to help break it down please |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-07 : 06:31:42
|
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1) -> this gives total no of days between current date and ce.enquiry_time value(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2) -> this is to subtract the number of weekend days that happens between current date and ce.enquiry_time. there are 2 days per week so (no of weeks ) * 2 will give totals weekend days excluding starting and ending dates(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END) -> checking if starting date is weekend day to subtract it too from total(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END) -> checking if ending date is weekend day to subtract it too from total(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1)) -> this will subtract any other holidays excluding weekends by looking at a table storing holiday details that falls between start and end dates.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|