Combinations

Hi,
I have the table:
exexposureRoute: with columns
caseId, exRouteID
200 70
300 71
200 72
200 73
.. ..

Route table:
exRoteID name
70 a
71 b
72 c
73 d
.. ..
In my result set i want like this:
Route CaseCount
a 78
b 89
c 90
d 345
a/b 9
a/c 8
a/d 9
a/b/c 2
Otherroute comb 98
in the above a/b means i want the casecount for which cases both a and b exists.
For one case there mey be possibilty of more than one route.

How to write the query to get those routes combinations case counts.
Thnaks in advance

Mahima:

I am assuming that because you have an entry of 'a/b/c' = 2 that you should also have an entry of 'b/c' = 2; is that true?

Dave

|||

Mahima:

I put together a mock-up of your tables and started piecing together a query that might work; however, I am still not sure of the status of whether or not the two 'B/C' records should be included. My query seems to work if these records need to be included. Here is my first-pass attempt with the results. I have made no attempt to optimize this because I am not sure that I understand the objective:

with routeCTE
as
( select 1 as depth,
cast (convert (char(10), a.exRouteId) as char (100)) as routeIdList,
cast (b.[name] as varchar(210)) as nameString,
b.name as topName,
caseId
from exposureRoute a
inner join route b
on a.exRouteId = b.exRouteId
union all
select x.depth + 1 as depth,
cast (substring (x.routeIdList, 10*x.depth-9, 10)
+ convert (char(10), a.exRouteId) as char (100))
as routeIDList,
cast( x.nameString + '/' + b.[name] as varchar(210)) as nameString,
case when b.[name] > x.topName then b.[name]
else x.topName
end as topName,
x.caseId
from routeCTE x
inner join exposureRoute a
on a.caseId = x.caseId
inner join route b
on a.exRouteId = b.exRouteId
and b.[name] > x.topName
where x.depth <= 3
)
select q.[Route],
caseCount
from ( select depth,
nameString as Route,
count(*) as caseCount
from routeCTE
group by depth,
nameString
) q
order by q.depth,
q.[Route]

-- -- Sample Output: --

-- Route caseCount
-- - --
-- A 78
-- B 89
-- C 90
-- D 345
-- A/B 9
-- A/C 8
-- A/D 9
-- B/C 2
-- A/B/C 2

|||

I think that this line

cast (convert (char(10), a.exRouteId) as char (100)) as routeIdList

and this set of lines

cast (substring (x.routeIdList, 10*x.depth-9, 10)
+ convert (char(10), a.exRouteId) as char (100))
as routeIDList,

are not needed.

Dave

|||

Hi Waldrop,

I have another table Exposure which contain the caseID,OutcomeID like the following,

CaseID OutcomeID

200 1

201 2

202 3

200 2

Now I need to modify the result which I am getting from the above query like the following

Route Outcome:1 Outcome:2 Outcome:3 ..

a 20(casecount) 30 4

b 30 3 32

a/b 7 6 2

..

Now we need to display the case count according to the OutcomeID:1

How do we need to modify the query in order to get the case Counts divided based on Outcome.

Thanks in advance.

|||

Hello, Mahima:

I mocked up the second exposureRoute table with this:

create table dbo.exposureRoute2
( CaseId integer,
OutcomeId integer,
constraint pk_exposureRoute2
primary key (CaseId, OutcomeId)
)
go

insert into exposureRoute2
select distinct
caseId,
cast(1 + 2.9999999*dbo.rand() as tinyint)
from exposureRoute
go

The RAND() scalar function can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

I was able to modify the previous procedure to:

with routeCTE
as
( select 1 as depth,
c.OutcomeId,
cast (convert (char(10), a.exRouteId) as char (100)) as routeIdList,
cast (b.[name] as varchar(210)) as nameString,
b.name as topName,
a.caseId
from exposureRoute a
inner join route b
on a.exRouteId = b.exRouteId
inner join exposureRoute2 c
on a.caseId = c.caseId
union all
select x.depth + 1 as depth,
OutcomeId,
cast (substring (x.routeIdList, 10*x.depth-9, 10)
+ convert (char(10), a.exRouteId) as char (100))
as routeIDList,
cast( x.nameString + '/' + b.[name] as varchar(210)) as nameString,
case when b.[name] > x.topName then b.[name]
else x.topName
end as topName,
x.caseId
from routeCTE x
inner join exposureRoute a
on a.caseId = x.caseId
inner join route b
on a.exRouteId = b.exRouteId
and b.[name] > x.topName
where x.depth <= 3
)
select [Route],
[1] as [Outcome1],
[2] as [Outcome2],
[3] as [Outcome3]
from ( select depth,
OutcomeId,
nameString as Route,
count(*) as caseCount
from routeCTE
group by depth,
nameString,
outcomeId
) q
pivot( sum(caseCount)
for OutcomeId in ([1],[2],[3])
) piv
order by depth, Route

-- Route Outcome1 Outcome2 Outcome3
-- -- -- --
-- A 32 31 31
-- B 37 32 28
-- C 37 32 28
-- D 104 120 125
-- A/B 7 5 5
-- A/C 3 7 5
-- A/D 5 5 3
-- B/C 2 2 1
-- A/B/C 2 2 1

