SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 QUERY understanding
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

Posted - 07/29/2013 :  11:15:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 07/29/2013 :  12:49:47  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/30/2013 :  02:15:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 07/30/2013 :  08:27:31  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/31/2013 :  01:24:35  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/31/2013 :  10:06:27  Show Profile  Reply with Quote
The two queries are not equivalent run the following to see the difference:

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)



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

India
52309 Posts

Posted - 07/31/2013 :  10:30:58  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/31/2013 :  10:37:32  Show Profile  Reply with Quote
GETDATE() in all of those instances need to be replaced with @EndDate;


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)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/31/2013 :  10:42:01  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

GETDATE() in all of those instances need to be replaced with @EndDate;


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)




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

547 Posts

Posted - 07/31/2013 :  10:48:53  Show Profile  Reply with Quote
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"

Edited by - MuMu88 on 07/31/2013 10:49:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/01/2013 :  02:28:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 08/01/2013 :  08:24:49  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000