Interesting query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Interesting query

Author

Topic

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-16 : 16:41:58

Table A has an ID, an authorization number, and a date
Table B has an ID, optional start and end dates for the authorizaton number

In a single query return whether the authorization number in Table A is good to use or not based on the following rules.

1. If the Table A ID is not in Table B it is good.
2. If the Table A ID is in Table B and Table A date is within the Table B start/end date range it is good (remember the start and end dates are both optional).
3. If the Table A ID is in Table B and both Table B dates are null the authorization is good.
4. If the Table A ID is in Table B, but outside the start/end date range the authorization is not good.

"In theory there is no difference between theory and practice. But in practice there is!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-16 : 17:08:10

SELECT A.* FROM TableA A
LEFT JOIN TableB B ON (A.ID=B.ID)
WHERE B.ID Is Null OR (B.StartDate IS Null AND B.EndDate Is Null)
OR A.Date BETWEEN IsNull(B.StartDate,A.Date) AND IsNull(B.EndDate, A.Date)

I think that'll do it.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-16 : 17:08:15

create table #a
(
id int identity(1,1),
authNum int,
datestamp datetime
)

create table #b
(
id int identity(1,1),
start datetime,
stop datetime
)

insert #a ( authNum, datestamp )
select 1 as a, current_timestamp union
select 2, dateadd(dd, -1, current_timestamp ) union
select 3, dateadd(dd, -2, current_timestamp ) union
select 4, dateadd(dd, 0, current_timestamp ) union
select 5, dateadd(dd, 0, current_timestamp )
order by a

insert #b ( start, stop )
select dateadd(dd, -1, current_timestamp ) as a, dateadd(dd, 2, current_timestamp ) union
select dateadd(dd, 0, current_timestamp ), dateadd(dd, 5, current_timestamp )union
select dateadd(dd, 2, current_timestamp ), dateadd(dd, 3, current_timestamp ) union
select null, null

-- select "good" authorization #'s according to above rules
select a.authNum, 'good'
from #a a
where not exists ( select 1 from #b where id = a.id )
or exists ( select 1 from #b where id = a.id and a.datestamp between start and stop )
or exists ( select 1 from #b where id = a.id and coalesce(start,stop) is null )
union
select a.authNum, 'bad'
from #a a
inner join #b b on a.id = b.id
where a.datestamp not between b.start and b.stop

setBasedIsTheTruepath
<O>

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-17 : 08:59:35

Nice query Rob, you're close but no cigar. This would return the good authorizations, but it's just as important to return the bad authorizations. Note the requirement to return whether the authorization number from Table A is good to use or not.

setbasedisthetruepath you're close also, but you missed the requirement in rule 2 that both start and end date are optional. There may be a start date entered with no end date or an end date entered with no start date.

Fun huh!?

Thanks for the responses.

"In theory there is no difference between theory and practice. But in practice there is!"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-17 : 09:23:26

did you actually need this for something or were we just validating your answer to a homework question?

setBasedIsTheTruepath
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 09:27:25

Well, I read this to mean:

4. If the Table A ID is in Table B, but outside the start/end date range the authorization is not good.

That you didn't want the bad authorizations. This should do the trick:

SELECT A.*, CASE WHEN B.ID Is Null THEN 'Good'
WHEN (B.StartDate IS Null AND B.EndDate Is Null) THEN 'Good'
WHEN A.Date BETWEEN IsNull(B.StartDate,A.Date) AND IsNull(B.EndDate, A.Date) THEN 'Good'
ELSE 'Bad' END AS Authorization
FROM TableA A
LEFT JOIN TableB B ON (A.ID=B.ID)

And you have to admit, this DOES sound like a homework question

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-17 : 10:17:39

It does sound like a homework problem doesn't it

It's actually a stored proc I rewrote that uses 2 cursors to do this

.
I just thought it an interesting problem that others might enjoy.
The quirky thing is the requirement that if the authorization does not exist in the other table its good and if it does it needs to meet other criteria to be good, otherwise its bad. What people (me

at first) miss is that a left join with a where clause for the criteria doesn't work. If the authorization is not in the other table, fine the second table is null the auth is good, but if it does exist in the other table, but doesn't meet the criteria samething but the auth is supposed to be bad in this case. If you see what I mean.

I have to say you guys and gals are good!

"In theory there is no difference between theory and practice. But in practice there is!"

Nazim
A custom title

1408 Posts

Posted - 2002-04-17 : 10:25:12

Where are the gals, never find them here. if you find do send them to me . i need to talk to them urgently

quote:

I have to say you guys and gals are good!

--------------------------------------------------------------

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-17 : 10:28:43

Not really sure, but figured I'd be P.C. and cover my bases

Hard to tell gender from a handle like: setBasedIsTheTruepath

quote:

Where are the gals, never find them here. if you find do send them to me . i need to talk to them urgently
quote:

I have to say you guys and gals are good!

--------------------------------------------------------------

"In theory there is no difference between theory and practice. But in practice there is!"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-17 : 10:59:01

as if 'btrimpop' answered anything!

setBasedIsTheTruepath
<O>

Nazim
A custom title

1408 Posts

Posted - 2002-04-17 : 11:30:53

Atleast his profile does