Re: previous value in a table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 10/07/04 

Date: Wed, 6 Oct 2004 18:52:49 -0700

Since event_date is not unique per cal_id, you need to choose a tiebreaker
(e.g., max id for prev, and min id for next).
See if the following query works for you:

SELECT id,
  (SELECT TOP 1 id
   FROM my_events AS E2
   WHERE E2.cal_id = E1.cal_id
     AND E2.event_date < E1.event_date
   ORDER BY event_date DESC, id DESC) AS previd,
  (SELECT TOP 1 id
   FROM my_events AS E2
   WHERE E2.cal_id = E1.cal_id
     AND E2.event_date > E1.event_date
   ORDER BY event_date, id) AS nextid
FROM my_events AS E1
WHERE event_date = '20040201' 

-- 
BG, SQL Server MVP
www.SolidQualityLearning.com
"Steven Yampolsky" <syampolsky@eagleinvsys.com> wrote in message 
news:%23otEFg$qEHA.1296@TK2MSFTNGP12.phx.gbl...
>I think I oversimplified my problem. Let me correct it. I am lookin for a
> way to fetch fetch event IDs instead of dates:
>
> cal_id|previous_event|next_event
> 1|3|1
> 2|4|6
>
> The only query I could think of was this:
>
> SELECT
> cur.cal_id, prv.id pid, cur.id cid, nxt.id nid
> FROM
> my_events cur,
> my_events nxt,
> my_events prv
> WHERE
> nxt.cal_id = cur.cal_id AND
> prv.cal_id = cur.cal_id AND
> nxt.event_date = (SELECT MIN(event_date) FROM my_events WHERE cal_id =
> cur.cal_id AND event_date > cur.event_date) AND
> prv.event_date = (SELECT MAX(event_date) FROM my_events WHERE cal_id =
> cur.cal_id AND event_date < cur.event_date)
> -- actual argument
> AND event_date = '20040201'
>
> Above query does not take into account the fact that there may be multiple
> events on the same date BUT, I need to display one row per cal_id. If one
> where to do one more insert:
>
> INSERT my_events VALUES(2,'2004-03-15')
>
> the result will have two rows for cal_id = 2 where I need just one:
>
> 1|3|1
> 2|4|6
> 2|4|7
>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:ulgUSN$qEHA.332@TK2MSFTNGP14.phx.gbl...
>> SELECT cal_id, MIN(event_date)
>>     FROM my_events
>>     WHERE event_date > '20040201'
>>     GROUP BY cal_id
>>     ORDER BY cal_id
>>
>> -- 
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>>
>>
>> "Steven Yampolsky" <syampolsky@eagleinvsys.com> wrote in message
>> news:OybqlF$qEHA.376@TK2MSFTNGP14.phx.gbl...
>> > I need help build a query/funciton. I have a table that contains event
>> > information for various parts of my web site. Each calendar has a set 
>> > of
>> > events that are independent from other calendars. I need a way to fetch
>> > prior and next event dates for a given event date.
>> >
>> > Here's the table definition:
>> >
>> > CREATE TABLE my_events ( id INT identity, cal_id INT, event_date
> DATETIME)
>> >
>> > here's a sample data:
>> >
>> > INSERT my_events VALUES( 1,'2004-01-01')
>> > INSERT my_events VALUES(1,'2004-02-01')
>> > INSERT my_events VALUES(1,'2004-03-01')
>> > INSERT my_events VALUES(2,'2004-01-15')
>> > INSERT my_events VALUES(2,'2004-02-01')
>> > INSERT my_events VALUES(2,'2004-03-15')
>> > INSERT my_events VALUES(2,'2004-03-17')
>> >
>> > What can I do to be able to get the following results by passing
>> 2004-02-01
>> > as my argument:
>> >
>> > cal_id|previous_date|next_date
>> > 1|2004-01-01|2004-03-01
>> > 2|2004-01-15|2004-03-15
>> >
>> >
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: previous value in a table
    ... use the query I provided. ... > BG, SQL Server MVP ... >> way to fetch fetch event IDs instead of dates: ... Each calendar has a set ...
    (microsoft.public.sqlserver.programming)
  • Re: previous value in a table
    ... > It is a bad query as it takes progressively longer to execute as the data ... >> BG, SQL Server MVP ... >>> way to fetch fetch event IDs instead of dates: ... Each calendar has a ...
    (microsoft.public.sqlserver.programming)
  • Re: previous value in a table
    ... way to fetch fetch event IDs instead of dates: ... Above query does not take into account the fact that there may be multiple ... Each calendar has a set of ...
    (microsoft.public.sqlserver.programming)
  • Showing tasks in calendar
    ... This seems too easy, but I can't find the answer to my query in Help, so I'd ... be grateful if anyone could assist me. ... I would very much like the calendar to show task due dates ... Prev by Date: ...
    (microsoft.public.outlook)
  • Calulating quarters from date field
    ... I can't seem to get my calendar dates to calculate as serial quarterly ... numbers when I try to build my query. ... Joe ... Prev by Date: ...
    (microsoft.public.access.queries)