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: Comparing database dates: SELECT FROM WHERE Date(today) < MYDATECOL ?
    ... How do I specify this in a SQL query from Java? ... You can in many databases use '' around the date: ... By using a PreparedStatement, you can make your queries less db specific, ...
    (comp.lang.java.databases)
  • Re: Please help!
    ... you group all the text fields and sum all the numeric fields together). ... I copy all the numbers from "Total Accum Time" for both with or without ... The Control Source of the report is a SQL query, ...
    (microsoft.public.access.queries)