Inserting data based on different conditons

Posted on

Question :

Though , i put my efforts in making question easy to understand , but i still dnt know whats going wrong . Anyways , im trying to make it more compact

I have below table now only :

In column number JU means Junior and SU means Senior

 name         number            fin
------------------------------------
 paul        JU00011          100401
 paul        JU00012          100402
 paul        JU00012          100403
 maul        JU00021          100641
 maul        JU00022          100642
 paul        SU00012          100401
 paul        SU00012          100402
 paul        SU00012          100403

Now in this table , fin for paul are same ( for junior ans senior ).
i need to make an update in this table 
for example :If paul is coming under two numbers (i.e JU and SU) and have same fin number,
so i want to update fin , when 2 fin are same and belongs to same name and is coming under Junior category and Senior Category 

 Final Result :
name         number            fin
------------------------------------
 paul        JU00011          100401
 paul        JU00012          100402
 paul        JU00012          100403
 maul        JU00021          100641
 maul        JU00022          100642
 paul        SU00012          1004011
 paul        SU00012          1004012
 paul        SU00012          1004013

  Such type of records are more.

Answer :

Given the way this question has changed, it is difficult to determine exactly what you need. The current data and query in the question do not match the result you have posted.

That said, assuming the data is the way I think it is, this should provide the result you want

INSERT INTO T3 (name,number,fin)
SELECT 
    T1.name,
    T1.Rollno as number,
    CAST(CID AS NVARCHAR(11)) +
    CASE WHEN DENSE_RANK() OVER (PARTITION BY T1.Name, T1.Unique_id ORDER BY Type) = 2 THEN N'1' ELSE N'' END +
    CAST(Right([Rollno], 1) AS NVARCHAR(1)) AS fin
FROM T1
INNER JOIN T2 ON T1.Unique_ID = T2.Unique_ID

Seriously though, I would put some effort into validating your data and reviewing your database design.

this structure is incorrect, use M:N relationship with 3 Table

Student Table

StudentId       FirstName          LastName                
1               John               Sampo        
2               Ann                Rolls              
3               Martin             Blake

School Table

SchoolId        SchoolName
1               ABC Language School
2               Albany County

StudentSchool Tbale

SchoolId      StudentId         
2               1
2               2
1               3

and now use inner join for relational select

Leave a Reply

Your email address will not be published.