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 |
jrobin747
Starting Member
48 Posts |
Posted - 2013-07-29 : 11:15:00
|
I understand the results that I get but I don't know what each line is doing in the select area. I sorta understand datediff is getting dates between the start of the month and current date. what is the purpose +1 and *2 I don't see that in the explaination of datediffhttp://www.w3schools.com/sql/func_datediff.aspI looked up datename at w3schools.com but didnt find anything. I'm learning how to use sql server mgt 2005. Also I don't understand CASE WHEN DATENAME. Because of the project I'm doing I know that we are saying the weeks starts on Sunday and ends on Saturday. We are trying to see how many work days have passed in the month. My query comes out correctly. I didn't write it though. Just unclear on some of it. I have to write a query that does the same but works every month. Can this be done without writing a seperate query for each month? DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '07/01/2013'SET @EndDate = '07/31/2013'SELECT (DATEDIFF(dd, @StartDate, GETDATE()) + 1) -(DATEDIFF(wk, @StartDate, GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 12:49:47
|
It is trying to calculate the number of weekdays (i.e., Monday-Friday) bewteen the startdate and enddate as follows:The first thing: (DATEDIFF(dd, @StartDate, GETDATE()) + 1) calculates the number of days including saturdays and sundaysThe second thing: (DATEDIFF(wk, @StartDate, GETDATE()) * 2) subtracts twice the number of week boundary crossings. The rationale being that each week has two days that you want to subtract out (Saturday and Sunday)The third and fourth pieces are trying to adjust for the fact that that calculation would not be quite right if the start date is on a Sunday or EndDate is on a Saturday.Do some experiments with various starting dates, print out each piece separately, and you will see what it is trying to do. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-30 : 02:15:30
|
quote: Originally posted by jrobin747 I understand the results that I get but I don't know what each line is doing in the select area. I sorta understand datediff is getting dates between the start of the month and current date. what is the purpose +1 and *2 I don't see that in the explaination of datediffhttp://www.w3schools.com/sql/func_datediff.aspI looked up datename at w3schools.com but didnt find anything. I'm learning how to use sql server mgt 2005. Also I don't understand CASE WHEN DATENAME. Because of the project I'm doing I know that we are saying the weeks starts on Sunday and ends on Saturday. We are trying to see how many work days have passed in the month. My query comes out correctly. I didn't write it though. Just unclear on some of it. I have to write a query that does the same but works every month. Can this be done without writing a seperate query for each month? DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '07/01/2013'SET @EndDate = '07/31/2013'SELECT (DATEDIFF(dd, @StartDate, GETDATE()) + 1) -(DATEDIFF(wk, @StartDate, GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
On a sidenotethe last two conditions can be rewritten independent of server settings as below..-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END) reason is herehttp://visakhm.blogspot.in/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-30 : 08:27:31
|
quote: Originally posted by visakh16
quote: Originally posted by jrobin747 I understand the results that I get but I don't know what each line is doing in the select area. I sorta understand datediff is getting dates between the start of the month and current date. what is the purpose +1 and *2 I don't see that in the explaination of datediffhttp://www.w3schools.com/sql/func_datediff.aspI looked up datename at w3schools.com but didnt find anything. I'm learning how to use sql server mgt 2005. Also I don't understand CASE WHEN DATENAME. Because of the project I'm doing I know that we are saying the weeks starts on Sunday and ends on Saturday. We are trying to see how many work days have passed in the month. My query comes out correctly. I didn't write it though. Just unclear on some of it. I have to write a query that does the same but works every month. Can this be done without writing a seperate query for each month? DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '07/01/2013'SET @EndDate = '07/31/2013'SELECT (DATEDIFF(dd, @StartDate, GETDATE()) + 1) -(DATEDIFF(wk, @StartDate, GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
On a sidenotethe last two conditions can be rewritten independent of server settings as below..-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END) reason is herehttp://visakhm.blogspot.in/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Correcting a logic error in Visakh's post:SELECT(DATEDIFF(dd, @StartDate, GETDATE()) + 1)-(DATEDIFF(wk, @StartDate, GETDATE()) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END) Even if you think locale/language is not an issue for you, you may be unpleasantly surprised, and there is no cost to doing it this way, so I would strongly recommend this method. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-31 : 01:24:35
|
quote: Originally posted by James K
quote: Originally posted by visakh16
quote: Originally posted by jrobin747 I understand the results that I get but I don't know what each line is doing in the select area. I sorta understand datediff is getting dates between the start of the month and current date. what is the purpose +1 and *2 I don't see that in the explaination of datediffhttp://www.w3schools.com/sql/func_datediff.aspI looked up datename at w3schools.com but didnt find anything. I'm learning how to use sql server mgt 2005. Also I don't understand CASE WHEN DATENAME. Because of the project I'm doing I know that we are saying the weeks starts on Sunday and ends on Saturday. We are trying to see how many work days have passed in the month. My query comes out correctly. I didn't write it though. Just unclear on some of it. I have to write a query that does the same but works every month. Can this be done without writing a seperate query for each month? DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '07/01/2013'SET @EndDate = '07/31/2013'SELECT (DATEDIFF(dd, @StartDate, GETDATE()) + 1) -(DATEDIFF(wk, @StartDate, GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
On a sidenotethe last two conditions can be rewritten independent of server settings as below..-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END) reason is herehttp://visakhm.blogspot.in/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Correcting a logic error in Visakh's post:SELECT(DATEDIFF(dd, @StartDate, GETDATE()) + 1)-(DATEDIFF(wk, @StartDate, GETDATE()) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END) Even if you think locale/language is not an issue for you, you may be unpleasantly surprised, and there is no cost to doing it this way, so I would strongly recommend this method.
Sorry I didnt understand what was issue in the logic. You've just replaced >4 condition with = 5 and =6 which looks equivalent to me.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-31 : 10:06:27
|
The two queries are not equivalent run the following to see the difference:[CODE]DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '06/01/2013'SET @EndDate = '06/30/2013'SELECT (DATEDIFF(dd, @StartDate, GETDATE()) + 1) -(DATEDIFF(wk, @StartDate, GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, GETDATE()) + 1)-(DATEDIFF(wk, @StartDate, GETDATE()) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, GETDATE()) + 1)-(DATEDIFF(wk, @StartDate, GETDATE()) * 2)-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END)[/CODE]quote: Originally posted by visakh16
quote: Originally posted by James K
quote: Originally posted by visakh16
quote: Originally posted by jrobin747 I understand the results that I get but I don't know what each line is doing in the select area. I sorta understand datediff is getting dates between the start of the month and current date. what is the purpose +1 and *2 I don't see that in the explaination of datediffhttp://www.w3schools.com/sql/func_datediff.aspI looked up datename at w3schools.com but didnt find anything. I'm learning how to use sql server mgt 2005. Also I don't understand CASE WHEN DATENAME. Because of the project I'm doing I know that we are saying the weeks starts on Sunday and ends on Saturday. We are trying to see how many work days have passed in the month. My query comes out correctly. I didn't write it though. Just unclear on some of it. I have to write a query that does the same but works every month. Can this be done without writing a seperate query for each month? DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '07/01/2013'SET @EndDate = '07/31/2013'SELECT (DATEDIFF(dd, @StartDate, GETDATE()) + 1) -(DATEDIFF(wk, @StartDate, GETDATE()) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
On a sidenotethe last two conditions can be rewritten independent of server settings as below..-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END) reason is herehttp://visakhm.blogspot.in/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Correcting a logic error in Visakh's post:SELECT(DATEDIFF(dd, @StartDate, GETDATE()) + 1)-(DATEDIFF(wk, @StartDate, GETDATE()) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END) Even if you think locale/language is not an issue for you, you may be unpleasantly surprised, and there is no cost to doing it this way, so I would strongly recommend this method.
Sorry I didnt understand what was issue in the logic. You've just replaced >4 condition with = 5 and =6 which looks equivalent to me.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-31 : 10:30:58
|
Anyways I passed GETDATE() as EndDate to get working days elapsed from start date till today and got result as 42 which is correct for me. the other two suggestions gave 43 for meDECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '06/01/2013'SET @EndDate = GETDATE()--'06/30/2013'SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate,@EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END)output-----------------------------------43(1 row(s) affected)-----------43(1 row(s) affected)-----------42(1 row(s) affected) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-31 : 10:37:32
|
GETDATE() in all of those instances need to be replaced with @EndDate;[CODE]DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '06/01/2013'SET @EndDate = '06/30/2013'SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate,@EndDate) * 2)-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END)[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-31 : 10:42:01
|
quote: Originally posted by MuMu88 GETDATE() in all of those instances need to be replaced with @EndDate;[CODE]DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = '06/01/2013'SET @EndDate = '06/30/2013'SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)- (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END)- (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END)SELECT(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate,@EndDate) * 2)-(CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 > 4 THEN 1 ELSE 0 END) -(CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 > 4 THEN 1 ELSE 0 END)[/CODE]
I did the samesee my last posted code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-31 : 10:48:53
|
The @EndDate is not the same in your post.Try the query I posted you will see the difference in the queries." > 4 " is not the same as " = 5" and " = 6" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-01 : 02:28:52
|
quote: Originally posted by MuMu88 The @EndDate is not the same in your post.Try the query I posted you will see the difference in the queries." > 4 " is not the same as " = 5" and " = 6"
Yep...I got thatWhat I told was as per values passed I got the correct answer by using >4 whereas other suggestions gave 1 day more------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-01 : 08:24:49
|
quote: Originally posted by visakh16
quote: Originally posted by MuMu88 The @EndDate is not the same in your post.Try the query I posted you will see the difference in the queries." > 4 " is not the same as " = 5" and " = 6"
Yep...I got thatWhat I told was as per values passed I got the correct answer by using >4 whereas other suggestions gave 1 day more------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Not sure what you mean here, but in the queries that Mumu posted at 07/31/2013 : 10:37:32, this is the behavior I see:1. the first query (OP's original query) always calculates the number of days correctly (assuming the correct server settingss)2. the second query (query that I posted and corrected by Mumu) always calculates the number of days correctly (regardless of server settings)3. the third query (which you posted) calculates the number of days incorrectly in some cases (certain combinations of startdate being on a Sunday or EndDate being on a Saturday)http://myshallowsqlblog.wordpress.com/weekdays-only-please/ |
|
|
|
|
|
|
|