Notifications
Clear all
Topic starter
To make a long story short, we have about 60 custom forms that were built with random observation terms and have been used for the last couple of years. There are about 4,500 observation terms in use. We just started using our state immunization registry electronically and several of the obs terms in our custom forms are using the same obs terms in the vaccination form and are sending out bogus data and creating quite the headache. I've gotten a lot of them cleared up but I want to be sure all of the obs terms we're currently using are not on this list.
The immunization list contains 7,200 obs terms and rather than go through it one by one, I'd like to dump it in excel (like i currently do) and set conditional formatting on the column to highlight any duplicate entries and then clear up the duplicates.
Is this even possible or am I just screwed?
Thanks!
Posted : December 4, 2013 5:59 am
You want to get the flowsheet OBSHEADs into an excel file? Yes that's possible. This is a late response so let me know if you're still interested.
Posted : December 26, 2013 1:22 pm
Topic starter
Yes, definitely!
Posted : December 26, 2013 9:47 pm
I preface this with the reminder to not run scripts from the internet against your production database. 
This is all in SSMS:
1) Create a helper function like this:
-- =============================================
-- Description:
-- Function to split CDL strings.
-- Performs well for small volumes of data.
-- DO NOT pass in dirty data! use for only known well structured data.
-- source: http://www.sqlperformance.com/.....it-strings
-- =============================================
CREATE FUNCTION [dbo].[temp_SplitStrings_XML]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO
2) Run this script using your flowsheet name:
DECLARE @flowsheetName VARCHAR(80)
SET @flowsheetName='Flowsheet name here'
--Pull CDL string of HDIDs from the source flowsheet
DECLARE @hdidList VARCHAR(MAX)
SELECT @hdidList=TEXT
FROM HIEROBJS
WHERE [NAME]=@flowsheetName
--Convert CDL string of HDIDs to table
--Clean up values. Some have .0, some do not.
DECLARE @hdids TABLE (hdid numeric(19))
INSERT INTO @hdids
SELECT LTRIM(Item) FROM dbo.temp_SplitStrings_XML(@hdidList, ',')
SELECT oh.[NAME]
FROM @hdids h
INNER JOIN OBSHEAD oh
ON oh.HDID=h.HDID
3) Copy and paste resulting table to Excel.
Posted : December 29, 2013 7:10 am
