Excluding weekend dates from calculated data pull

I have a business need to display some date specific data to a selcect group
of users 3 bussiness days before everyone else is allowed to see it.
THere are many fields I will be displaying across many tables. The date I am
using is ONLY in the primary table (meaning I don't have to include or
exclude data based on a date match from the other tables).
What I need to know is if there is a way in the SQL Query to not count the
weekend days as part of the 3 bussiness days. My query right now uses "WHERE
datafield < (GetDate + 5)". But this lets the users see data for the week's
Friday on the week's Monday. Not acceptable. If I only use "WHERE datafield <
(GetDate + 3) Then data to been displayed on This week's Tuesday can not be
seen on the previous Friday. Also Not Acceptable.
Any ideas?
Thanks.
Carrie E. Wells
Web Architect/Developer
Oh, and if Ihave this posted int eh wrong place tell me where to put it an I
move it.
On Wed, 25 May 2005 08:43:25 -0700, Wells wrote:
(snip)
>What I need to know is if there is a way in the SQL Query to not count the
>weekend days as part of the 3 bussiness days. My query right now uses "WHERE
>datafield < (GetDate + 5)". But this lets the users see data for the week's
>Friday on the week's Monday. Not acceptable. If I only use "WHERE datafield <
>(GetDate + 3) Then data to been displayed on This week's Tuesday can not be
>seen on the previous Friday. Also Not Acceptable.
Hi Carrie,
The simple, quick and dirty solution would be to use a CASE expression
to add either 3 or 5 to the current date, depending on the result of
DATEPART(day, GetDate()). But that would not take public holidays and
company holidays into account.
The best solution is to use a calendar table. You'll have to create it
once; after that, you can use it in this and many other situations. How
to make a calendar table, and many possible usees, is described at this
site: http://www.aspfaq.com/show.asp?id=2519

>Oh, and if Ihave this posted int eh wrong place tell me where to put it an I
>move it.
I don't think you can move posts on Usenet :-)
Anyway, this group is actually intended for MSEQ (Microsoft English
Query). In practice, nobody ever posts anything about MSEQ, but the
group does catch some stray questions about SQL Server queries.
A better place for this kind of questions is the programming group at
microsoft.public.sqlserver.programming. That group is frequented by many
more experts than this one.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Since the site is 24/7 then that should be ok. The whole reason for 3 days
was to work around the schedules of our foriegn customers, who of course
don't have the same holidays. This issue was about certian Key Customers
getting the data first but not before the company rules said they could.
Thanks for the solution. I never even thought about using a Case statement
and this has been driving me crazy for two weeks. :-)
Thanks Again,
Carrie Wells
Carrie E. Wells
Web Architect/Developer
"Hugo Kornelis" wrote:

> On Wed, 25 May 2005 08:43:25 -0700, Wells wrote:
> (snip)
> Hi Carrie,
> The simple, quick and dirty solution would be to use a CASE expression
> to add either 3 or 5 to the current date, depending on the result of
> DATEPART(day, GetDate()). But that would not take public holidays and
> company holidays into account.
> The best solution is to use a calendar table. You'll have to create it
> once; after that, you can use it in this and many other situations. How
> to make a calendar table, and many possible usees, is described at this
> site: http://www.aspfaq.com/show.asp?id=2519
>
> I don't think you can move posts on Usenet :-)
> Anyway, this group is actually intended for MSEQ (Microsoft English
> Query). In practice, nobody ever posts anything about MSEQ, but the
> group does catch some stray questions about SQL Server queries.
> A better place for this kind of questions is the programming group at
> microsoft.public.sqlserver.programming. That group is frequented by many
> more experts than this one.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>