Please let me know how it turns out.

|||

Hi Kent,

It is working fine.

select [Route],
[1] as [Outcome1],
[2] as [Outcome2],
[3] as [Outcome3]
Can we change this line I have the names corresponding to the each OutcomeID in the Outcome table which has

Outcome Table:

OutcomeID Name

0 yyy

1 No Effect

2 zz

3 aaaaa

4 uuuu

Like that.Can we get the Outcome names from this table and display count for each. I can do like this

[0] as [yyy],

[1] as [No Effect],

[2] as [zz],

[3] as [aaaaa]

[4] as [uuuu]

But in future if the Outco me table is chnaged we need to change this stored procedure also.Can we automeate this one?

And also one minor requirement is the result need to be order by RouteID in ascending order.

Thanks very much for your help.

|||

Mahima:

Is this the way that you want your example ordered:

-- Route Outcome1 Outcome2 Outcome3
-- -- -- --
-- A 32 31 31
-- A/B 7 5 5
-- A/B/C 2 2 1
-- A/C 3 7 5
-- A/D 5 5 3
-- B 37 32 28
-- B/C 2 2 1
-- C 37 32 28
-- D 104 120 125

|||

Hi Kent,

Yeah , But I need to display Outcome names In your code you are coding the Outcome1, Outcome2 .. like this but in our data base we have those names coming from the Outcome table whic contain OutcomeID, Name.Now I need to display Oucome name from that table.In the code I can code the names for the corresponding OutcomeIDs but the Problem is if in future if the Outcome table changed we need to change this last part of code.

If OutcomeID is null then it need to be coded as Invalid.

Thanks in advance.

|||If OutcomeID is null then it need to be coded as Invalid.|||

with routeCTE
as
( select 1 as depth,
isnull(c.OutcomeId, -1) as OutcomeId,
cast (convert (char(10), a.exRouteId) as char (100)) as routeIdList,
cast (b.[name] as varchar(210)) as nameString,
b.name as topName,
a.caseId
from exposureRoute a
inner join route b
on a.exRouteId = b.exRouteId
inner join exposureRoute2 c
on a.caseId = c.caseId
union all
select x.depth + 1 as depth,
OutcomeId,
cast (substring (x.routeIdList, 10*x.depth-9, 10)
+ convert (char(10), a.exRouteId) as char (100))
as routeIDList,
cast( x.nameString + '/' + b.[name] as varchar(210)) as nameString,
case when b.[name] > x.topName then b.[name]
else x.topName
end as topName,
x.caseId
from routeCTE x
inner join exposureRoute a
on a.caseId = x.caseId
inner join route b
on a.exRouteId = b.exRouteId
and b.[name] > x.topName
where x.depth <= 3
)
select [Route],
[-1] as [INVALID],
[0] as [yyy],
[1] as [No Effect],
[2] as [zz],
[3] as [aaaaa],
[4] as [uuuu]
from ( select depth,
OutcomeId,
nameString as Route,
count(*) as caseCount
from routeCTE
group by depth,
nameString,
outcomeId
) q
pivot( sum(caseCount)
for OutcomeId in ([-1],[0],[1],[2],[3],[4])
) piv
order by Route

-- Route INVALID yyy No Effect zz aaaaa uuuu
-- - - - -- -- -
-- A 10 11 21 13 18 23
-- A/B 2 3 5 1 3 4
-- A/B/C NULL 1 1 NULL 1 2
-- A/C 2 2 2 1 3 5
-- A/D NULL 2 5 1 2 4
-- B 8 20 22 13 14 21
-- B/C NULL 1 1 NULL 1 2
-- C 4 19 16 15 18 25
-- D 11 77 60 68 65 69

|||

Hi Kent,

Thank you very much for your help.

Is there any way to change this code to get names from the Outcome table instead of coding here.

select [Route],
[-1] as [INVALID],
[0] as [yyy],
[1] as [No Effect],
[2] as [zz],
[3] as [aaaaa],
[4] as [uuuu]

And also Order by RouteID .

Thanks.|||I think you would need to use dynamic SQL to do that. I can't think of any good way of doing it otherwise. Sorry.

Can somebody make a suggestion here?|||

Hi Kent,

I have one question in the above requirement.In the query what you gave whether we can specify conditions for the some route combinations only like I need

Routes Outcome1 Outcome2 Outcome3

a

b

c

d

e

f

g

h

a/c

a/c/b

b/c/e

a/e

a/b

a/d

a/b/d

a/b/e

a/d/e

a/b/d/e

b/e

b/d

b/d/e

d/e

I need to get only those combinations.How to get only that combinations IS it possible to give names to the combinations like

in original My Route names are

a-Ingestion

b-Inhalation/Nasal

c-Aspiration(with Ingestion)

d-Ocural

They are displsplaying the combinations like this:

a/c -->Ing/Asp

a/b -->Ing/Inh

Can we able to give names like that for combinations to your query.

Thank you very much for your help.