get status if the cell contains particular value

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 2005 Forums

Transact-SQL (2005)

get status if the cell contains particular value

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 10:35:05

Hello, I have two tables
REGISTER and TILL_ASSIGNMENT
REGISTER looks like


RGST_ID   STAT  NETW_ID     CPMTR_NM
11          2   AAA         Test_Only   
27          2   BBB         VM-Me

TILL_ASSIGNMENT looks like
RGST_ID   EMP_ID   STAT    
27         ZYU     4 
27         TVO     4
27         THE     2
27         A3S     4
11         VMA     9
11         ASP     4
27         VMA     2
11         ACO     2
11         VLI     2

The game rule is once every register has a STAT=4, it is considered as SIGNED ON.
From the table we can see that both 27 and 11 should be "SIGNED ON".
I used the sql query

select R.RGST_ID as WorkstationID,
            CMPTR_NM as DeviceAddress,
            CASE
            WHEN R.STAT=1 THEN 'CLOSED'
            WHEN R.STAT=3 THEN 'DISABLED'
            WHEN R.STAT=2 AND MAX(T.STAT)=2 THEN 'SIGNED OFF'
            WHEN R.STAT=2 AND MAX(T.STAT)=4 THEN 'SIGNED ON'
            ELSE 'SIGNED OFF'
            END AS TerminalStatusCode,
            R.STAT as Status
            FROM REGISTER R LEFT OUTER JOIN TILL_ASSIGNMENT T
            ON R.RGST_ID = T.RGST_ID
            where
            NETW_ID IS NOT NULL
            group by R.RGST_ID, R.CMPTR_NM, R.STAT

The result is


WorkstationID  DeviceAddress  TerminalStatusCode  Status
11                Test_Only     SIGNED OFF          2
27                VM_Me         SIGNED ON           2

Obviously for the register 11 the result is incorrect.
Could you please tell me how to modify the query?
Many thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 11:03:02

Why is register 11 wrong? what is the correct answer when the max(t.stat) = 9?

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 11:06:48

Once T.STAT has a value 4, it should be considered as "SIGNED ON' even when MAX(T.STAT)=9.
I don't know how to exclude the case of MAX(T.STAT)=9.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 11:16:43

Then will this work?


select R.RGST_ID as WorkstationID
       ,CMPTR_NM as DeviceAddress
       ,CASE
            WHEN R.STAT=1 THEN 'CLOSED'
            WHEN R.STAT=3 THEN 'DISABLED'
            WHEN R.STAT=2 AND MAX(T.STAT)=4 THEN 'SIGNED ON'
            ELSE 'SIGNED OFF'
            END AS TerminalStatusCode
       ,R.STAT as Status
FROM   REGISTER R 
LEFT OUTER JOIN TILL_ASSIGNMENT T
       ON R.RGST_ID = T.RGST_ID
       and t.stat = 4
where  NETW_ID IS NOT NULL
group by R.RGST_ID
       ,R.CMPTR_NM
       ,R.STAT

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 11:22:15

Not exactly. Because sometimes if MAX(T.STAT)=2, I need the result as well.
In your query, it doesn't contain it because of T.STAT=4 enforced.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 11:22:25

it seems like to me it is going to the else of that CASE

will this work?

select R.RGST_ID as WorkstationID,
CMPTR_NM as DeviceAddress,
CASE
WHEN R.STAT=1 THEN 'CLOSED'
WHEN R.STAT=3 THEN 'DISABLED'
WHEN R.STAT=2 AND MAX(T.STAT)=2 THEN 'SIGNED OFF'
WHEN R.STAT=2 AND ( MAX(T.STAT)=4 OR MAX(T.STAT)=9) THEN 'SIGNED ON'
ELSE 'SIGNED OFF'
END AS TerminalStatusCode,
R.STAT as Status
FROM REGISTER R LEFT OUTER JOIN TILL_ASSIGNMENT T
ON R.RGST_ID = T.RGST_ID
where
NETW_ID IS NOT NULL
group by R.RGST_ID, R.CMPTR_NM, R.STAT

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 11:24:27

quote:
Originally posted by zhshqzyc

Not exactly. Because sometimes if MAX(T.STAT)=2, I need the result as well.
In your query, it doesn't contain it because of T.STAT=4 enforced.

According to your original query if the max(stat) = 2 then the result is "signed off" anyway which is the same as the ELSE statement.

EDIT:
here is the code I used to test:


use tempdb
go

create table Register (RGST_ID int,  STAT int, NETW_ID char(3), CMPTR_NM varchar(10))
create table TILL_ASSIGNMENT (RGST_ID int, EMP_ID char(3),   STAT int)
go

insert Register
select 11,          2,   'AAA',         'Test_Only' union all
select 27,          2,   'BBB',         'VM-Me'


insert TILL_ASSIGNMENT
select 27,         'ZYU',     4 union all
select 27,        'TVO' ,    4 union all
select 27,         'THE',     2 union all
select 27,         'A3S',     4 union all
select 11,         'VMA',     9 union all
select 11,         'ASP',     4 union all
select 27,         'VMA',     2 union all
select 11,         'ACO',     2 union all
select 11,         'VLI' ,    2


select R.RGST_ID as WorkstationID
       ,CMPTR_NM as DeviceAddress
       ,CASE
            WHEN R.STAT=1 THEN 'CLOSED'
            WHEN R.STAT=3 THEN 'DISABLED'
            WHEN R.STAT=2 AND MAX(T.STAT)=4 THEN 'SIGNED ON'
            ELSE 'SIGNED OFF'
            END AS TerminalStatusCode
       ,R.STAT as Status
FROM   REGISTER R 
LEFT OUTER JOIN TILL_ASSIGNMENT T
       ON R.RGST_ID = T.RGST_ID
       and t.stat = 4
where  NETW_ID IS NOT NULL
group by R.RGST_ID
       ,R.CMPTR_NM
       ,R.STAT

go
drop table Till_Assignment
drop table Register

OUTPUT:
WorkstationID DeviceAddress TerminalStatusCode Status
------------- ------------- ------------------ -----------
11            Test_Only     SIGNED ON          2
27            VM-Me         SIGNED ON          2

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 12:08:17

quote:
Originally posted by yosiasz

it seems like to me it is going to the else of that CASE

will this work?

select R.RGST_ID as WorkstationID,
CMPTR_NM as DeviceAddress,
CASE
WHEN R.STAT=1 THEN 'CLOSED'
WHEN R.STAT=3 THEN 'DISABLED'
WHEN R.STAT=2 AND MAX(T.STAT)=2 THEN 'SIGNED OFF'
WHEN R.STAT=2 AND ( MAX(T.STAT)=4 OR MAX(T.STAT)=9) THEN 'SIGNED ON'
ELSE 'SIGNED OFF'
END AS TerminalStatusCode,
R.STAT as Status
FROM REGISTER R LEFT OUTER JOIN TILL_ASSIGNMENT T
ON R.RGST_ID = T.RGST_ID
where
NETW_ID IS NOT NULL
group by R.RGST_ID, R.CMPTR_NM, R.STAT

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

It doesn't work if all the T.STAT=9 because of no 4. In this case, it should be SIGNED OFF but you set the condition MAX(T.STAT)=9.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 12:11:17

quote:

Originally posted by TG

quote:
Originally posted by zhshqzyc

Not exactly. Because sometimes if MAX(T.STAT)=2, I need the result as well.
In your query, it doesn't contain it because of T.STAT=4 enforced.

According to your original query if the max(stat) = 2 then the result is "signed off" anyway which is the same as the ELSE statement.

EDIT:
here is the code I used to test:


use tempdb
go

create table Register (RGST_ID int,  STAT int, NETW_ID char(3), CMPTR_NM varchar(10))
create table TILL_ASSIGNMENT (RGST_ID int, EMP_ID char(3),   STAT int)
go

insert Register
select 11,          2,   'AAA',         'Test_Only' union all
select 27,          2,   'BBB',         'VM-Me'


insert TILL_ASSIGNMENT
select 27,         'ZYU',     4 union all
select 27,        'TVO' ,    4 union all
select 27,         'THE',     2 union all
select 27,         'A3S',     4 union all
select 11,         'VMA',     9 union all
select 11,         'ASP',     4 union all
select 27,         'VMA',     2 union all
select 11,         'ACO',     2 union all
select 11,         'VLI' ,    2


select R.RGST_ID as WorkstationID
       ,CMPTR_NM as DeviceAddress
       ,CASE
            WHEN R.STAT=1 THEN 'CLOSED'
            WHEN R.STAT=3 THEN 'DISABLED'
            WHEN R.STAT=2 AND MAX(T.STAT)=4 THEN 'SIGNED ON'
            ELSE 'SIGNED OFF'
            END AS TerminalStatusCode
       ,R.STAT as Status
FROM   REGISTER R 
LEFT OUTER JOIN TILL_ASSIGNMENT T
       ON R.RGST_ID = T.RGST_ID
       and t.stat = 4
where  NETW_ID IS NOT NULL
group by R.RGST_ID
       ,R.CMPTR_NM
       ,R.STAT

go
drop table Till_Assignment
drop table Register

OUTPUT:
WorkstationID DeviceAddress TerminalStatusCode Status
------------- ------------- ------------------ -----------
11            Test_Only     SIGNED ON          2
27            VM-Me         SIGNED ON          2

Be One with the Optimizer
TG

But for register 11, if all T.STAT=2, it doesn't include it because of you enforced T.STAT=4.
I want to display all the register status. In the case of all T.STAT=2, only register 27 displays.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 12:26:42

can you create a user function fnTerminalStatusCode that does the checks instead of a case statement in the select or will that just be pushing the problem to a function. because in a function you can do IF and other bitwise comparisons. oh ye show about a bitwise comparison thingy?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 12:39:16

quote:

But for register 11, if all T.STAT=2, it doesn't include it because of you enforced T.STAT=4.
I want to display all the register status. In the case of all T.STAT=2, only register 27 displays.

Then change the left outer join to an INNER JOIN. If that doesn't work then please post enough sample data and desired output to satisfy all your requirments.

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 13:14:55

inner join still not working.
sample data:


insert TILL_ASSIGNMENT
select 27,         'ZYU',     4 union all
select 27,        'TVO' ,    4 union all
select 27,         'THE',     2 union all
select 27,         'A3S',     4 union all
select 11,         'VMA',     2 union all
select 11,         'ASP',     2 union all
select 27,         'VMA',     2 union all
select 11,         'ACO',     2 union all
select 11,         'VLI' ,    2

Desired output


OUTPUT:
WorkstationID DeviceAddress TerminalStatusCode Status
------------- ------------- ------------------ -----------
11            Test_Only     SIGNED OFF         2
27            VM-Me         SIGNED ON          2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 13:25:29

With your new data and my original solution the actual output matches your desired output:


actual output:
WorkstationID DeviceAddress TerminalStatusCode Status
------------- ------------- ------------------ -----------
11            Test_Only     SIGNED OFF         2
27            VM-Me         SIGNED ON          2

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:27:42

so you want it to be SIGNED ON if there's at least one occurance of TSTAT=4?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 13:37:18

quote:
Originally posted by visakh16

so you want it to be SIGNED ON if there's at least one occurance of TSTAT=4?

YES.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:43:28

and similarly atleast one TSTAT=1 closed,...

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-23 : 13:46:52

Yes. Thanks TG.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 13:48:00

no sweat

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:49:46

and can there be case where you've two records with TSTAT=1 & 4 in that case what should be status? how is priority given?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 13:55:16

quote:
Originally posted by visakh16

and can there be case where you've two records with TSTAT=1 & 4 in that case what should be status? how is priority given?

If i understand correctly the stat=1 would be from the R table. So once a Register row goes (closed or disabled) then it doesn't matter what's in the T table.

Be One with the Optimizer
TG

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-28 : 17:10:02

I come back because of condition changed.
The requirement becomes:
Once one occurance of T.STAT=4 then 'SIGNED ON'.
For T.STAT=5,8,7,2 then 'SIGNED OFF'.
For T.STAT=NULL,3,1 then 'CLOSED'.

Thanks for any help.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources