Re: Excluding weekend dates from calculated data pull

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)
.

Relevant Pages

  • Re: Clarification of BytesSent vs BytesSentDelta in ISA 2004 Firewall log
    ... Would it be possible to please post the SQL query you used to get the ... > In ISA Server Management, I can get matching results between the Bytes ... > Remember that the data in the logs is per connection, ...
    (microsoft.public.isaserver)
  • Re: Many To Many Relationships
    ... INNER JOIN CATEGORY AS C ... An SQL query typically returns a table. ... Requires RVAs and an aggregate union. ...
    (comp.databases.theory)
  • Re: faster search engine for fulltext search
    ... Here is the SQL query I have used in a table with less than 200.000 records, ... FULLTEXT KEY `full` ... againstorder by date desc, rank desc limit 0,20; ...
    (perl.beginners)
  • Re: Compare Records & Move to new table in VBA
    ... If i write an sql query, can the field be a variable pulled from somewhere ... So i need to know how to compare in VB, because the criteria in a query is ... If it's called tblClient, Access will alias the 2nd one as tblClient_1. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Incremental search with numbers
    ... If you mean that you don't know how to enter that in the graphical query designer, the easiest way is to use the View menu and change to SQL view, where you can directly edit the sql statement. ... > my program places it in a SQL query which uses a LIKE 'A*' to extract> all ... >>> characters keyed in on my form. ...
    (microsoft.public.access.formscoding)