Row_number()

1

Hi,

I have the following query;
dbo.QryMaxPatID

SELECT     TOP (100) PERCENT (MAX(dbo.EmisPatient1.PatientID) / 100000 + 1) * 1 AS PatID, dbo.EmisCreatePatList1.PracID
FROM         dbo.EmisCreatePatList1 LEFT OUTER JOIN
                      dbo.EmisPatient1 ON dbo.EmisCreatePatList1.PracID = dbo.EmisPatient1.PracID
GROUP BY dbo.EmisCreatePatList1.PracID
ORDER BY dbo.EmisCreatePatList1.PracID

Results

PatID                    PracID
20378                   20001
1901                     20002
30000                   20003

Now, I have the query that joins the PatID and PracID - presently PatientID is NULL in dbo.QryPatID_Rtn.
I want it to READ the PatID from dbo.QryMaxPatID and set it as the start value.


SELECT     TOP (100) PERCENT PracID, CAST(PatID AS Varchar(10)) + CAST(PracID AS varchar(10)) AS PatientID
FROM         (SELECT     PracID, row_number() OVER (partition BY pracid
                       ORDER BY pracid) AS PatID
FROM         dbo.QryPatID_Rtn WITH (tablock)) t
ORDER BY PracID, PatID

Therefore, the result will be;

PatientID                      PracID
2037820001                   20001
2037920001                   20001
2038020001                   20001 ....
190120002                     20002
190220002                     20002....
3000020003                   20003
3000120003                   20003
3000220003                   20003

I hope you got the logic, thank you so much

2

Any help please with the above..

Thanks

3

I read your posting couple of times, but could not understand the logic you want to implement.

I am guessing that the PatientID is a combination of PatId and PracId as in 20378 adn 20001 result in PatientId = 2037820001. But where did 2038920001 come from?

If you can post consumable data (create table statements etc.) that shows the sample input,the output you want to get and the logic you want to use, that would make it easier to respond.

1 Like
4

Thanks JamesK -

The last number for Prac 20001 in the table (dbo.EmisPatient1.PatientID) is 2037720001 - I wrote the query to extract the patID 20377 and added 1

SELECT     TOP (100) PERCENT (MAX(dbo.EmisPatient1.PatientID) / 100000 + 1) * 1 AS PatID, dbo.EmisCreatePatList1.PracID
FROM         dbo.EmisCreatePatList1 LEFT OUTER JOIN
                      dbo.EmisPatient1 ON dbo.EmisCreatePatList1.PracID = dbo.EmisPatient1.PracID
GROUP BY dbo.EmisCreatePatList1.PracID
ORDER BY dbo.EmisCreatePatList1.PracID

Now I have the max values for all pracids...

so instead of starting patid from 120001, 2,20002 etc as this part of the query does;

(SELECT PracID, row_number() OVER (partition BY pracid ORDER BY pracid) AS PatID

I want to start from 2037820001, 2037920001 to have a continuation because 120001 already exists etc

This will be the case for all other practices....

Thanks

5

Any help on this please