November 29, 2012 at 7:29 am
Hello all,
a quick question i am working on a SQL script which a colleague of mine has half completed. The issue is i have in then next step is create a new field which has counter based on different episode number.
My current code is:
SELECT a.Coding_Source, ad.Admit_Date, dd.Disch_Date, pn.Patient_No, w.Ward, ed.Episode_Date,
en.Episode_Number
FROM PS_TestForOnline a
CROSS APPLY (SELECT TOP 1 Admit_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Admit_Date IS NOT NULL AND b.Admit_Date <> ''
ORDER BY b.POS DESC) ad
CROSS APPLY (SELECT TOP 1 Disch_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Disch_Date IS NOT NULL AND b.Disch_Date <> ''
ORDER BY b.POS DESC) dd
CROSS APPLY (SELECT TOP 1 Patient_No
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Patient_No IS NOT NULL AND b.Patient_No <> ''
ORDER BY b.POS DESC) pn
CROSS APPLY (SELECT TOP 1 Ward
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Ward IS NOT NULL AND b.Ward <> ''
ORDER BY b.POS DESC) w
CROSS APPLY (SELECT TOP 1 Episode_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Episode_Date IS NOT NULL AND b.Episode_Date <> ''
ORDER BY b.POS DESC) ed
CROSS APPLY (SELECT TOP 1 Episode_Number
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Episode_Number IS NOT NULL AND b.Episode_Number <> ''
ORDER BY b.POS DESC) en
what i want is another column at the end (position) which starts the count of the episode_number when it changes to a new number i.e
Coding_SourceAdmit_Date Disch_Date Patient_NoWard Episode_DateEpisode_Number Position (new coloumn)
4 2012-11-232012-11-23 1234567DSS*CIC2012-11-23 1234567>1:1 1
50 2012-11-232012-11-23 1234567DSS*CIC2012-11-23 1234567>1:1 2
6 2012-11-212012-11-23 7654321AG*CIC2012-11-23 7654321>2:1 1
35 2012-11-212012-11-23 7654321AG*CIC2012-11-23 7654321>2:1 2
50 2012-11-212012-11-23 7654321AG*CIC2012-11-23 7654321>2:1 3
60 2012-11-212012-11-23 7654321AG*CIC2012-11-23 7654321>2:1 4
i'm hoping some can assist me in achieving this?
November 29, 2012 at 7:36 am
You need to use the ROW_NUMBER function with a PARTITION BY clause.
John
November 29, 2012 at 7:58 am
thanks John it worked.
i've updated the script if others get stuck to a similar problem
SELECT a.Coding_Source, ad.Admit_Date, dd.Disch_Date, pn.Patient_No, w.Ward, ed.Episode_Date,
ROW_NUMBER() OVER(Partition by en.Episode_Number ORDER BY en.Episode_Number) AS Row_Number
FROM PS_TestForOnline a
CROSS APPLY (SELECT TOP 1 Admit_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Admit_Date IS NOT NULL AND b.Admit_Date <> ''
ORDER BY b.POS DESC) ad
CROSS APPLY (SELECT TOP 1 Disch_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Disch_Date IS NOT NULL AND b.Disch_Date <> ''
ORDER BY b.POS DESC) dd
CROSS APPLY (SELECT TOP 1 Patient_No
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Patient_No IS NOT NULL AND b.Patient_No <> ''
ORDER BY b.POS DESC) pn
CROSS APPLY (SELECT TOP 1 Ward
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Ward IS NOT NULL AND b.Ward <> ''
ORDER BY b.POS DESC) w
CROSS APPLY (SELECT TOP 1 Episode_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Episode_Date IS NOT NULL AND b.Episode_Date <> ''
ORDER BY b.POS DESC) ed
CROSS APPLY (SELECT TOP 1 Episode_Number
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Episode_Number IS NOT NULL AND b.Episode_Number <> ''
ORDER BY b.POS DESC) en
November 29, 2012 at 8:11 am
OK, well done. Just one comment. If you use the same column (episode number) in your ORDER BY and PARTITION BY clause, you're saying that you're not bothered about the order of the rows within each episode. Was that your intention?
John
December 7, 2012 at 2:54 am
jbon007 (11/29/2012)
thanks John it worked.i've updated the script if others get stuck to a similar problem
SELECT a.Coding_Source, ad.Admit_Date, dd.Disch_Date, pn.Patient_No, w.Ward, ed.Episode_Date,
ROW_NUMBER() OVER(Partition by en.Episode_Number ORDER BY en.Episode_Number) AS Row_Number
FROM PS_TestForOnline a
CROSS APPLY (SELECT TOP 1 Admit_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Admit_Date IS NOT NULL AND b.Admit_Date <> ''
ORDER BY b.POS DESC) ad
CROSS APPLY (SELECT TOP 1 Disch_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Disch_Date IS NOT NULL AND b.Disch_Date <> ''
ORDER BY b.POS DESC) dd
CROSS APPLY (SELECT TOP 1 Patient_No
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Patient_No IS NOT NULL AND b.Patient_No <> ''
ORDER BY b.POS DESC) pn
CROSS APPLY (SELECT TOP 1 Ward
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Ward IS NOT NULL AND b.Ward <> ''
ORDER BY b.POS DESC) w
CROSS APPLY (SELECT TOP 1 Episode_Date
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Episode_Date IS NOT NULL AND b.Episode_Date <> ''
ORDER BY b.POS DESC) ed
CROSS APPLY (SELECT TOP 1 Episode_Number
FROM PS_TestForOnline b
WHERE b.POS <= a.POS
AND b.Episode_Number IS NOT NULL AND b.Episode_Number <> ''
ORDER BY b.POS DESC) en
You're going to break the data smudge. In your colleague's original post (see here), I pointed out that you must guarantee row order to be able to get the smudge down to work. If you add the "ORDER BY en.Episode_Number" then you may cause issues (I haven't tested this, it's my gut feeling). Instead, order by the insertion order column "POS" that we added to your sample data and partition by "Episode_Number".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply