CODEWITHSUNDEEP
sql
zafar hayat
zafar hayat

Reputation: 98

Can some one help me in understanding this sql query

INSERT INTO Tbl_Basicinfo(Employer_Id,Lname,Fname,UserName,Userpass,Location_Id,cc2,CC3,cc4,cc5,Dohire,Job_Status,ssn,Import_Emp_No,[Benefit_Terminate], [Show_Age], [Change_IP], [IsNewHired])
SELECT M.Employer_Id,
            M.LASTNAME,
            M.FIRSTNAME,
            UPPER(LEFT(ISNULL(M.FIRSTNAME,'A'),1)+''+LEFT(ISNULL(M.LASTNAME,'C'),1))+CONVERT(VARCHAR,MaxImportEENumber+ROWNUM) [UserName],
            RIGHT(NEWID(),10) [UserPass],
            ISNULL(M.Location_Id,0)[Location_Id],
            M.Level2code,
            M.Level3code,
            M.LEVEL4Code,
            M.LEVEL5Code,
            M.DATEOFHIRE,
            1 [JobStatus],
            SSN,
            **M.MaxImportEENumber+ROWNUM [ImportEmpNo]**,
            0 [Benefit_Terminate], 
            0[Show_Age],
            M.ChangeIP,
            1 [IsNewHired] 
            from(
            ***SELECT ROW_NUMBER() OVER(ORDER BY [FIRSTNAME]) AS ROWNUM,**** FROM
            (
            ***SELECT DISTINCT 1117 [Employer_Id],PR.LASTNAME,PR.FIRSTNAME,(SELECT  MAX(CAST(ISNULL(Employee_Id,0) as BIGINT)) from Tbl_Basicinfo) [MaxImportEENumber]***,
            (SELECT TOP 1 Location_Id from Tbl_Location where Location_Code=PR.LEVEL1CODE and Employer_Id=1117 and Location like '%'+PR.LEVEL1DESCRIPTION+'%')  [Location_Id],
            (SELECT TOp 1 LEVEL2Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL2Code,
            (SELECT TOp 1 LEVEL3Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL3Code,
            (SELECT TOp 1 LEVEL4Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL4Code,
            (SELECT TOp 1 LEVEL5Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL5Code,
            (***SELECT MIN(DATEOFHIRE)*** from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) DATEOFHIRE,1 Job_Status,PR.SSN,PR.ChangeIP

            from Tbl_PPACA_Import PR
            LEFT OUTER JOIN Tbl_Basicinfo L ON L.ssn=PR.SSN and L.Employer_Id=PR.EmployerId
            WHERE PR.EmployerId=1117 and L.SSN is null and ISNULL(PR.SSN,'') not like '' AND ISNUMERIC(PR.SSN)=1
            and ISNULL(PR.PPE,DATEOFHIRE) =***(SELECT MAX(ISNULL(PPE,DATEOFHIRE)) FROM Tbl_PPACA_Import I WHERE I.SSN=PR.SSN AND I.EmployerId=PR.EmployerId )*** 

                    ) V
            ) M;

Note:This query works perfect for me but can some one help me in line which i highlighted as bold and italic in above sql query. What purpose of these line..

Upvotes: 0

Views: 52

Answers (1)

danjuggler
danjuggler

Reputation: 1320

It looks like it's an attempt to create a unique identifier of some sort. My guess would be that MaxImportEENumber gets the largest existing employee number. Then once you add the row number of the row in this particular select, [ImportEmpNo] will be a sequential number starting at the largest existing employee number counting up from the first row returned.

Upvotes: 2

Related Questions