Friday, March 23, 2012

Noob count question

I have a table of events, which stores an event ID, event Name and event
date.
I want to write a stored procedure that returns the next 4 events starting
from today, so far I have:
SELECT
[EventID],
[EventName],
[EventDate]
FROM
Events
WHERE
(DATEDIFF(dd, GETDATE(), EventDate) > 0)
ORDER BY
[EventDate] ASC
How do I add a clause to say only return a maximum of 4 (or however many is
left if fewer) events from today?Use the TOP clause using 4 as the number of records to be returned.
Optionally SET ROWCOUNT. But TOP is preferred.
HTH
Jerry
"Seefor" <invalid@.email.address> wrote in message
news:DfBXe.111419$G8.92946@.text.news.blueyonder.co.uk...
>I have a table of events, which stores an event ID, event Name and event
>date.
> I want to write a stored procedure that returns the next 4 events starting
> from today, so far I have:
> SELECT
> [EventID],
> [EventName],
> [EventDate]
> FROM
> Events
> WHERE
> (DATEDIFF(dd, GETDATE(), EventDate) > 0)
> ORDER BY
> [EventDate] ASC
>
> How do I add a clause to say only return a maximum of 4 (or however many
> is left if fewer) events from today?
>|||many thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:etg0fRTvFHA.1392@.tk2msftngp13.phx.gbl...
> Use the TOP clause using 4 as the number of records to be returned.
> Optionally SET ROWCOUNT. But TOP is preferred.
> HTH
> Jerry
> "Seefor" <invalid@.email.address> wrote in message
> news:DfBXe.111419$G8.92946@.text.news.blueyonder.co.uk...
>|||If you want to use an index on EventDate efficiently, make sure you don't
have any manipulation on the filtered column.
Instead of:
WHERE (DATEDIFF(dd, GETDATE(), EventDate) > 0)
Use:
WHERE EventDate >= CONVERT(VARCHAR(8), GETDATE(), 112)
Specify TOP 4 in the SELECT list, and sort by EventDate .
BG, SQL Server MVP
www.SolidQualityLearning.com
"Seefor" <invalid@.email.address> wrote in message
news:DfBXe.111419$G8.92946@.text.news.blueyonder.co.uk...
>I have a table of events, which stores an event ID, event Name and event
>date.
> I want to write a stored procedure that returns the next 4 events starting
> from today, so far I have:
> SELECT
> [EventID],
> [EventName],
> [EventDate]
> FROM
> Events
> WHERE
> (DATEDIFF(dd, GETDATE(), EventDate) > 0)
> ORDER BY
> [EventDate] ASC
>
> How do I add a clause to say only return a maximum of 4 (or however many
> is left if fewer) events from today?
>

No comments:

Post a Comment