February 27, 2014 at 11:18 am
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the 1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1
where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc. Again, the series are usually discontinuous and specific to each specialty.
I'm sorry this is so convoluted. If I need to explain it more succinctly or intelligibly please let me know.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values ([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
--,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_13]),([2_14])
--,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8])
--,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16])
--,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
--,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
--,([8_4]),([8_7])
--,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_8])
--,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
--,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
--,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
--,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
--,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 1
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription
,[1_1], [1_2], [1_3], [1_4], [1_5], [1_6], [1_7], [1_8]
,round((convert(float,cnt)/convert(float,8))*100.00,1) as percentInPlace
from #tempAllergy
order by 3
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran. I know there's a better way but I'm not there yet in my knowledge.
Thanks in advance for your help!
February 28, 2014 at 7:01 am
From an earlier post: "Here's a table of the possible combinations of specialties and capabilities (the 'YES' column should be ignored):"
Do you have a table which looks similar to what you posted under the other thread?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 28, 2014 at 10:18 am
Yes, this is a continuation of the thread you helped me with a few days ago. I had another post yesterday that I deleted because it was too convoluted to expect anyone to read and respond to. Also, I hadn't been specific enough. In that deleted post I cited my earlier post and your answer. Sorry I didn't do that in this one.
THANKS CRISM@WORK for responding again. I really appreciate it.
In in the process of doing what I need by hand (at least part of it). Here's the code for one specialty (I have 22 to do):
--Cardiology
if object_id('tempdb..#temp1') is not null
drop table #temp1
select distinct
specialty,
capnumber,
convert(int,dbo.ReplaceNonNumericChars(capnumber)) as 'a',
capforthisspecialty,
POPracticeUnitCode,
POPracticeUnitDescription,
specialtyredux,
([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16]),([4_18])
,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,([8_4]),([8_7])
,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_7]),([9_8])
,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
into #temp1
from CapYesForSpec c
inner join winter_2014uto w
on c.specialty = w.specialtyredux
where c.specialty = 'Cardiology'
AND capforthisspecialty = 'YES'
union all
select distinct
specialty,
capnumber,
convert(int,dbo.ReplaceNonNumericChars(capnumber)) as 'a',
capforthisspecialty,
POPracticeUnitCode,
POPracticeUnitDescription,
specialtyredux,
([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16]),([4_18])
,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,([8_4]),([8_7])
,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_7]),([9_8])
,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
from CapYesForSpec c
inner join winter_2014uto w
on c.specialty = w.specialtyredux
where c.specialty = 'Cardiology'
AND capforthisspecialty = 'YES'
and capnumber in (select distinct capnumber from CapYesForSpec where specialty = 'Cardiology')
---*******************************
select * from #temp1
select specialty
--, count(a)
, capnumber
--, a,capforthisspecialty
,POPracticeUnitCode
, POPracticeUnitDescription,
--, specialtyredux
([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16]),([4_18])
,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,([8_4]),([8_7])
,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_7]),([9_8])
,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
from #Temp1
group by Specialty,capNumber,POPracticeUnitDescription,POPracticeUnitCode,
([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16]),([4_18])
,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,([8_4]),([8_7])
,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_7]),([9_8])
,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
order by specialty, POPracticeUnitDescription
--,a
if object_id('tempdb..#temp2') is not null
drop table #temp2
select specialty
,capNumber
,POPracticeUnitCode
, POPracticeUnitDescription,
([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16]),([4_18])
,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,([8_4]),([8_7])
,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_7]),([9_8])
,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
into #temp2
from #temp1
select * from #temp2 #t2
inner join eligphysUTO epUTO
on #t2.POPracticeUnitDescription = epUTO.[practice name]
if object_id('tempdb..#temp2a') is not null
drop table #temp2a
--select * from #temp2
--select distinct POPracticeUnitDescription, specialty from #temp2a
--select * from eligphysUTO
select *
into #temp2a
from #temp2 #t2
inner join eligphysUTO epUTO
on #t2.POPracticeUnitDescription = epUTO.[practice name]
where specialtyredux = 'Cardiology'
--___________________________________________________________________________
--change denom as needed below
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2a T
cross apply (
select Count(*)
from (values ([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
,([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16]),([4_18])
,([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,([8_4]),([8_7])
,([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_7]),([9_8])
,([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])) C (Val)
where Val = 'YES'
) A (Cnt)
order by POPracticeUnitDescription
select distinct *, POPracticeUnitDescription,round((convert(float,cnt)/convert(float,116))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values ([1_1]),([1_2]),([1_3]),([1_4]),([1_5]),([1_6]),([1_7]),([1_8])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 1
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription
,[1_1], [1_2], [1_3], [1_4], [1_5], [1_6], [1_7], [1_8]
,round((convert(float,cnt)/convert(float,8))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
go
drop table #tempCardiology
go
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 2
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([2_1]),([2_2]),([2_3]),([2_4]),([2_5]),([2_6]),([2_7]),([2_8]),([2_9]),([2_11]),([2_12]),([2_13]),([2_14])
,round((convert(float,cnt)/convert(float,13))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 3
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([3_1]),([3_2]),([3_3]),([3_4]),([3_5]),([3_8]),([3_11]),([3_12])
,round((convert(float,cnt)/convert(float,8))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 4
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([4_1]),([4_2]),([4_3]),([4_4]),([4_5]),([4_6]),([4_7]),([4_8]),([4_9]),([4_11]),([4_12]),([4_13]),([4_14]),([4_15]),([4_16])
,round((convert(float,cnt)/convert(float,15))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 5
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([5_1]),([5_2]),([5_3]),([5_4]),([5_5]),([5_6]),([5_7]),([5_9])
,round((convert(float,cnt)/convert(float,8))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 6
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([6_1]),([6_2]),([6_3]),([6_4]),([6_5]),([6_6]),([6_7]),([6_8]),([6_9])
,round((convert(float,cnt)/convert(float,9))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([8_4]),([8_7])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 8
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([8_4]),([8_7])
,round((convert(float,cnt)/convert(float,2))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_8])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 9
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([9_1]),([9_2]),([9_3]),([9_4]),([9_5]),([9_6]),([9_8])
,round((convert(float,cnt)/convert(float,7))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 10
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([10_1]),([10_2]),([10_3]),([10_4]),([10_5]),([10_6]),([10_7]),([10_8])
,round((convert(float,cnt)/convert(float,8))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 11
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([11_1]),([11_2]),([11_3]),([11_4]),([11_5]),([11_6]),([11_8])
,round((convert(float,cnt)/convert(float,7))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 12
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([12_1]),([12_2]),([12_3]),([12_4]),([12_5]),([12_6]),([12_7]),([12_8]),([12_9]),([12_11]),([12_12])
,round((convert(float,cnt)/convert(float,11))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 13
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([13_1]),([13_2]),([13_3]),([13_4]),([13_5]),([13_6]),([13_7]),([13_8]),([13_9])
,round((convert(float,cnt)/convert(float,9))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
--___________________________________________________________________________
if object_id('tempdb..#tempCardiology') is not null
drop table #tempCardiology
go
--select * from #tempCardiology
select *
into #tempCardiology
from
dbo.#temp2 T
cross apply (
select Count(*)
from (values
([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
) C (Val)
where Val = 'YES'
) A (Cnt)
where left(CapNumber,charindex('_', CapNumber)-1) = 14
order by POPracticeUnitDescription
select distinct specialty,POPracticeUnitCode, POPracticeUnitDescription,
([14_1]),([14_2]),([14_3]),([14_4]),([14_5]),([14_6]),([14_7]),([14_8]),([14_9])
,round((convert(float,cnt)/convert(float,9))*100.00,1) as percentInPlace
from #tempCardiology
order by 3
I bet the code is too long and will bog down the system again but I'm not sure how to shorten it and make it clear.
My goal from the last post was to get results (and past into Excel but that's a future portion I'll try to automate) for each specialty and how many "yes" in each of the specific list of capabilities ([1_1], [1_2]...{14_10]) but discontinuous and specific to each specialty. Then I would take these counts and divide by the total number of capabilities for each specialty (example: 110 for Allergy). Now they want each group for each specialty. They call a group "domains of function". All the 1_, 2_,...,14_ go together in a group. No 7_ group for any specialty. So the code I posted does it by hand. I have to copy and paste each of the last 13 result sets from SQL Server horizontally across the worksheet I have for each specialty.
I've immersed in this for the last 7 days so I'm not sure if I'm explaining this enough or if I'm leaving crucial details out. I also realize the length of my post is quite off-putting. Not sure how to condense it though.
Would using a stored procedure and passing in a table be possible. I have very little experience with stored procedures though. I would love to automate this before I have to do it again in three months. Even a pointer in the right direction would be great!
Thanks!!
February 28, 2014 at 10:42 am
Here are the columns for winter_2014uto:POPracticeUnitCode
POPracticeUnitDescription
specialtyredux
[1_1]
[1_2]
[1_3]
[1_4]
[1_5]
[1_6]
[1_7]
[1_8]
[2_1]
[2_2]
[2_3]
[2_4]
[2_5]
[2_6]
[2_7]
[2_8]
[2_9]
[2_10]
[2_11]
[2_12]
[2_13]
[2_14]
[2_15]
[2_16]
[2_17]
[2_18]
[3_1]
[3_2]
[3_3]
[3_4]
[3_5]
[3_6]
[3_7]
[3_8]
[3_9]
[3_10]
[3_11]
[3_12]
[3_13]
[4_1]
[4_2]
[4_3]
[4_4]
[4_5]
[4_6]
[4_7]
[4_8]
[4_9]
[4_10]
[4_11]
[4_12]
[4_13]
[4_14]
[4_15]
[4_16]
[4_17]
[4_18]
[5_1]
[5_2]
[5_3]
[5_4]
[5_5]
[5_6]
[5_7]
[5_8]
[5_9]
[6_1]
[6_2]
[6_3]
[6_4]
[6_5]
[6_6]
[6_7]
[6_8]
[6_9]
[8_4]
[8_7]
[9_1]
[9_2]
[9_3]
[9_4]
[9_5]
[9_6]
[9_7]
[9_8]
[9_9]
[10_1]
[10_2]
[10_3]
[10_4]
[10_5]
[10_6]
[10_7]
[10_8]
[11_1]
[11_2]
[11_3]
[11_4]
[11_5]
[11_6]
[11_7]
[11_8]
[12_1]
[12_2]
[12_3]
[12_4]
[12_5]
[12_6]
[12_7]
[12_8]
[12_9]
[12_10]
[12_11]
[12_12]
[13_1]
[13_2]
[13_3]
[13_4]
[13_5]
[13_6]
[13_7]
[13_8]
[13_9]
[14_1]
[14_2]
[14_3]
[14_4]
[14_5]
[14_6]
[14_7]
[14_8]
[14_9]
[14_10]
The columns in the form above have values 'YES' or any number of stray chars, NULLs, strings, you name it. I converted any string that had 'in place' but not 'not in place' into 'Yes'. Not shown and not an issue.
Here is the structure of CapYesForSpec:
specialty
capnumber
capforthisspecialty
specialty and specialtyredux match after I did a whole lot of decoding between my data sources (not shown - not an issue).
capnumber lists all the capabilities in the format [n_n] for each specialty. CapForThisSpecialty is always 'YES' because that's what I selected for when creating this table.
So each specialty has a specific set of capabilities for it. I wish I could pass these values in from a table for each specialty script. Better yet, it would be ideal to have one script that I use for all so that I could pass in the specialty and those capabilities that apply to it. Any ideas / advice welcome. I've finished doing it manually today but I'm very interested in reducing the manual work and I'm looking forward to taking this code from kludge to more elegant. It's much too error prone the way it is now not to mention how tedious it is.
Thanks!
March 6, 2014 at 8:15 am
Hello,
Is my question too general or do I have too much detail? If anyone could please tell me if there's something I could do to make my post better please let me know. Thanks in advance.
March 6, 2014 at 8:51 am
pharmkittie (3/6/2014)
Hello,Is my question too general or do I have too much detail? If anyone could please tell me if there's something I could do to make my post better please let me know. Thanks in advance.
This post here - does the table at the top of the post actually exist?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2014 at 1:32 pm
Yes. The table exists. Why?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply