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
 Development Tools
 Reporting Services Development
 datediff in expression excluding weekends

Author  Topic 

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 09:57:06
Hello, I have the following expression:

=Iif((Fields!DELIVERYDATE.Value > Fields!SHIPPINGDATEREQUESTED.Value),
datediff("w",Fields!DELIVERYDATE.Value,Fields!SHIPPINGDATEREQUESTED.Value), "NotLate")

How do I properly subtract these two dates to ignore weekends? I've researched online and many solutions were to write a UDF to calculate the weekends and call within the query first but there has to be an easier way using an expression...?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:11:26
nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:26:12
quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Can you lead me to the best UDF I can use. Also, how to call the UDF within my reports query. I can post my query if need be.

Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:32:20
quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Can you lead me to the best UDF I can use. Also, how to call the UDF within my reports query. I can post my query if need be.

Thank you.


see one below
http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

use @weekdays = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:33:00
quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:34:45
quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?


you mean in query behind?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:40:47
quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?


you mean in query behind?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Yes.
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:45:08
quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?


you mean in query behind?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Well, either in the query behind or within the expression, either or.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:48:51
try something like

SELECT DATEDIFF(dd,StartDate,EndDate) - (2 * DATEDIFF(wk,StartDate,EndDate)) as Weekdays FROM Table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 11:01:25
Wow, worked like a charm!!! So how does this logic go? If I am following correctly, you get an integer number of days using DATEDIFF(dd,StartDate,EndDate) and then you get an integer number of weeks using DATEDIFF(wk,StartDate,EndDate), multiply by 2 and subtract the two integers... I guess I don't get this part: (2 * DATEDIFF(wk,StartDate,EndDate))

None the less, it works great, thank you again visakh!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 11:05:40
you've 2 weekend days per week

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 11:09:28
I see, you da man.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:44:51
cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -