18 lines
959 B
SQL
18 lines
959 B
SQL
-- Create a Summary of various Links within Procedure text
|
|
select StartCount,EndCount,StartStartCount,EndEndCount
|
|
, case stat when 0 then 'Good - Matching Starts and Ends' when 1 then 'Bad - Multiple Starts' else 'Bad - Requires Manual Fix' end Status
|
|
, Count(*) HowMany
|
|
from (
|
|
select ContentID, StartCount,EndCount,StartStartCount,EndEndCount
|
|
, case when StartCount=EndCount and StartStartCount = 0 and EndEndCount = 0 then 0
|
|
when StartCount = EndCount+StartStartCount and EndEndCount = 0 then 1
|
|
else 2 end Stat
|
|
from (
|
|
select ContentID
|
|
, (Len(text)-len(replace(text,'<START]','')))/7 StartCount
|
|
, (Len(text)-len(replace(text,'[END>','')))/5 EndCount
|
|
, (Len(text)-len(replace(text,'<START]<START]','')))/14 StartStartCount
|
|
, (Len(text)-len(replace(text,'[END>[END>','')))/10 EndEndCount
|
|
From Contents) T1 ) t2
|
|
Group By Stat,StartCount,EndCount,StartStartCount,EndEndCount
|
|
Order By Stat,StartStartCount,EndEndCount,abs(StartCount-EndCount),StartCount,EndCount |