I have a dataset with a bunch of event data. Some years have one event some have multiple.
I'm trying to only give the first and last event for a particular year.
This is what I have so far:
WITH yearnum (EID, ED, YR, EN, EDS, CID, COD) AS
(
SELECT
eventid, MIN(eventdate) AS minimum_event_date,
DATEPART(year, EVENTdate) AS year_in_question,
EventName, EventDetails, CategoryID, CountryID
FROM
tblevent
GROUP BY
eventid, EventDate, EventName, EventDetails, CategoryID, CountryID
),
rownumS (EID, ED, YR, EN, EDS, CID, COD, RN) AS
(
SELECT
EID, ED, YR, EN, EDS, CID, COD,
ROW_NUMBER() OVER (PARTITION BY YR ORDER BY YR ASC) AS rownumyear
FROM
yearnum
)
SELECT
EID, ED, YR, EN, EDS, CID, COD, RN
FROM
rownums
Result:
2条答案
按热度按时间8wigbo561#
Break the problem down into pieces you can process.
This may not be the most efficient way alternatives include
Assumptions:
Option 1) using exists and correlation to only incldue those with a min/max event date per year which we determine in advance in a common table expression.
Alternative apporach using rownumber window function...
.
wvt8vs2t2#
I managed to get it to display what I wanted: