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
74 e
and so on
Outcome table
OutomceID name
1 Outcome1
2 Outcome2
..
In my result set i want like this:
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 not all the Possible combinations.
in the above a/b means i want the casecount for which cases both a and b exists for each outcome type.
For one case there mey be possibilty of more than one route.
How to write the query to get those routes combinations case counts.
Kent:What you gave previously will give all possible combinations which have value for atleast one outcome.Now I want only the above combinations only.
Thanks in advance.
Mahima:
How about creating a table of "allowedCombinations" and loading these combinations into the new table. Then join the table to the output of whatever it is you are getting now. Then if you will have the ability to configure "allowedCombinations" taylored to your needs.
|||Hi,
Kent Thank you very much for your reply.
The idea you gave to me is implemented.And I have one simple requirement If the RouteID IS NULL then we have to display Invalid as a row and display counts for each Outcome.
Thanks in advance.
|||Hi Kent,
I created a table with the all possible combinations in one combination i lhave 'Ingestion/Dermal'
but the name string contains 'Dermal/Ingestion'
when i did the comparision like this
Routecte.Namestring(contains 'Dermal/Ingestion') like temptable.routename(here it is 'Ingestion/Dermal'
I am not getting this record.
How to do the comarision or
I need to get the combinations base on RouteID Ingestion is 70 dermal is 75 like that in this order.
Thanks in advance
|||Hi Kent,
When forming the Route combinations I need to get by the order of routeID descending order.
For example:
RouteID name
1 a
2 b
3 c
4 d
5 e
......
all the combinations like the following
a/b, a/c , a/d, b/d, a/b/c from the query what you gave earlier is forming some route combinations like this 'c/a' d/b thats why I am not getting these values when i compare it with another table Allowed combinations which contain a/c and b/d like that.
How to form the Route combinations based on routeid descending order.
Thanks in advance.
|||Mahima:
I am not sure which version of the query you are asking about; can you aim me at what version you mean?
|||I am using this query, but the following query is giving the all possible route combinations which has a value for the Outcome.
with routeCTE
as
( select 1 as depth,
isnull( ex.exMedicalOutcomeId, -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.caCaseId
from exExposureRoute a
inner join exRoute b on a.exRouteId = b.exRouteId
inner join exExposure ex on a.caCaseId = ex.caCaseId
Inner Join caCase c ON c.caCaseID=ex.caCaseID
Inner Join paPatient p ON p.caCaseID=c.caCaseID
Where c.startDate >= @.StartDate AND c.StartDate < @.EndDate
AND c.caStatusID NOT IN(1076,1077)
AND c.PublicID_adOrganization_Secondary IS NULL
And c.caCallTypeID=0 --Exposures
And c.caStatusID=2 --Closed
And p.paSpeciesID=1 --Human
And c.adOrganizationID_Primary in(@.Center)
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( substring(x.nameString,1,3) + '/' + substring(b.[name],1,3) as varchar(210)) as nameString,
case when b.[name] > x.topName then b.[name]
else x.topName
end as topName,
x.caCaseId
from routeCTE x
inner join exExposureRoute a on a.caCaseId = x.caCaseId
inner join exRoute b on a.exRouteId = b.exRouteId
and b.[name] > x.topName
where x.depth <= 3
)
select [Route] as RouteName,
[0] as [No Effect],
[1] as [Minor Effect],
[2] as [Mod Effect],
[3] as [Major Effect],
[4] as [Death],
[5] as [NF Nontoxic],
[ 6] as [NF Minimal],
[7] as [NF Pot_Toxic],
[-1] as [Invalid Missing]
from ( select depth,
OutcomeId,
namestring as Route
-- ar.DisplayName as Route
,routeIDList,
count(*) as caseCount--,ar.Recordid as id
from routeCTE
--inner join Rpt72MedOutComeByRouteExp ar on RouteCTE.NameString=ar.RouteCombination
group by depth
--ar.DisplayName
,namestring,routeIDList,OutcomeId --,ar.Recordid
) q
pivot( sum(caseCount)
for OutcomeId in ([0],[1],[2],[3],[4],[5],[ 6],[ 7],[ 8],[-1])
) piv
order by depth,[0] desc --,id
But I need to get only the below Route combinations only.
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 not all the Possible combinations.
For this you told that Create a table which contain only the Allowed combinations thatswhy I have created the temporary table which contain those tables and comparing that table with our result which contain all possible combinations.
I created a table with the all possible combinations in one combination i lhave 'Ingestion/Dermal'
but the our query result name string contains 'Dermal/Ingestion'
when i did the comparision like this
Routecte.Namestring(contains d/a’ ) like temptable.routename(here it is ‘a/d’ )
I am not getting this record.
This is the problem so is there any way to change the RouteCTE combinations based on RouteID order.
Thanks in advance.
|||
Mahima:
I found the previous version that I was using to simulate your situation. I found that if I modify the CTE such that (1) I add the exRouteID to the CTE and (3) in the RECURSIVE portion if I compare based on exRouteId instead of NAME that it would be ordered by RouteID. You should be able to make a similar modification:
|||with routeCTE
as
( select 1 as depth,
a.exRouteId,
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 exExposureRoute a
inner join exRoute b
on a.exRouteId = b.exRouteId
inner join exposureRoute2 c
on a.caseId = c.caseId
union all
select x.depth + 1 as depth,
a.exRouteId,
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 exExposureRoute a
on a.caseId = x.caseId
inner join exRoute b
on a.exRouteId = b.exRouteId
-- and b.[name] > x.topName
and a.exRouteId > x.exRouteId
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 16 45 74 75 83 58
B 4 16 13 26 15 24
C 3 29 22 14 18 14
C/B NULL 1 1 1 2 1
D 1 17 16 20 28 17
D/A NULL 4 2 4 5 NULL
D/B NULL 3 2 3 3 5
D/C 1 5 2 2 6 4
D/C/B NULL 1 1 1 2 1
*/
Hi Kent,
Thank you very much for your solution.You gave sevaral solutions to my problem. I am very thank ful to you.
Thanks,
Mahima
No comments:
Post a Comment