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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 QUERY understanding

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 datediff
http://www.w3schools.com/sql/func_datediff.asp

I 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 DATETIME
DECLARE @EndDate DATETIME
SET @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 sundays
The 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.
Go to Top of Page

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 datediff
http://www.w3schools.com/sql/func_datediff.asp

I 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 DATETIME
DECLARE @EndDate DATETIME
SET @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 sidenote
the 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 here

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 datediff
http://www.w3schools.com/sql/func_datediff.asp

I 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 DATETIME
DECLARE @EndDate DATETIME
SET @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 sidenote
the 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 here

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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.
Go to Top of Page

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 datediff
http://www.w3schools.com/sql/func_datediff.asp

I 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 DATETIME
DECLARE @EndDate DATETIME
SET @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 sidenote
the 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 here

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 DATETIME
DECLARE @EndDate DATETIME
SET @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 datediff
http://www.w3schools.com/sql/func_datediff.asp

I 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 DATETIME
DECLARE @EndDate DATETIME
SET @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 sidenote
the 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 here

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

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 me

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 DATETIME
DECLARE @EndDate DATETIME
SET @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]
Go to Top of Page

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 DATETIME
DECLARE @EndDate DATETIME
SET @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 same
see my last posted code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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"
Go to Top of Page

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 that
What I told was as per values passed I got the correct answer by using >4 whereas other suggestions gave 1 day more

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 that
What I told was as per values passed I got the correct answer by using >4 whereas other suggestions gave 1 day more

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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/
Go to Top of Page
   

- Advertisement -