Re: Prevent Spanning Dates

From: jez123456 (jez123456_at_discussions.microsoft.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 06:45:01 -0700

Sorry Roji, still haveing problems. Here is the DDL with some test data.

CREATE TABLE [vacation] (
        [id] [int] IDENTITY (1, 1) NOT NULL ,
        [name] [char] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
        [startdate] [smalldatetime] NULL ,
        [enddate] [smalldatetime] NULL ,
        CONSTRAINT [PK_vacation] PRIMARY KEY CLUSTERED
        (
                [id]
        ) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [vacation]([name], [startdate], [enddate])
VALUES('joe bloggs', '05 may 2004', '18 may 2004')

INSERT INTO [vacation]([name], [startdate], [enddate])
VALUES('joe bloggs', '02 june 2004', '13 june 2004')

INSERT INTO [vacation]([name], [startdate], [enddate])
VALUES('joe bloggs', '30 march 2004', '07 april 2004')

INSERT INTO [vacation]([name], [startdate], [enddate])
VALUES('pete smith', '15 march 2004', '19 march 2004')

INSERT INTO [vacation]([name], [startdate], [enddate])
VALUES('pete smith', '05 january 2004', '14 january 2004')

Thanks

"Roji. P. Thomas" wrote:

> If you are putting it inside a trigger, you have to replace the variables
> with the columns in the "inserted" table.
>
> If you have further queries, plz post DDL
>
>
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
>
> "jez123456" <jez123456@discussions.microsoft.com> wrote in message
> news:ECCE1901-D617-4616-99FC-91818438B10F@microsoft.com...
> > Hi Roji. I tried putting your code in the trigger but I got an Error 137:
> > Must declare the variable '@name'.
> > Not sure what this means.
> >
> > "Roji. P. Thomas" wrote:
> >
> > > IF EXISTS(SELECT 1 FROM YourTable WHERE name = @name
> > > AND ((@StartDate BETWEEN StartDate AND EndDate) OR
> (@EndDate
> > > BETWEEN StartDate AND EndDate))
> > > PRINT 'EXISTS'
> > > ELSE
> > > PRINT 'NOT EXISTS'
> > >
> > >
> > >
> > > --
> > > Roji. P. Thomas
> > > Net Asset Management
> > > https://www.netassetmanagement.com
> > >
> > >
> > > "jez123456" <jez123456@discussions.microsoft.com> wrote in message
> > > news:5380EE95-F6BB-4517-9172-FF92BE8D8248@microsoft.com...
> > > > Hi, I have a table which stores vacation data and includes fields
> > > StartDate
> > > > and EndDate.
> > > >
> > > > I'm not sure if this is possible, but I need to check that the
> StartDate
> > > and
> > > > EndDate are not included in an existing record's date span.
> > > >
> > > > Example
> > > >
> > > > Name StartDate EndDate
> > > > Joe Bloggs 05 May 2004 18 May 2004
> > > >
> > > >
> > > > For the above record I need to prevent Joe Bloggs entering any of the
> > > > following date combinations:-
> > > >
> > > >
> > > > Name StartDate EndDate
> > > > Joe Bloggs 07 May 2004 10 May 2004
> > > > Joe Bloggs 01 May 2004 10 May 2004
> > > > Joe Bloggs 10 May 2004 21 May 2004
> > > >
> > >
> > >
> > >
>
>
>

Relevant Pages

  • Re: Dynamic table name in query
    ... "Roji. ... P. Thomas" wrote in message ... >> Dynamic SQL, you could just explain your position or at least write the ... >>> You are trying to write a procedure without any cohesion at all. ...
    (microsoft.public.sqlserver.programming)
  • Re: searching with varying parameters
    ... "Roji. ... P. Thomas" wrote in message ... > Net Asset Management ... >> body, Search by attachment name, search if message has attachments). ...
    (microsoft.public.sqlserver.programming)
  • Re: Output parameters
    ... > Set CmdParams = Nothing ... > "Roji. ... P. Thomas" wrote in message ... >> Net Asset Management ...
    (microsoft.public.sqlserver.programming)
  • Re: DISTINCT and LEFT JOIN problem
    ... > "Roji. ... P. Thomas" wrote in message ... >>>>> SELECT DISTINCT resResourceID, resDateIn, resDateOut, empEmployeeID, ...
    (microsoft.public.sqlserver.programming)
  • Re: Structure Help
    ... "Drew" wrote in message ... > "Roji. ... P. Thomas" wrote in message ... CharacteristicsInPeople - Again a list ...
    (microsoft.public.sqlserver.programming)