December 5, 2007 at 3:03 pm
I have what should be simple, but maybe someone can take me in the right direction.
Here is the problem:
One table: BaseOutlet
Has four columns that make up the Primary Key:
BaseId int,
OUTLID int,
MKTID int,
RACID bit
The application has the "BaseId", but the data is sending duplicates in a way. The "RACID" can be "0" or "1" and nothing else. There can be two records possible that end up like this:
BaseId = 1
OUTLID = 2
MKTID = 1
RACID = 1
And another record with:
BaseId = 1
OUTLID = 2
MKTID = 1
RACID = 0 <-- only difference
In this query I want to exclude the 0 record if a 1 is present, but if no 1 is present then send back the 0.
There is a lot of records so I wanted to see if this could be done in a query and not a loop/cursor.
Maybe I need some sleep, but I thought this would be simple.
:w00t:
script to help in testing:
CREATE TABLE BaseOutlet (
CaseId int,
OUTL_ID int,
MKT_ID int,
TAC_ID bit)
INSERT INTO BaseOutlet
VALUES (1,2,1,0)
INSERT INTO BaseOutlet
VALUES (1,2,1,1)
--I don't want both the records above to show up in the query
INSERT INTO BaseOutlet
VALUES (3,4,1,0)
INSERT INTO BaseOutlet
VALUES (5,6,1,1)
--the above two can show up
December 5, 2007 at 4:03 pm
select A.*
from BaseOutlet A
join (select CaseId, OUTL_ID, MKT_ID, max(cast(TAC_ID as tinyint)) as TAC_ID
from BaseOutlet
group by CaseId, OUTL_ID, MKT_ID) DT on A.CaseId = DT.CaseId and A.OUTL_ID = DT.OUTL_ID and A.MKT_ID=DT.MKT_ID and A.TAC_ID = DT.TAC_ID
Results
CaseId OUTL_ID MKT_ID TAC_ID
1 2 1 1
3 4 1 0
5 6 1 1
December 5, 2007 at 4:55 pm
Awesome....thanks Ray
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply