tricky CASE WHEN query... 
Author Message
 tricky CASE WHEN query...

I have 2 tables, STATUS and LOG.

The STATUS table contain 2 columns, "statuscode" and "stamp".
The LOG table only contain 1 column, "stamp".

STATUS
==statuscode===stamp==========
   ON                  23-01-2002 18:27:23
   OFF                 24-01-2002 19:23:32
   IDLE                25-01-2002 20:10:43

LOG
===stamp==========
    20-01-2002 17:26:33
    23-01-2002 18:27:53
    24-01-2002 20:27:14

I wan't to make a query that outputs either "1" or "0".

It has to output "1" in the following cases and "0" in any other
combination:
1.) WHEN MAX(status.stamp) > MAX(log.stamp)
2.) WHEN status.stamp record is NOT found AND log.stamp record is NOT found
3.) WHEN status.stamp IS found AND log.stamp record is NOT found

Can you help me?

/Jakob



Tue, 20 Jul 2004 21:22:10 GMT
 tricky CASE WHEN query...

CASE
    WHEN MAX(a.Stamp) > MAX(b.Stamp) THEN 1
    WHEN a.Stamp IS NULL and b.Stamp IS NOT NULL THEN 1
    ELSE 0
END

Hope it helps

Quote:
> I have 2 tables, STATUS and LOG.

> The STATUS table contain 2 columns, "statuscode" and "stamp".
> The LOG table only contain 1 column, "stamp".

> STATUS
> ==statuscode===stamp==========
>    ON                  23-01-2002 18:27:23
>    OFF                 24-01-2002 19:23:32
>    IDLE                25-01-2002 20:10:43

> LOG
> ===stamp==========
>     20-01-2002 17:26:33
>     23-01-2002 18:27:53
>     24-01-2002 20:27:14

> I wan't to make a query that outputs either "1" or "0".

> It has to output "1" in the following cases and "0" in any other
> combination:
> 1.) WHEN MAX(status.stamp) > MAX(log.stamp)
> 2.) WHEN status.stamp record is NOT found AND log.stamp record is NOT
found
> 3.) WHEN status.stamp IS found AND log.stamp record is NOT found

> Can you help me?

> /Jakob



Wed, 21 Jul 2004 00:17:14 GMT
 tricky CASE WHEN query...
Read the message at the bototm of this post.  I will make a guess not
supposed by what you posted that the stamps are the primary keys and
therefore they are never NULL.

Quote:
>> It has to output "1" in the following cases and "0" in any other

combination:
1.) WHEN MAX(status.stamp) > MAX(log.stamp)
2.) WHEN status.stamp record is NOT found AND log.stamp record is NOT
found
3.) WHEN status.stamp IS found AND log.stamp record is NOT found <<

The answer is always 1!  Look at condition #2 -- There is always a time
that is missing from both tables.  We need a new specification.  

SELECT DISTINCT
       CASE WHEN L1.stamp IS NULL THEN 1
            WHEN (SELECT MAX(stamp) FROM Status)
                  > (SELECT MAX(stamp) FROM Log)
            THEN 1
            WHEN <<correct version of #2>>
            THEN 1 ELSE 0 END
  FROM Status AS S1
       LEFT OUTER JOIN
       Log AS L1
       ON S1.stamp = L1.stamp

The trick is using scalar subqueries and ordering the WHEN clauses from
most likely to happen to least likely to happen.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 21 Jul 2004 00:41:21 GMT
 tricky CASE WHEN query...
The stamp are NOT primary keys and there is also some other columns in each
table that I just didn't put into my question because they are irrelevant
and I just fit the SQL query with some extra column1=value1 AND
culumn2=value2 so it works in my setup:)

I don?t know if the word NULL is the right one to use, but it might be that
there isn't any records in one or both af the tables and that was what I
wanted to take care of when I wrote "WHEN status.stamp record is NOT found
AND log.stamp record is NOT found".

Do you get it:)?

The sample data provided was just example data and should only be used for
easying the question.

In other words, what I wan't to do is:

1.) WHEN MAX(status.stamp) > MAX(log.stamp)

Quote:
>>This is when there is actually some records to compare in both rows...

2.) WHEN status.stamp record is NOT found AND log.stamp record is NOT found.

Quote:
>> This is when there is no records found in both the tables

3.) WHEN status.stamp IS found AND log.stamp record is NOT found
Quote:
>> This is when there are records in Status table and no records in Log

table.

I hope this clears up my question:)

/Jakob



Quote:
> Read the message at the bototm of this post.  I will make a guess not
> supposed by what you posted that the stamps are the primary keys and
> therefore they are never NULL.

> >> It has to output "1" in the following cases and "0" in any other
> combination:
> 1.) WHEN MAX(status.stamp) > MAX(log.stamp)
> 2.) WHEN status.stamp record is NOT found AND log.stamp record is NOT
> found
> 3.) WHEN status.stamp IS found AND log.stamp record is NOT found <<

> The answer is always 1!  Look at condition #2 -- There is always a time
> that is missing from both tables.  We need a new specification.

> SELECT DISTINCT
>        CASE WHEN L1.stamp IS NULL THEN 1
>             WHEN (SELECT MAX(stamp) FROM Status)
>                   > (SELECT MAX(stamp) FROM Log)
>             THEN 1
>             WHEN <<correct version of #2>>
>             THEN 1 ELSE 0 END
>   FROM Status AS S1
>        LEFT OUTER JOIN
>        Log AS L1
>        ON S1.stamp = L1.stamp

> The trick is using scalar subqueries and ordering the WHEN clauses from
> most likely to happen to least likely to happen.

> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Wed, 21 Jul 2004 02:03:40 GMT
 tricky CASE WHEN query...
Quote:
>> The stamp are NOT primary keys and there is also some other columns

in each table that I just didn't put into my question because they are
irrelevant <<

Keys are never irrelevant; if you do not have a key, then you do not
have a table.  Now I can guess that the timestamps in each table can be
duplicated or be NULL, but I do not if that guess is correct

Quote:
>> I hope this clears up my question:) <<

No, again, please post DDL, so that people do not have to guess what the
keys, constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are.  Include sample data that shows all three cases.  

I still do not understand #2.  Should I return the all the microseconds
from "2002-02-02 00:00:00.0000" to "9999-12-31  00:00:00.0000" because
they do not appear in either table?  I do not thyink tat is right, but
that is the specification.  

--CELKO--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 21 Jul 2004 02:29:18 GMT
 tricky CASE WHEN query...
Ok Joe, I'll try to make my question more clearly and here I've provided all
information about the tables...

I have 2 tables: STATUS and LOG.

The STATUS table has 3 columns:
ID(smallint)(primary key), statuscode(char[10]) and stamp(datetime)

The LOG table has 2 columns:
UserID(smallint) and stamp(datetime) (UserID+stamp makes primary key)

The STATUS table holds information of the statuscode of every ID. Only 1
statuscode can be given per ID but the same statuscode can be given to
different IDs. The IDs can range from 1-100 and only those IDs that have
been given a statuscode are in the table. If an ID not yet have been given a
statuscode, this ID will NOT be in the table.

The LOG table holds information about when a user has last "seen" all
statuscodes for the IDs.

Now the combinations where I wan't "1" as output:
1.) status.stamp > log.stamp
If a statuscode are given to any ID, in the time after the user last have
"seen" the statuscodes.
*There are 1 or more records in both STATUS and LOG.

2.) NOT EXISTS(status.stamp) AND NOT EXISTS(log.stamp)
If there have not yet been given any statuscodes to any IDs and the user
have not yet "seen" any statuscodes.
*There are no records in both STATUS or LOG table.

3.) EXISTS(status.stamp) AND NOT EXISTS(log.stamp)
If there are IDs that has been given a statuscode but the user never have
"seen" the statuscodes.
*There are no record in the LOG table but 1 or more records on the STATUS
table.

Was this a better explanation?

/Jakob



Quote:
> >> The stamp are NOT primary keys and there is also some other columns
> in each table that I just didn't put into my question because they are
> irrelevant <<

> Keys are never irrelevant; if you do not have a key, then you do not
> have a table.  Now I can guess that the timestamps in each table can be
> duplicated or be NULL, but I do not if that guess is correct

> >> I hope this clears up my question:) <<

> No, again, please post DDL, so that people do not have to guess what the
> keys, constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are.  Include sample data that shows all three cases.

> I still do not understand #2.  Should I return the all the microseconds
> from "2002-02-02 00:00:00.0000" to "9999-12-31  00:00:00.0000" because
> they do not appear in either table?  I do not thyink tat is right, but
> that is the specification.

> --CELKO--

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Wed, 21 Jul 2004 03:33:23 GMT
 tricky CASE WHEN query...
J,

  I don't think I completely understand, but I think
you will need a table containing all the IDs to do
this, since you want results even for IDs that are
not in either table.

This may be close to what you want, and I hope it helps:

set dateformat dmy

create table Jstatus (
  ID int primary key,
  status char(4),
  stamp datetime
)
insert into Jstatus values (1,'ON','23-01-2002 18:27:23')
insert into Jstatus values (2,'OFF','24-01-2002 19:23:32')
insert into Jstatus values (3,'IDLE','25-01-2002 20:10:43')
insert into Jstatus values (5,'OFF','25-01-2002 20:11:43')
go

create table JLog (
  UserID int not null,
  stamp datetime not null,
  constraint JLog_pk
  PRIMARY KEY (UserID, stamp)
)
insert into JLog values (2,'20-01-2002 17:26:33')
insert into JLog values (3,'23-01-2002 18:27:53')
insert into JLog values (3,'24-01-2002 20:27:14')
insert into JLog values (4,'23-01-2002 18:27:53')
insert into JLog values (4,'26-01-2002 20:27:14')
insert into JLog values (6,'26-01-2002 20:27:14')
insert into JLog values (7,'20-01-2002 20:27:14')

--Make the table of 1-100
select top 100 identity(int,1,1) as ID
into #All_id from Northwind..Orders

--Now run the query
select A.ID,
   case when
      L.UserID is null or
      max(L.Maxstamp) < (
        select max(stamp) from JStatus)
   then 1 else 0 end as answer
from #All_ID A
left join (
  select UserID, MAX(stamp) as Maxstamp
  from JLog group by UserID) L
on L.UserID = A.ID
where A.ID < 10 --for demonstration
group by A.ID, L.UserID
order by A.ID
go

drop table #All_id
drop table JStatus
drop table JLog

Steve Kass
Drew University

Quote:

> Ok Joe, I'll try to make my question more clearly and here I've provided all
> information about the tables...

> I have 2 tables: STATUS and LOG.

> The STATUS table has 3 columns:
> ID(smallint)(primary key), statuscode(char[10]) and stamp(datetime)

> The LOG table has 2 columns:
> UserID(smallint) and stamp(datetime) (UserID+stamp makes primary key)

> The STATUS table holds information of the statuscode of every ID. Only 1
> statuscode can be given per ID but the same statuscode can be given to
> different IDs. The IDs can range from 1-100 and only those IDs that have
> been given a statuscode are in the table. If an ID not yet have been given a
> statuscode, this ID will NOT be in the table.

> The LOG table holds information about when a user has last "seen" all
> statuscodes for the IDs.

> Now the combinations where I wan't "1" as output:
> 1.) status.stamp > log.stamp
> If a statuscode are given to any ID, in the time after the user last have
> "seen" the statuscodes.
> *There are 1 or more records in both STATUS and LOG.

> 2.) NOT EXISTS(status.stamp) AND NOT EXISTS(log.stamp)
> If there have not yet been given any statuscodes to any IDs and the user
> have not yet "seen" any statuscodes.
> *There are no records in both STATUS or LOG table.

> 3.) EXISTS(status.stamp) AND NOT EXISTS(log.stamp)
> If there are IDs that has been given a statuscode but the user never have
> "seen" the statuscodes.
> *There are no record in the LOG table but 1 or more records on the STATUS
> table.

> Was this a better explanation?

> /Jakob



> > >> The stamp are NOT primary keys and there is also some other columns
> > in each table that I just didn't put into my question because they are
> > irrelevant <<

> > Keys are never irrelevant; if you do not have a key, then you do not
> > have a table.  Now I can guess that the timestamps in each table can be
> > duplicated or be NULL, but I do not if that guess is correct

> > >> I hope this clears up my question:) <<

> > No, again, please post DDL, so that people do not have to guess what the
> > keys, constraints, Declarative Referential Integrity, datatypes, etc. in
> > your schema are.  Include sample data that shows all three cases.

> > I still do not understand #2.  Should I return the all the microseconds
> > from "2002-02-02 00:00:00.0000" to "9999-12-31  00:00:00.0000" because
> > they do not appear in either table?  I do not thyink tat is right, but
> > that is the specification.

> > --CELKO--

> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!



Wed, 21 Jul 2004 09:15:09 GMT
 tricky CASE WHEN query...
Hi everybody!
I managed to do what I wanted:)
Here's the query:

SELECT
(CASE WHEN MAX(S.stamp) <> '' THEN

(CASE WHEN MAX(S.stamp) > (SELECT MAX(L.stamp) FROM log AS L WHERE
L.user_id=1) THEN 1 ELSE 0 END)

WHEN MAX(S.stamp) IS NULL THEN

(CASE WHEN (SELECT MAX(L.stamp) FROM log AS L WHERE L.user_id=1) IS NULL
THEN 1 ELSE 0 END)

ELSE 0 END) AS refresh
FROM status AS S

Thanks anyway for your help:) Your input got me on the right way...

/Jakob



Thu, 22 Jul 2004 00:42:08 GMT
 tricky CASE WHEN query...
The CASE expression attempts the WHEN clauses in the order they were
written and returns a result on the first TRUE it finds.  I think you
might want to combine some of the statments:

SELECT
(CASE WHEN MAX(S.stamp) <> ''
      THEN (CASE WHEN MAX(S.stamp)
                      > (SELECT MAX(L.stamp)
                           FROM Log AS L
                          WHERE L.user_id = 1)
                 THEN 1 ELSE 0 END)
      ELSE WHEN MAX(S.stamp)IS NULL
           THEN (CASE WHEN (SELECT MAX(L.stamp)
                              FROM log AS L
                             WHERE L.user_id = 1) IS NULL
                      THEN 1 ELSE 0 END)
      END) AS refresh
 FROM status AS S;

This will save an extra test

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 23 Jul 2004 11:05:10 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. tricky case problem

2. tricky join with case

3. The query designer does not support the CASE SQL construct, when using Case in a view

4. Case sensitvie query on case insensitive database.

5. Tricky query for me, should be simple for you

6. Tricky Query Problem

7. A Tuff Tricky One - Please Help with Queries

8. tricky query (for me atleast)

9. Tricky "most recent sales" query

10. Tricky query question.

11. Tricky update query

12. tricky query


 
Powered by phpBB® Forum Software