Monday, June 1, 2009

SQL Gymnastics

Every medical professional has a set of specialties, stored here in an expertise table. This uses a temporary table to define a sequence which is too complex for an ORDER BY clause. Older records are sorted by OrderNo and have SequenceNo=0, and newer records are ordered by SequenceNo. To complicate matters, records are grouped by facility where these specialties are practiced. We group each expertise together and work out the sorting, then build a new expertise table denormalizing these fields so the first five can be placed in a table optimized for fast access. This code was never used.


CREATE VIEW mso_test.ipd2MedProfExpertise AS
WITH ExpertiseList AS (
SELECT
row_number() over (
order by Med_Prof_Record_No, SequenceNo, OrderNo
) AS 'RowNumber',
Expertise,
Med_Prof_Record_No,
FacCode
FROM mso_test.Med_Prof_Expertises
)
SELECT
e1.Med_Prof_Record_No,
e1.FacCode,
e1.Expertise AS Expertise1,
e2.Expertise AS Expertise2,
e3.Expertise AS Expertise3,
e4.Expertise AS Expertise4
FROM ExpertiseList e1
LEFT OUTER JOIN
ExpertiseList e2 ON (
e2.Med_Prof_Record_no = e1.Med_Prof_Record_No AND
e2.FacCode = e1.FacCode AND
e2.RowNumber > e1.RowNumber
)
LEFT OUTER JOIN
ExpertiseList e3 ON (
e3.Med_Prof_Record_no = e1.Med_Prof_Record_No AND
e3.FacCode = e1.FacCode AND
e3.RowNumber > e2.RowNumber
)
LEFT OUTER JOIN
ExpertiseList e4 ON (
e4.Med_Prof_Record_no = e1.Med_Prof_Record_No AND
e4.FacCode = e1.FacCode AND
e4.RowNumber > e3.RowNumber
)
GO