Combinations

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

200 71

200 72
.. ..
Route table:
exRoteID name
70 a
71 b
72 c
73 d
.. ..
In my result set i want like this:
Route CaseCount
a 1 (200 has route 70)

b 1 (300 has route 71)

c 1
d 1
a/b 1(200 has both 70,71)
a/c 0
a/d 0
a/b/c 1(200 has both 70,71,72)

in the above a/b means i want the casecount for which cases both routes 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

Is this similar to the problem we worked on in January?

This looks like the same data; is this basically just a different report than the other one?

Also, why are there results for:

a/c and a/d

but not:

b/c, b/d, c/d, b/c/d and a/b/c/d?

|||

Hi,

How you solved your problem?

Thanks in advance

|||

Hi,

In the old report they are showing the some combinations only.At Present I am getting the combinations like this I wrote a function to which iam passing the Routes suppose we need combinations of 4 routes we are sending like this

funGetRouteCount(Route1,Route2,Route3,Route4,NoOfRoutes)

{

Select @.RCount= Count(*) from Table1

join ..

Group by table1.cacaseID

Having Count(distinct table2.exRouteID)=@.noOfRoutes

return RCount

}

Iam calling this function like this

declare @.routeTab table

(

routeID1 int,

routeID2 int,

routeID3 int,

routeID4 int,

routeName varchar(50),

noOfRoutes int,

RouteID int

)

insert into @.routeTab values (70,72,0,0, 'a/b',2,10245)

insert into @.routeTab values (70,71,72,0 ,'a/b/c',3,10246)

insert into @.routeTab values (70,71,74,0, 'a/b/d',3,10247)

elect 2,RouteID,routeName as exRoute,dbo.funGetRouteCount(routeID1,routeID2, routeID3, routeID4,0,noOfRoutes,@.StartDate,@.EndDate,@.Center),

dbo.funGetRouteCount(routeID1,routeID2, routeID3, routeID4,1,noOfRoutes),

dbo.funGetRouteCount(routeID1,routeID2, routeID3, routeID4,2,noOfRoutes) ,

It is working perfectly but taking some time to give the result because getting the result from the function.

Any better approach is available.

Thanks in advance

|||Are you running SQL 2000 or SQL 2005?|||

SqlServer 2005

thanks

|||

UNCLE!

I have 140 lines of what looks like dreadful code that I am not willing to post. The output that I get is:

-- routeString lineCount description
-- -- --
-- A 1 (200 has route 70)
-- A/B 1 (200 has routes 70,71)
-- A/B/C 1 (200 has routes 70,71,72)
-- A/B/D 0
-- A/C 1 (200 has routes 70,72)
-- A/C/D 0
-- A/D 0
-- B 1
-- B 1 (200 has route 71)
-- B/C 1 (200 has routes 71,72)
-- B/C/D 0
-- B/D 0
-- C 1 (200 has route 72)
-- C/D 0
-- D 1

Right now I am fried from this; hopefully someone else can give you a hand. I am quitting on this for the night and I will compare what I have to what you posted in the morning so that I can try to see where I am going wrong.

