Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    Cool Unanswered: Linking on date, date - 1, date +1

    I've got a complex query (union and left outer joins) to bring some system data together by date. The problem is sometimes the system processes multiple transactions over several days. I'm not sure how to write the statement to join the transactional data properly. There are additional joins and AND statements other than this.

    Currently:
    where (case when ss.start_dt is null then ss.stop_dt else ss.start_dt end)*=ah.eff_dt

    Is there a means of writing the query so that it's along the lines of
    where (date1) OR (date1 + 1 day) OR (date1- 1day) *= ah.eff_dt

    I realize I need to use a DateAdd function... I was just asking in english. Thanks!
    Last edited by coders4hire; 07-29-04 at 20:06.
    Doug

  2. #2
    Join Date
    Aug 2004
    Posts
    42
    Something like this:

    create table #a ( Date1 datetime, k char(1) )
    create table #ah ( eff_dt datetime, k char(1) )

    insert #a select "6/14/2004" ,"K"
    insert #a select "6/15/2004" ,"K"
    insert #a select "6/16/2004" ,"K"
    insert #a select "7/10/2004" ,"K"
    insert #a select "7/12/2004" ,"K"

    insert #ah select "6/15/2004" ,"K"
    insert #ah select "6/16/2004" ,"K"
    insert #ah select "7/10/2004" ,"K"


    select #a.*,#ah.*
    from #a,#ah
    where #a.k *= #ah.k -- Just to represent other outer-join conditions
    and (dateadd (day, 1, Date1) *= #ah.eff_dt
    or dateadd (day, 0, Date1) *= #ah.eff_dt
    or dateadd (day,-1, Date1) *= #ah.eff_dt)

    Results:

    Date1 k eff_dt k
    -------------------------- - -------------------------- -
    Jun 14 2004 12:00AM K Jun 15 2004 12:00AM K
    Jun 15 2004 12:00AM K Jun 15 2004 12:00AM K
    Jun 15 2004 12:00AM K Jun 16 2004 12:00AM K
    Jun 16 2004 12:00AM K Jun 15 2004 12:00AM K
    Jun 16 2004 12:00AM K Jun 16 2004 12:00AM K
    Jul 10 2004 12:00AM K Jul 10 2004 12:00AM K
    Jul 12 2004 12:00AM K NULL NULL

    While this construction is legal you might want to be aware that

    1. Performance may be an issue. I would -- as always -- review the showplan output.
    2. You may get 1-to-many joins -- see the 2 Jun16 rows in the result set above -- that you don't expect.
    3. Are you really sure you want an outer join?

  3. #3
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40
    I appreciate the help. I'm actually going to loop through and write the min date difference from a subselect after giving it some more thought. Your feedback helped a lot! Thanks!
    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •