DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Re: Query Compare for DaveSatz

  1. #1
    Ronald Guest

    Re: Query Compare for DaveSatz


    Dave thanks for the help you offered but I can't find the info you sent me
    to so if you can help me a little I would be very thankfull

    Ron

    "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
    >search for "overlap" in the subject of messages on
    >microsoft.public.sqlserver.programming on msnews.microsoft.com
    >
    >HTH,
    >Dave
    >
    >"Ronald" <rjones@unitech1.com> wrote in message
    >news:3c028dbe$1@147.208.176.211...
    >>
    >> I need some help with my query, so if anyone can help me thanks in

    >advance.
    >> Here is my problem I have a table that have two date fields (ex. Start

    >date
    >> 01-01-2001 and End Date 12-31-2001) with multiple records I need to write
    >> a query that would compare all the records in the table and see if the

    >dates
    >> overlap anyone in the table

    >
    >



  2. #2
    DaveSatz Guest

    Re: Query Compare for DaveSatz

    here are the posts:

    Good catch, Isaac! The last thread had assumed no overlaps, but then I
    should know better than to assume that preconditions will really be met
    anyway.

    There are a couple of GO's missing (or run the example statement by
    statement), and the two copies of Northwind..Orders need to be aliased.
    Don't know how all that slipped by.

    Anyway, the cursor version is much better.

    Steve

    "Isaac Blank" <izblank@yahoo.com> wrote in message
    news:u0JE61$WBHA.1972@tkmsftngp05...
    > Shouldn't it be SELECT DISTINCT (to handle overlaps)?
    >
    > "Steve Kass" <skass@drew.edu> wrote in message
    > news:3BD5C696.B0ACE5F6@drew.edu...
    > >
    > > SELECT E.ssn, X.D
    > > INTO EnrollmentDaily
    > > FROM enrollment E
    > > JOIN Seq_0_5000 X
    > > ON X.D BETWEEN E.startdate AND E.enddate
    > > CREATE UNIQUE CLUSTERED INDEX EnrollmentDaily_ssn_D ON
    > > EnrollmentDaily(ssn,
    > > D)

    >
    >
    >



    "Ronald" <rjones@unitech1.com> wrote in message
    news:3c038e72$1@147.208.176.211...
    >
    > Dave thanks for the help you offered but I can't find the info you sent me
    > to so if you can help me a little I would be very thankfull
    >
    > Ron
    >
    > "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
    > >search for "overlap" in the subject of messages on
    > >microsoft.public.sqlserver.programming on msnews.microsoft.com
    > >
    > >HTH,
    > >Dave
    > >
    > >"Ronald" <rjones@unitech1.com> wrote in message
    > >news:3c028dbe$1@147.208.176.211...
    > >>
    > >> I need some help with my query, so if anyone can help me thanks in

    > >advance.
    > >> Here is my problem I have a table that have two date fields (ex. Start

    > >date
    > >> 01-01-2001 and End Date 12-31-2001) with multiple records I need to

    write
    > >> a query that would compare all the records in the table and see if the

    > >dates
    > >> overlap anyone in the table

    > >
    > >

    > I should have read the thread. I just remembered that I also posted a

    cursor solution - while the question was how to do this without cursors,
    in this case, I think cursors are the best approach, and much faster
    than any query:

    SELECT TOP 0 * INTO Results FROM enrollment

    DECLARE C CURSOR FAST_FORWARD FOR
    SELECT ssn, startdate, enddate, policy
    FROM enrollment

    OPEN C
    DECLARE @ssn char(9)
    DECLARE @sd datetime
    DECLARE @ed datetime
    DECLARE @policy int

    DECLARE @rssn char(9)
    DECLARE @rsd datetime
    DECLARE @red datetime
    DECLARE @rpolicy int

    FETCH NEXT FROM C INTO @ssn, @sd, @ed, @policy
    SET @rssn = @ssn
    SET @rsd = @sd
    SET @red = @ed
    SET @rpolicy = @policy

    WHILE @@FETCH_STATUS = 0 BEGIN
    IF @ssn <> @rssn OR @sd > @red + 1 BEGIN
    INSERT INTO Results SELECT @rssn, @rsd, @red, @rpolicy
    SET @rssn = @ssn
    SET @rsd = @sd
    SET @red = @ed
    SET @rpolicy = @policy
    END ELSE
    SET @red = @ed
    FETCH NEXT FROM C INTO @ssn, @sd, @ed, @policy
    END
    CLOSE C
    DEALLOCATE C

    SELECT * FROM results

    Steve Kass
    Drew University

    David Browne wrote:
    >
    > /*
    > try this
    > */
    >
    > CREATE VIEW StartDates
    > as
    > SELECT * FROM T
    > WHERE NOT EXISTS (SELECT * FROM T AS TSUB
    > WHERE TSUB.Person = T.Person
    > AND TSUB.PK <> T.PK
    > AND TSUB.END_DATE <= T.StartDate
    > AND TSUB.End_Date > DATEADD(DAY,-1,T.Start_Date))
    > GO
    >
    > CREATE VIEW EndDates
    > as
    > SELECT * FROM T
    > WHERE NOT EXISTS (SELECT * FROM T AS TSUB
    > WHERE TSUB.Person = T.Person
    > AND TSUB.PK <> T.PK
    > AND TSUB.START_DATE >= T.EndDate
    > AND TSUB.START_Date < DATEADD(DAY,1,T.End_Date))
    > SELECT * From StartDates
    >
    > GO
    >
    > SELECT S.Person, S.BeginDate, E.EndDate FROM
    > StartDates S
    > JOIN EndDates E ON
    > S.Person = E.Person
    > AND E.End_Date = (SELECT MIN(End_Date)
    > FROM EndDates
    > WHERE Person = E.Person
    > AND End_Date > S.Start_Date)
    >
    > /*
    > no guarantees on performance, though
    >
    > David
    > */
    >
    > "Dave Claerhout" <dave.claerhout@allianceatlantis.com> wrote in message
    > news:RLfB7.45407$Z2.685682@nnrp1.uunet.ca...
    > Hi. I am having a problem attempting to create a query which will

    parse
    > out a series of dates and return the information which occurs on

    continuous
    > timelines. The source data looks like this:
    >
    > PK Person Start_Date End_Date
    > 1 Dave July 5, 2000 July 19, 2000
    > 2 Dave July 20, 2000 July 25, 2000
    > 3 Dave July 18, 2000 July 30, 2000
    > 4 Dave Feb 2, 2000 Feb 14, 2000
    > 5 Eduard Feb 2, 2000 Feb 9, 2000
    > 6 Eduard Feb 10, 2000 Feb 17, 2000
    > 7 Eduard Feb 13, 2000 Feb 22, 2000
    >
    > What I would like to get out of this is the PK fields that bump up against
    > each other or overlap. It doesn't matter what the dates are that come
    > back... but ultimately the PK fields should be grouped the following way:
    >
    > PK Person_ID Start_Date End_Date
    > 1 Dave July 5, 2000 July 30, 2000
    > 2 Dave July 5, 2000 July 30, 2000
    > 3 Dave July 5, 2000 July 30, 2000
    >
    > 4 Dave Feb 2, 2000 Feb 14, 2000
    >
    > 5 Eduard Feb 2, 2000 Feb 22, 2000
    > 6 Eduard Feb 2, 2000 Feb 22, 2000
    > 7 Eduard Feb 2, 2000 Feb 22, 2000
    >
    > The reduced results could look like this: (PK is not actually necessary)
    > Dave July 5, 2000 July 30, 2000
    > Dave Feb 2, 2000 Feb 14, 2000
    > Eduard Feb 2, 2000 Feb 22, 2000
    >
    > As you can see, there is sometimes overlap, there is sometimes no

    overlap
    > but if there is less than one day between Start_Date and End_Date it

    should
    > be treated as continuous, and there should be no check for date overlap if
    > the field "Person" changes.
    >
    > Would anyone know how to do this without resorting to a cursor?
    >
    > </Dave>
    >
    > DDL:
    >
    > CREATE TABLE [Timesheet] (
    > [PK] [int] NOT NULL ,
    > [Person] [varchar] (50) NOT NULL ,
    > [Start_Date] [datetime] NOT NULL ,
    > [End_Date] [datetime] NOT NULL
    > )
    >
    > SET NOCOUNT ON
    >
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 1, 'Dave',
    > 'July 5, 2000', 'July 19, 2000'
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 2, 'Dave',
    > 'July 20, 2000','July 25, 2000'
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 3, 'Dave',
    > 'July 18, 2000', 'July 30, 2000'
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 4, 'Dave',
    > 'Feb 2, 2000', 'Feb 14, 2000'
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 5,

    'Eduard',
    > 'Feb 2, 2000', 'Feb 9, 2000'
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 6,

    'Eduard',
    > 'Feb 10, 2000', 'Feb 17, 2000'
    > INSERT INTO Timesheet (PK, Person, Start_Date, End_Date) SELECT 7,

    'Eduard',
    > 'Feb 13, 2000', 'Feb 22, 2000'
    >
    > SELECT * FROM Timesheet
    >
    > DROP TABLE [Timesheet]




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center