June 19, 2009 at 9:58 am
I have the below script that gets does return the desired data but not in the format I would like to have.
Here is the code:
SELECT TOP (100) PERCENT dbo.Cases.Account_Number, dbo.Guideline_Criteria.Guideline_Criteria_Text
FROM dbo.Assgn_Patient_List INNER JOIN
dbo.Cases ON dbo.Assgn_Patient_List.Case_ID = dbo.Cases.Case_ID INNER JOIN
dbo.Cases_Super ON dbo.Cases.SuperCase_ID = dbo.Cases_Super.SuperCase_ID INNER JOIN
dbo.Patient_Record ON dbo.Cases_Super.Patient_Record_ID = dbo.Patient_Record.Patient_Record_ID INNER JOIN
dbo.Persons ON dbo.Patient_Record.Person_ID = dbo.Persons.Person_ID INNER JOIN
dbo.Assgn_Shift_Summary ON dbo.Assgn_Patient_List.Assgn_Shift_Summary_ID = dbo.Assgn_Shift_Summary.Assgn_Shift_Summary_ID INNER JOIN
dbo.Location_Of_Service ON dbo.Assgn_Shift_Summary.Unit_ID = dbo.Location_Of_Service.Location_ID INNER JOIN
dbo.Area_Of_Service ON dbo.Location_Of_Service.CareArea_ID = dbo.Area_Of_Service.CareArea_ID INNER JOIN
dbo.CaseMixLevels ON dbo.CaseMixLevels.CaseMix_Level_ID = dbo.Assgn_Patient_List.CaseMix_Level_ID INNER JOIN
dbo.Assgn_Patient_Class_Rsn ON dbo.Assgn_Patient_List.Assgn_Patient_List_ID = dbo.Assgn_Patient_Class_Rsn.Assgn_Patient_List_ID INNER JOIN
dbo.Guideline_Criteria ON dbo.Assgn_Patient_Class_Rsn.Guideline_Criteria_ID = dbo.Guideline_Criteria.Guideline_Criteria_ID
WHERE (CONVERT(nvarchar, dbo.Assgn_Shift_Summary.End_DTime, 101) LIKE CONVERT(nvarchar, GETDATE(), 101) OR
CONVERT(nvarchar, dbo.Assgn_Shift_Summary.Start_DTime, 101) LIKE CONVERT(nvarchar, GETDATE(), 101)) AND
(dbo.Area_Of_Service.TxtField LIKE '%Acute Care') AND (dbo.Location_Of_Service.TxtField LIKE 'BRK-4 E%')
This returns the data like:
Account_Number Guideline_Criteria
BRK5016980228CRT patient that remains on the unit.
BRK5016980228Epilepsy requiring monitoring. (2:1 ratio)
BRK5017167938Dialysis or bedside pheresis– day of.
BRK5017167938Moribund/DNRs requiring >30 minutes or with large family.
BRK5016980228Physiological instability is LIFE-THREATENING (codes, status asthmaticus, status epilepticus).
BRK5016980228Care off unit > 1 hour (e. g. surgery, transport)
BRK5016980228Seizure patient actively seizing/status epileptus
As you can see there are multiple rows for each Account_Number. What I would like to have returned is one row for each account_number will all the guideline_criteria.
Example
BRK5016980228 CRT patient that remains on the unit. Epilepsy requiring monitoring. (2:1 ratio)
And when put into an Excel worksheet I would like the row to look like:
Cell A1 =
BRK5016980228
Cell A2 =
CRT patient that remains on the unit.
Epilepsy requiring monitoring. (2:1 ratio)
June 19, 2009 at 10:31 am
Hey,
not 100% sure what you are wanting to do here, what output are you expecting? Is it just the first two values you are wanting output into cell A2? Or is it all of the values for Guideline_Criteria associated with a given Account_Number?
You may be able to do something FOR XML, but I haven't been able to get the new line working:
DECLARE @temp AS TABLE (Account_Number CHAR(13), Guideline_Criteria VARCHAR(255))
INSERT INTO @temp
SELECT 'BRK5016980228', 'CRT patient that remains on the unit.' UNION ALL
SELECT 'BRK5016980228', 'Epilepsy requiring monitoring. (2:1 ratio)' UNION ALL
SELECT 'BRK5017167938', 'Dialysis or bedside pheresis– day of.' UNION ALL
SELECT 'BRK5017167938', 'Moribund/DNRs requiring >30 minutes or with large family.' UNION ALL
SELECT 'BRK5016980228', 'Physiological instability is LIFE-THREATENING (codes, status asthmaticus, status epilepticus).' UNION ALL
SELECT 'BRK5016980228', 'Care off unit > 1 hour (e. g. surgery, transport)' UNION ALL
SELECT 'BRK5016980228', 'Seizure patient actively seizing/status epileptus'
SELECT * FROM @temp
SELECT
Account_Number,
(
SELECT Guideline_Criteria + CHAR(13) + CHAR(10)
FROM @temp T2
WHERE T1.Account_Number = T2.Account_Number
FOR XML PATH('')
)
FROM @temp T1
GROUP BY T1.Account_Number
June 19, 2009 at 11:09 am
That does work. I can live without the newline and just separate each criteria with a semi-colon.
The script run fine on my test server which is 2005 but I get the below error in PROD which is SQL2000.
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'XML'.
I have tried replacing the FOR XML PATH('') with FOR XML RAW but that doesn't work either.
Any suggestions?
THANKS!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply