I am trying to write a query.
If I activated for eg.
1) a tag in a vehicle as below ,
2) and then inactivated the same tag from the vehicle ,
3) and activated another tag in the same vehicle.
My Query should pull, that vehicle as tagless if there is no active tag associated with that vehicle.
If there is both inactive and active tag, then it shouldnt pull it as tagless vehicle.
How to write a query
CREATE tags ( acct_id number
, veh_id NUMBER
, tag_id NUMBER
, tag_sts VARCHAR2(10) );
-- Activated tag
INSERT INTO tags VALUES (1001,111, 1, 'ACTIVE');
2)-- Then I Inactivated tag
UPDATE TAGS T
SET T.TAG_STS = 'INACTIVE'
WHERE T.TAG_ID = 1;
3)--activated tag to the same vehicle and added one more tag to new vehicle.
We can have multiple vehicle in the same account
INSERT INTO tags VALUES ( 1001 ,111 , 2, 'ACTIVE');
INSERT INTO tags VALUES (1001,222, 2, 'ACTIVE');
Thanks for posting the CREATE TABLE and INSERT statements. Please test and, if necessary, correct them before posting. There's an error in the CREATE statement.
Here's one way to do what you requested:
WHEN 0 = COUNT (CASE WHEN tag_sts = 'ACTIVE' THEN 1 END) OVER (PARTITION BY veh_id)
END AS label
FROM tags t
ORDER BY veh_id
I hope this answers your question.
If not, point out where the query above is giving the wrong results, and explain, using specific examples, how you get the correct results from the given data in those places. If you changed the query at all, post your code.