Evaluate with NULL

Transact-SQL
1

Hi,

I have the following query (A);

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

The above query works OK, and generates the PatientIDs, however, I want the PatientID to be a follow up of the Max Value of PatientID in another table (EmisPatient1)

So, for example, if the Max Value of PatientID in the table for a particular practice (e.g., 20001) is 10002,

I want the next PatientID generated from the above query to follow up i.e., it will be 1000320001 and so on ....

The other table dbo.EmisPatient1 has the following fields;

PatientID, PatientNo, PracID, PersonID.

For example in table EmisPatient1,

PatientID     PatientNo       PracID        PersonID
2320001        16                20001              1
2420001        17                20001               2
2520021        18                20021               1
6720021        19                20021               2

I want the query to extract any patients in the two practices as shown below as a start up - i.e., follow up of the PatientID as in the EMISPatient1 table; Note - 25 (is an incremental value and 20001 is the PracID)

PatientID     PatientNo       PracID        PersonID
2520001        178              20001               7
6820021          9               20021               4

Any help will be indeed appreciated..

Many Thanks

2

remove this

then, why can't you just get the max id from the other table and concatenate the two?

3

Ok - will remove the tablock

-- Max ID is already a concatenation..

PatientID

A+B

A = incrementing value by 1
B =PracID

So, Max PatientID in the table will be for example; 100220001 (where 20001 is B and the value has incremented to 1002)

If you know what I mean ?

I want to read 1002 then add 1 and increment all other records after ..

Also, I want the Max per practice so e.g

PatientIDs

1624220001
1625220001
1220006
127620006
7620018
8720018
720020

Max Value

Prac ID         Max Value
20001           1625
20006           1276
20018            87
20020            7

Thanks

4

so...
(max(patientID) / 100000 + 1) * 100000 + pracID
?

5

I don't understand .. Sorry

6

if max(PatientID) = 100220001

100220001/ 100000 = 1002

1002 + 1 1003

1003 * 100000 = 100300000

if PracID = 20001 then

100300000 + 20001 = 100320001

7

Ok Tried this;

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

Received the error message;

The conversion of the varchar value '3578620212' overflowed an int column.

N/B - PatientID is a BIGINT data type

Any help please

8

In your previous examples, the sample data was 9 places long. The error message is showing 10 places. What is BIGINT defined as on your platform?

9

I can do better. BIGINT

10

Those two messages contradict each other

11

Ok ..


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

This worked fine - but it never covered everything...

I have some patientIDs that have not been created - and by selecting (MAX(dbo.EmisPatient1.PatientID) - the PatientID doesn't exist in the EmisPatient1 table (new patients) so the end result becomes a NULL value.

In this case, I want it to start from a 1. E.g., if you have pracID = 20299 then it should be be 120299, ,220299 and so on as the number of patients increase.

In most cases, when a new practice joins we expects patients and hence generation of PatientID.

I hope I am clear - how can I tackle the above problem??

Many thanks

12

isnull(max(...), 1)

13

ok, I have;

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

I have results with;

PatientID                    PersonID

120245                           1
120245                            2
120245                            1

Expected the result to be;

PatientID                    PersonID

120245                           1
220245                            2
320245                            1

Any help please....

Thanks for your assistance

14

You are expecting the max to be recomputed for each row? It won't be! It will be computed just once, which is why you get the results you do. To recompute for each row, you will need a few steps or perhaps a tally table.

15

I am nearly there in sorting this problem.. Please can you let me know the few steps so that I can amend the following issue...

Thank you so much

16

any help please