Gossamer Forum
(Advanced Search)
for
Home : General : Databases and SQL :

Difficult SQL query

May 15, 2003, 7:06 AM
oda10
New User (2 posts)
May 15, 2003, 7:06 AM
Post #1 of 3
Views: 5225
Shortcut
Quote Reply
Difficult SQL query
Hi!
I have this table:
IDNR FOM TYP TOM
1000 2003-mar-31
1000 2003-mar-31 T 2003-apr-30
1000 2003-feb-11 2003-mar-30
1010 2003-mar-31
1020 2003-mar-31 T 2003-apr-30
1020 2003-mar-31 2003-apr-30
1030 2003-mar-31
1030 2003-feb-11 2003-apr-30

If I run the query on april 29 I want this result:
IDNR FOM TYP TOM
1000 2003-mar-31 T 2003-apr-30
1010 2003-mar-31
1020 2003-mar-31 T 2003-apr-30
1030 2003-feb-11 2003-apr-30

I want only one record per IDNR. If the runningdate is between FOM and TOM and TYP=T then that record should be returned. If TYP != T and the runningdate is between FOM and TOM then this record is correct, bot only if the first condition isn't met. If FOM is before the runningdate and TOM is null theh that record should be returned, but only if the first two conditions fail Angelic Any idea anyone?
Thank you
/Fredrik
May 15, 2003, 9:00 PM
Unquick
Novice (35 posts)
Unquick's avatar
May 15, 2003, 9:00 PM
Post #2 of 3
Views: 5077
Shortcut
Quote Reply
Re: [oda10] Difficult SQL query In reply to
In Reply To:

Quote:
I want only one record per IDNR. If the runningdate is between FOM and TOM and TYP=T then that record should be returned.
OK, so you will get two records back with the first condition
Quote:
If TYP != T and the runningdate is between FOM and TOM then this record is correct, bot only if the first condition isn't met.
OK, this doesn't make alot of senceUnsure: If TYP does not equal 'T' and first condition was not met, this would mean that the rest of the records would be picked up also.
Quote:
If FOM is before the runningdate and TOM is null theh that record should be returned, but only if the first two conditions fail Any idea anyone?
All the dates in this example are before FOM and everything that didn't make it into the last two condition will fall through to this one (because first two conditions will never be true because you can't compare dates if one is NULL.
Thank you
/Fredrik


Maybe I'm just confused by the wording, but since it seems to me that everything would go through based on the description I would suggest a "select * from table" Tongue Maybe if you redescibe it in more detail I will see if I can whip up a statment for you.
May 16, 2003, 12:08 AM
oda10
New User (2 posts)
May 16, 2003, 12:08 AM
Post #3 of 3
Views: 4991
Shortcut
Quote Reply
Re: [Unquick] Difficult SQL query In reply to
Ok, I'll try to give a better explanaition Smile

For each IDNR there can be up to three records. All of them have a FOM-date. They can all be the same date but in most cases they are different. If one of the records for an IDNR has TYP=T (TYP is always either T or a space) and the runningdate is between FOM and TOM then this is the record to return. If TYP=T this should always go first, but only if the datecondition are met. If the datecondition fails we have to go to the record with both a FOM and a TOM date. If the datecondition holds, then this record should be returned. Otherwise we have to return the record that only has a FOM-date (if it is before the runningdate of course).

I think I have to make some kind of subquery if it is at all possible to make this kind of query.

Hope this makes it a bit clearer Crazy

/Fredrik