Read from another column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Read from another column

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-03 : 10:30:50

Hi,

I have a table called TblCustomer. It has many columns but for this exercise I will ONLY take the first 2 columns relevant to this problem..


Customer ID    Supplier ID 
                 20001
                 20001
                 20465
                 20566
                 20566

Now, I want on record load it populates the Customer ID as incremental for that supplier ONLy, therefore, the result will be


Customer ID    Supplier ID 
 120001          20001
 220001          20001
 120465          20465
 120566          20566
 220566          20566
 320566          20566

So as you can see, I am incrementing the first digit ONLY if the supplier ID is the same, otherwise, I start again at 1 for the next customer ID and so on.

Now, how can I achieve this in SQL database -

Thank you

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-03 : 11:37:50

any idea how i can accomplish this please?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-03 : 11:46:28

So...is this what you need:

When a row is inserted in the table, set the customer id to the supplier id, prepending a sequence number. So, if this is the first time the supplier appears, the sequence number is 1. If it is the second time the supplier appears, the sequence number is 2, etc.?

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-03 : 11:50:29

Exactly ..... As shown on the example above..

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-03 : 12:05:57

So...


insert into mytable (customerid, supplierid)
select o.supplierid,
       c.id + 100000
from othertable o
outer apply (
	select top (1) isnull(m.customerid, o.supplierid) from mytable m
	where o.supplierid = m.supplierid
	order by o.customerid desc
) c(id)

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-06 : 05:52:44

Ok- As I mentioned earlier I have more than one field - but in this case ONLY CustomerID is generated...

In the code below, assume

PatID = CustomerID and PracID = SupplierID


INSERT INTO dbo.TablePatient (PatID, PracID, PersonID, PatientGuidDigest, BirthYear, Gender, DeathDate,Ethnicity, PatientType, Registered, RegistrationStartDate, RegistrationEndDate)
SELECT o.PracID, o.PersonID, o.PatientGuidDigest, o.BirthYear, o.Gender, o.DeathDate, o.Ethnicity, o.PatientType, o.Registered, o.RegistrationStartDate, o.RegistrationEndDate,
       c.ID + 10000
FROM dbo.QryPatientExtraction o 
OUTER APPLY(
      SELECT TOP (1) ISNULL (m.PatID, o.PracID) FROM dbo.TablePatient m
       WHERE o.Pracid = m.pracid and o.PersonID = m.PersonID and o.PatientGuidDigest = m.PatientGuidDigest
       ORDER BY o.PatID DESC
       ) c(ID)

Receive the error:

Invalid column name 'PatID'

I have checked the column PatID has been created in the table design view.

Also note, all the other fields are present in QryPatientExtraction with the exception of PATID. Which I would like to have the as shown in blog #1

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-06 : 08:07:01

in your outer apply, you only return one column, ID, which is why you get the error message

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources