Row Number Partition By

Transact-SQL
1

Hi,

I have the following query

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

The above query works fine if you what PatID to start from 1, 2, 3 .... so on

i.e.,

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

so a typical result will be like;

PatientID           PracID
120001              20001
220001              20001
120002              20002
220002              20002
320002              20002
120006              20006

Now, instead of PatID to start from 1, 2, 3 for each PracID... I have a query that have a starting point for each PracID.
the query is called - QryMaxPracID and has the following

PatID                  PracID
22376                  20001
654                    20002
10008                  20006

Now I want to update the above query (1) so that it starts the row number from reading the value from the QryMaxPracID...

So that the query will have a result;

PatientID              PracID
2237620001            20001
2237720001            20001
65420002              20002
65520002              20002
65620002              20002
1000820006            20006

any help please - thank you

2 
SELECT        
        PracID, 
        CAST(PatID AS Varchar(10)) + CAST(PracID AS varchar(10)) AS PatientID
FROM    (
        SELECT        
                p.PracID, 
                m.PatID - 1 + row_number() OVER (partition BY p.PracID ORDER BY p.PracID) AS PatID
        FROM        dbo.QryPatID_Rtn p
        INNER JOIN    QryMaxPracID m        
        ON        p.PracID    = m.PracID
    ) t
ORDER BY    
        PracID, 
        PatID