:-(

|||

Mahima:

I am afraid that I still don't understand what you want. I also looked at your post in January:

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

I will give you what I have but like in January I think I have missed the target. Sorry that I can't help more. If the output that I posted last night looks better I will post that version. Sorry for adding to confusion:

--
-- table @.caseCount is used to store the number of exRouteIDs associated
-- with each particular caseID
--
declare @.caseCount table
( caseId integer,
caseCount integer
)

insert into @.caseCount
select caseId,
count(*) as caseCount
from exposureRoute
group by caseId

declare @.prm_routeCount integer
set @.prm_routeCount = ( select max(caseCount) from @.caseCount )

declare @.universe table
( routeCount integer,
routeString varchar(110),
routeList varchar(120)
)

--
-- This set gets a list of all possible route combinations. This is
-- probably not a necessary step.
--
-- This routine builds two fixed-length string lists:
-- routeString: This is the list of route names such as 'a/b/c'
-- elements are also seperated by '/' characters
-- routeList: This is a list of the exRouteID keys
-- elements are also separated by a comma
--
-- Use a REPLACE function to eliminate blank filler characters from
-- each string list.
--
-- The data for this is stored in the @.universe table
--
;with allRoutes
as
( select 1 as routeCount,
exRouteID as lastRouteId,
cast(cast ([name] as char(10)) as varchar(110)) as routeString,
cast(right(' ' + convert (varchar(11), exRouteId), 11) as varchar(120))
as routeList
from route
union all
select routeCount + 1 as routeCount,
b.exRouteId,
cast(routeString + '/' + cast ([name] as char(10)) as varchar(110)),
cast(routeList + ',' + right(' ' +
convert (varchar(11), exRouteId), 11) as varchar(120))
from allRoutes a
inner join route b
on a.lastRouteId < b.exRouteId
and routeCount < @.prm_routeCount
)
insert into @.universe
select routeCount,
routeString,
routeList
from allRoutes

--
-- The @.details table is used to store summary information for each
-- simple route and to store detail information for each complex route.
--
-- A "containsList" string is is generated as part of the @.details
-- table. This is not necessary; however, it does seem to simplify the
-- output select statement by factoring the "containsList" up to this
-- point rather than dealing with it later.
--
-- Also note, that this routine uses an iterator table. A setup for the
-- iterator table can be found here:
--
-- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1
--
declare @.details table
( routeCount integer,
caseCount integer,
caseId integer,
routeString varchar (110),
routeList varchar (120),
containsList varchar (120)
)

insert into @.details
select routeCount,
count(distinct b.exRouteId) as caseCount,
caseId,
routeString,
routeList,
containsList
from ( select routeString,
routeCount,
iter,
routeList,
convert(integer, substring (routeList, 12*iter-11, 11))
as exRouteId,
replace(routeList, ' ', '') as containsList
from @.universe a
inner join small_iterator (nolock)
on iter <= @.prm_routeCount
and iter <= routeCount
) a
left join exposureRoute b
on a.exRouteId = b.exRouteId
group by routeString,
caseId,
routeList,
routeCount,
containsList
having count(distinct b.exRouteId) = routeCount
order by routeString,
caseId,
routeList,
routeCount

--
-- This is select that returns the output.
--
select left(replace(routeString, ' ', ''), 10) as routeString,
lineCount,
left (description, 30) as description
from ( select routeString,
case when a.routeCount = 1 and b.caseCount = 1
then ''
when a.routeCount = 1 and b.caseCount > 1
then '(' + convert(varchar(11),a.caseId) +
' has route ' + containsList + ')'
else '(' + convert(varchar(11),a.caseId) +
' has routes ' + containsList + ')'
end as Description,
routeCount,
b.caseCount,
a.caseId,
count(*) as lineCount
from @.details a
inner join @.caseCount b
on a.caseId = b.caseId
group by routeString,
routeCount,
b.caseCount,
a.caseId,
case when a.routeCount = 1 and b.caseCount = 1
then ''
when a.routeCount = 1 and b.caseCount > 1
then '(' + convert(varchar(11),a.caseId) +
' has route ' + containsList + ')'
else '(' + convert(varchar(11),a.caseId) +
' has routes ' + containsList + ')'
end
) x
order by routeString,
caseCount,
caseId

-- routeString lineCount description
-- -- -- -
-- A 1 (200 has route 70)
-- A/B 1 (200 has routes 70,71)
-- A/B/C 1 (200 has routes 70,71,72)
-- A/C 1 (200 has routes 70,72)
-- B 1
-- B 1 (200 has route 71)
-- B/C 1 (200 has routes 71,72)
-- C 1 (200 has route 72)
-- D 1

I still think this looks dreadful.

:-(

|||

I can't get your data to fully match with the expected results.
If a/b/c has a route then doesn't a/c? Also is CaseCount only ever one
or zero? You're better off providing more precise sample data and results
if you want a better response.

set nocount on

create table exposureRoute(caseId int, exRouteID int)
insert into exposureRoute(caseId , exRouteID )
select 200 , 70 union all
select 300 , 71 union all
select 200 , 72 union all
select 202 , 73 union all
select 200 , 71 union all
select 200 , 72

create table Route(exRouteID int, name char(1))
insert into Route(exRouteID, name)
select 70 , 'a' union all
select 71 , 'b' union all
select 72 , 'c' union all
select 73 , 'd';

with exposureRouteName(caseId , exRouteID, name) as
(select distinct e.caseId,
r.exRouteID,
r.name
from exposureRoute e
inner join Route r on r.exRouteID=e.exRouteID),
RecurRoute(name, caseId, ConcatName,ConcatRouteID,CaseCount) as
(select name,
caseId,
cast(name as varchar(max)),
cast(exRouteID as varchar(max)),
1
from exposureRouteName
union all
select r.name,
r.caseId,
c.ConcatName + '/' + r.name,
c.ConcatRouteID + ',' + cast(r.exRouteID as varchar(max)),
case when r.caseId=c.caseId then CaseCount else 0 end
from exposureRouteName r
inner join RecurRoute c on r.name>c.name),
Results(Route,CaseCount,Description,rn) as
(select ConcatName,
CaseCount,
case when CaseCount > 0 then '('+cast(caseId as varchar(10))+' has route '+ConcatRouteID+')'
else '' end,
row_number() over(partition by ConcatName order by CaseCount desc)
from RecurRoute)
select Route,
CaseCount,
Description
from Results
where CaseCount>0 or rn=1
order by len(Route),Route

|||

All right; thank you for picking me up, Mark. :-)

And I agree with your comments on the test results versus test data.

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.

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.

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.