sql 
Author Message
 sql
I am trying to write a SQL statement that will look at 2 dates and determine
the number of work days there are between them. I need to issue the SQL
against many rows. If I can at least exclude the week-ends that would be
great. The holidays, a bonus.... Does anyone have an answer?


Fri, 10 Jul 1998 03:00:00 GMT
 sql

Quote:
> >I am trying to write a SQL statement that will look at 2 dates and determine
> >the number of work days there are between them. I need to issue the SQL
> >against many rows. If I can at least exclude the week-ends that would be
> >great. The holidays, a bonus.... Does anyone have an answer?

How 'bout the following - basically populate a table once a year - or
whatever; then get a count of valid dates
JB

Start SQL
=========================
set nocount on

/*create table tmp (days smalldatetime)*/
/*create unique clustered index tmp_idx on tmp(days)*/

delete from tmp




/* accoutn for leap year */

begin



/* exclude Sat Sun , optional join on holiday table done here*/



end

/*  do some error checking to ensure check dates are in right
order , values are valid blah, blah, ... */

/*   your dates would be a param(s)   */

select
   count(days)
from
   tmp
where
   days >= '05-feb-1996' and
   days <= '09-mar-1996'

===========

End SQL

Results in :

-----------
          25

Quote:


> >I am trying to write a SQL statement that will look at 2 dates and determine
> >the number of work days there are between them. I need to issue the SQL
...

> Could be an interesting contest : who can provide the most elegant
> and efficient solution

> Isaac



Sun, 12 Jul 1998 03:00:00 GMT
 sql

Quote:

> I am trying to write a SQL statement that will look at 2 dates and determine
> the number of work days there are between them. I need to issue the SQL
> against many rows. If I can at least exclude the week-ends that would be
> great. The holidays, a bonus.... Does anyone have an answer?

I have put together this small SQL statement for you, to calculate the number of
workdays, with workdays defined as being monday to friday, within a given datespan.
The given datespan is used inclusive.

I do not take care of other non-working days than saturday and sunday,
since the holiday definition then has to be implemented
within the server language/country setting, and as it is today with SQL Server,
I do not think it is.

The sample below uses the dates given in variables. When used in a query against
a table, you just substitute the variables with the table field names.

<INCLUDETEXT>

/*
** Set the first day of the week to monday, defaults to sunday for us_english.
** Valid for this session.
*/
set datefirst 1

/*
** Set up the date span.
*/


/*
** Calculate the number of workdays
**
**      a) I take the number of midnights between day 1 and 2
**      b) I subtract 2 (i.e. sat and sun) * the number of sundays between day 1 and 2
**      c) I then adjusts for day 1 or 2 being a saturday
**      d) At last I adjust for day 1 being a working day
*/
select 'Work days' =






go

</INCLUDETEXT>

Good luck!

--

<!-- Jo Arne Lervik -->          ////
                                (. .)
*----------------------------o00-(_)-00o-----------------------------*

Micro Design AS              Phone    : 0047 7382 6500 / 7350 2467
P.O. Box 3974 Leangen        Fax      : 0047 7382 6501
N-7002 Trondheim, Norway     Cellular : 0047 9482 1167

*-------- http://ourworld.compuserve.com/homepages/masterdat --------*



Sun, 12 Jul 1998 03:00:00 GMT
 sql

Quote:

>I am trying to write a SQL statement that will look at 2 dates and determine
>the number of work days there are between them. I need to issue the SQL
>against many rows. If I can at least exclude the week-ends that would be
>great. The holidays, a bonus.... Does anyone have an answer?

create table Calendar (day datetime, workDayFlg int)
insert Calendar values ("Jan 1 1996",0) /* A holiday */
insert Calendar values ("Jan 2 1996",1)
insert Calendar values ("Jan 3 1996",1)
insert Calendar values ("Jan 4 1996",1)
insert Calendar values ("Jan 5 1996",1)
insert Calendar values ("Jan 6 1996",0) /* Saturday */
insert Calendar values ("Jan 7 1996",0) /* Sunday */
insert Calendar values ("Jan 8 1996",1)
..................................





select sum(workdayFlg) from Calendar


Could be an interesting contest : who can provide the most elegant
and efficient solution

Isaac



Sun, 12 Jul 1998 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. MS Access 97 SQL to MS SQL SQL SQL SQL

2. SQL-92, Migrate PL/SQL at ORACLE into PL/SQL at SQL Server 7.0

3. SQL 6.5 vs. SQL 7.0 or SQL 2000

4. Translating FoxPro SQL into SQL Server SQL

5. SQL-DMO Problem in T-SQL in SQL Server 7 with SP3

6. ERROR: Must Update SQL EM and SQL-DMO to SQL Svr 2000

7. Connecting to Sql Server from asp after upgrade from ms sql 7 to ms sql 2000

8. US-TX-DFW SQL SERVER DBA - SQL SERVER DATABASE ADMINISTRATOR - SQL DBA

9. from oracle (pl/sql) to sql server (t-sql)

10. Differences between SQL 7 And SQL 2K T-SQL

11. SQL-DMO: Inconsistency between SQL 7 and SQL 2000

12. Transforming Access 2002 SQL to SQL-Server 2000 SQL


 
Powered by phpBB® Forum Software