June 23, 2010 at 7:58 am
HI,
I need help writing a query. Where in a table ABC....
there is code column codes (ab, bc, cd, de, etc), and there is status column with values(open, close)
I need to get the count of codes(bc, cd, de) where the codes(ab) status(close)
Table abc
ID codes status
1 ab cls
2 bc cls
3 cd cls
4 de cls
5 bc cls
6 cd cls
There are multiple codes(ab), but for one case there is only one (ab) and a single case can have multiple records of (bc, cd, de) so here I want to count the multiple codes (bc, cd, de) for a case in which the code(ab) has status (cls)...Hope I Made it understandable....
Thanks in Advance
June 23, 2010 at 8:01 am
Please proved clearer "Desired Output" ; it is very hard to arrive at what you want for your post,mate...
June 23, 2010 at 8:07 am
ssc_san (6/23/2010)
...I need to get the count of codes(bc, cd, de) where the codes(ab) status(close)
...
That query does exactly what you have asked for:
SELECT codes, count(*)
FROM ABC
WHERE codes in ('bc', 'cd', 'de')
and codes = 'ab' and status and status = 'cls'
Surprised in no results? Please ask your question properly, provide create table script, some sample data insert script and clearly show expected results. Otherwise, it will be hard to help you.
June 23, 2010 at 12:10 pm
Do you have the column for CaseID...? Please specify your requirement... Assuming you have column for caseID I am providing below T-SQL ....
DECLARE @CaseID int
set @CaseID = 111
DECLARE @test-2 TABLE (
id int identity (1,1)
,CaseID int
,Code char(2) null
,Status varchar (10) null
)
Insert Into @test-2 select 111,'ab','closed' union all select 111,'ab','Open' union all select 111,'bc',''
union all select 111,'cd','' union all select 111,'cd',''
select * From @test-2
select * from
(
select t.code as Code
from @test-2 t where exists (select * from @test-2 where code = 'ab' and caseid = @caseid and status = 'Closed')
) datatable
PIVOT
(
COUNT(Code)
FOR code in ([bc],[cd])
) pivottable
FO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply