Date Range - once again

Question

Date Range - once again

dobrzak-134971

Right there with Babe

Points: 737
More actions
July 26, 2006 at 7:33 am

#92143

Hi all,
probably someone has asked about that problem, but I can't find, so if you can point me out it would be great. In other case, please help me. How I can create data range from other data range, here is example:
I have table:
ClientID    Date    Value
1    2005-03-16    2
1    2005-04-16    3
1    2005-05-16    3
1    2005-06-16    2
1    2005-07-16    2
2    2005-03-16    2
2    2005-04-16    2
2    2005-05-16    3
2    2005-06-16    3
3    2005-06-16    4
3    2005-07-16    4
3    2005-08-16    4
But how create table:

ClientID    StartDate    EndDate    Value
1    2005-03-16    2005-04-15    2
1    2005-04-16    2005-06-15    3
1    2005-06-16    2079-06-06    2
2    2005-03-16    2005-05-15    2
2    2005-05-16    2079-06-06    3
3    2005-06-16    2079-06-06    4

Many thanks for help.

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

When I first started playing with this, I figured it was simply a subselect with grouping by ClientID and Value. But when I look at your desired results, I am completely confused.

I take it 2079-06-06 is a default value. Can you explain the relationships you are trying to portray a bit more. I am not getting how you would get these final results.

Thanks

I wasn't born stupid - I had to study.

dobrzak-134971 Right there with Babe Points: 737 More actions · Answer 2

No it isn't as simple as it seems. The last EndDate for client is equal the max value for data type smalldatetime.

But I've resolved this problem, I'll add here solution later.

Regards,

Rafal

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I understood that, but 2079-06-06 is not within your sample data... Look forward to your solution.

I wasn't born stupid - I had to study.

dobrzak-134971 Right there with Babe Points: 737 More actions · Answer 4

I've red that we should avoid using nulls in tables. What the difference if I update date '6/6/2079' instead of null ? And I have improve in executing query using BETWEEN.

Regards,

Rafal

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

Use NULLs where it makes sense to use NULLs.

NULL = unknown.

Since the end date is indeed unknown, it makes sense to use a NULL value.

The problem is that many folks don't know how to use / query against NULL values. But that's just laziness on their parts.

dobrzak-134971 Right there with Babe Points: 737 More actions · Answer 6

But I really don't see the problem with that end date ? I can query against NULL values, but I think that in this case is better using '6/6/2079'. Maybe I'm wrong, but your argument don't persuade me.

dobrzak-134971 Right there with Babe Points: 737 More actions · Answer 7

Hmm, you have right, I'll change this in my table.

Thanks,

Rafal