June 10, 2011 at 9:49 am
I have a table dbo.Listing
( Id VARCHAR(20),
Location VARCHAR(255),
[Status] VARCHAR(6)
)
ID Location Status
1 LocABCD Active
2 LocABCD Closed
3 LocDEFG Closed
4 LocDEFG Active
3401 FOOD & LIQUOR Closed
3422 FOOD & LIQUOR Active
3623 FOOD & LIQUOR Active
3333 SPEED Active
3334 SPEED Closed
3338 SPEED de-Installed
I want the output something like this
Location Info
LocABCD Conversion
LocDEFG Conversion
that is if Locations are equal and ID are different and status of one is active and other is closed then Conversion.
I donot want Food & liquor since it has two active staus even though there is one closed. Also I donot want SPEED since it has de-installed. Anything other than active,closed and repeative active or closed will not be a conversion.
Thanks,
June 10, 2011 at 10:09 am
DECLARE @Listing TABLE(
Id VARCHAR(20),
Location VARCHAR(255),
[Status] VARCHAR(6)
)
INSERT INTO @Listing
VALUES
(1, 'LocABCD', 'Active')
,(2, 'LocABCD', 'Closed')
,(3, 'LocDEFG', 'Closed')
,(4, 'LocDEFG', 'Active')
,(3401, 'FOOD & LIQUOR', 'Closed')
,(3422, 'FOOD & LIQUOR', 'Active')
,(3623, 'FOOD & LIQUOR', 'Active')
,(3333, 'SPEED', 'Active')
,(3334, 'SPEED', 'Closed')
,(3338, 'SPEED', 'de-Ins')
SELECT Location, 'Conversion' AS Info
FROM @Listing AS A
GROUP BY Location
HAVING SUM(CASE [Status] WHEN 'Active' THEN 1 WHEN 'Closed' THEN -1 END) = 0
AND MIN(CASE [Status] WHEN 'Active' THEN 1 WHEN 'Closed' THEN 2 WHEN 'de-Ins' THEN 0 END) > 0
Hope this helps
Gianluca
-- Gianluca Sartori
June 10, 2011 at 10:33 am
Hi PSB,
Try something like:
;with Active as
(
select Location
from dbo.Listing
where status in('Active','Repeating Active')
group by Location, Status
having count(*) = 1
except
select Location
from dbo.Listing
where status = 'de-Installed'
group by Location
),
Closed as
(
select Location
from dbo.Listing
where status in('Closed','Repeating Closed')
group by Location, Status
having count(*) = 1
)
select A.Location, 'Conversion'
from Active as A
intersect
select C.Location, 'Conversion'
from Closed as C
I've not tested this but you get the point 😀
Let me know how it goes
Cheers
Dave
PSB (6/10/2011)
I have a table dbo.Listing( Id VARCHAR(20),
Location VARCHAR(255),
[Status] VARCHAR(6)
)
ID Location Status
1 LocABCD Active
2 LocABCD Closed
3 LocDEFG Closed
4 LocDEFG Active
3401 FOOD & LIQUOR Closed
3422 FOOD & LIQUOR Active
3623 FOOD & LIQUOR Active
3333 SPEED Active
3334 SPEED Closed
3338 SPEED de-Installed
I want the output something like this
Location Info
LocABCD Conversion
LocDEFG Conversion
that is if Locations are equal and ID are different and status of one is active and other is closed then Conversion.
I donot want Food & liquor since it has two active staus even though there is one closed. Also I donot want SPEED since it has de-installed. Anything other than active,closed and repeative active or closed will not be a conversion.
Thanks,
June 10, 2011 at 12:12 pm
Thanks All for the help.
SSC, you code works but it pulls the Food & Liqor.
I want To pull locations only if count of locations = 2 and Ids are not equal and status of one is closed and one is active.
The table has Food & Liqor three times so I dont want that.
June 10, 2011 at 1:26 pm
create table test2
( Id VARCHAR(20),
Location VARCHAR(255),
[Status] VARCHAR(20)
)
drop table test2
insert into test2
select '1','LocABCD','Active'
union all
select '2','LocABCD','Closed'
union all
select '3','LocDEFG','Closed'
union all
select '4','LocDEFG','Active'
union all
select '3401','FOOD & LIQUOR','Closed'
union all
select '3422','FOOD & LIQUOR','Active'
union all
select '3623','FOOD & LIQUOR','Active'
union all
select '3333','SPEED','Active'
union all
select '3334','SPEED','Closed'
union all
select '3338','SPEED','de-Instal'
select location,REPLACE(sumst,0,'Conversion')
from
(
select location, SUM(st) as sumst
from
(
select location, case status
when 'Active' then 1
when 'Closed' then -1
when 'de-instal' then 0
end as st
from test2
where location <> (select location from test2 where status in ('de-Instal'))
)x group by location
) y
where y.sumst = 0
June 10, 2011 at 1:43 pm
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply