A better way to summarize Date results?

Question

Post reply

A better way to summarize Date results?

lenardd

Default port

Points: 1435
More actions
March 20, 2004 at 9:55 am

#161125

Consider the following dataset:
ID, EventNumber, EventNameID, StartDate, EndDate
1, 001, 3, 20040301, 20040302
2, 002, 7, 20040501, 20040502
3, 002, 7, 20040503, 20040504
4, 002, 7, 20040505, 20040506
I would like to summarize it as follows:
1, 001, 3, 20040301, 20040302
2, 002, 7, 20040501, 20040506
Notice that the second record contains the StartDate of record #2 and the EndDate of record #4 of the original dataset.
The only way that comes to mind is to sort ASC by StartDate and in the SELECT clause do a Top 1 and call a UDF to return the MAX(EndDate) for the EventNumber record being processed.
Is there a better way?
--Lenard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Here is the way.

select EventNumber, EventNameID, min(StartDate) StartDate, max(EndDate) EndDate

group by EventNumber, EventNameID

You may have to finish it by puting identity column at the beginning.

lenardd Default port Points: 1435 More actions · Answer 2

Thanks wz700 for your reply.

Your method won't work in my "real world senario" because some of the columns in my SELECT statement may not contain the same value within the group. I can only count on EventNumber being equal.

Sorry about misleading you. In my example, I should have showed EventNameID being different.

--Lenard

Antares686 SSC Guru Points: 125444 More actions · Answer 3

So let's say you data looked like this.

1, 001, 3, 20040301, 20040302

2, 002, 7, 20040501, 20040502

3, 002, 6, 20040503, 20040504

4, 002, 7, 20040505, 20040506

5, 001, 3, 20040601, 20040602

2, 003, 4, 20040601, 20040602

3, 003, 4, 20040603, 20040604

4, 002, 6, 20040605, 20040606

What would you expect your out come to look like?

lenardd Default port Points: 1435 More actions · Answer 4

Hi Antares686:

Actually, the ID will always be unique. So changing your dataset slightly:

1, 001, 3, 20040301, 20040302

2, 002, 7, 20040501, 20040502

3, 002, 6, 20040503, 20040504

4, 002, 7, 20040505, 20040506

5, 001, 3, 20040601, 20040602

6, 003, 4, 20040601, 20040602

7, 003, 4, 20040603, 20040604

8, 002, 6, 20040605, 20040606

What I decided to do is do is an accending sorted SELECT with a TOP 1 and then create a UDF called LookupEventNumberDatePeriod ( EventNumber ) to return a formatted date range ( like "20040501-20040606" ) of each result. In the UDF, I used the MIN/MAX example as per wz700 post.

So using your dataset example, it would return the following:

001, 3, "20040301-20040602"

002, 7, "20040501-20040606"

003, 3, "20040601-20040604"

In my program, the user picks from the above list. The EventNumber is passed to the main form where the actual individual event days are shown in a more detailed manner.

--Lenard

wz700 SSCertifiable Points: 7133 More actions · Answer 5

Would this help?

select EventNumber, EventNameID=Max(EventNameID), StartDate=Min(StartDate), EndDate=Max(EndDate) from test

group by EventNumber

lenardd Default port Points: 1435 More actions · Answer 6

That would get around it for this simple example. But in my "real world" senario, I have columns which return text based info (like location and incident text) and would fall apart there.

My method and your earlier suggestion (implemented via a UDF) seems to work fine.

Thanks for your time and suggestions.

--Lenard