Query to pick first row of data

Hello
I am attaching a query below which implements the logic of a Excel Vlookup, i.e t fetches the first matching row from the tables.

Now i have a issue
It only picks the row 1 as it is specified as rn1=1 as in the below snapshot

left outer Join (select * from (select a.* ,row_number() over (partition by (WBSCode + EmployeeID)
order by EmployeeID) as rn1 from GPSF as a) as b where rn1 = 1 and
CONVERT(VARCHAR(10),UploadDate, 120) = '2011-02-14') as GPS on
(cast(HAR.PersonNo as varchar) + cast(PC.ProjectTLE as varchar)) =
(cast(GPS.EmployeeID as varchar) + cast(GPS.WBSCode as varchar))

i want to change this query to take the row which has data, as the query returns NULL even if data is available in the database. The reason is data is available in consecutive rows and not in row1.

SOS
Please Help
TIA

Select HAR.PersonNo as PersonNo,HAR.EmployeeName as EmployeeName,HAR.Period as Period,HAR.ProfitCenter as ProfitCenter,HAR.CostCenter as CostCenter,HAR.ProjectTime as ProjectTime,YTC.Text as Text,Null as Status,Null as Remark,HAR.Account as Account, HAR.ProjectNumber as ProjectNumber,CONVERT(VARCHAR(10),HAR.Date, 120) as Date,PC.ProjectTLE as ProjectTLE,EMD.BaseLocation as BaseLocation,ETES.ManagerName as ManagerName,EMD.ORGManager1 as ORGManager1,HAR.Vertical as Vertical,ICE.Manual_Auto as Manual_Auto,ICE.GDS_NonGDS as GDS_NonGDS,convert(varchar(10),GPS.ResourceStartDate, 120) as ResourceStartDate,convert(varchar(10),GPS.ResourceEndDate, 120) as ResourceEndDate,GPS.Status as Status_Act, GPS.BillingKey as BK,PC.SoldToPt as SoldToPt,AM.AccountDescription as AccountDescription,ETES.EmployeeEmailID as EmployeeEmail,ETES.ManagerEmailID as EtesManagerEmail,CONVERT(VARCHAR(10),HAR.UploadDate, 120) As UploadDate From WeeklyHARReport HAR left outer Join (select * from (select x.* ,row_number() over (partition by (cast(PersonNo as varchar)+cast(cast(ObjectID as datetime) as varchar)) order by objectid ) as rn from YTCError as x) as y where rn=1) as YTC on (cast(HAR.PersonNo as varchar) + Cast(cast(HAR.Date as datetime)as varchar)) = (cast(YTC.PersonNo as varchar) + cast(cast(YTC.ObjectID as datetime) as varchar)) left outer Join (select * from (select a.* ,row_number() over (partition by ShortIdentification order by ShortIdentification) as rn1 from ProjectConsolidated as a) as b where rn1=1 ) as PC on HAR.ProjectNumber = PC.ShortIdentification left outer Join (select * from (select a.* ,row_number() over (partition by EmpID order by EmpID) as rn1 from ETesMgrEmp as a) as b where rn1=1 ) as ETES on HAR.PersonNo = ETES.EmpID left outer Join (select * from (select a.* ,row_number() over (partition by EmployeeID order by EmployeeID) as rn1 from EmanageDump as a) as b where rn1=1 ) as EMD on HAR.PersonNo = EMD.EmployeeID left outer Join (select * from (select a.* ,row_number() over (partition by SoldToParty order by SoldToParty) as rn1 from ICEntities as a) as b where rn1=1 ) as ICE on PC.SoldToPt = ICE.SoldToParty left outer Join (select * from (select a.* ,row_number() over (partition by (WBSCode + EmployeeID) order by EmployeeID) as rn1 from GPSF as a) as b where rn1 = 1 and CONVERT(VARCHAR(10),UploadDate, 120) = '2011-02-14') as GPS on (cast(HAR.PersonNo as varchar) + cast(PC.ProjectTLE as varchar)) = (cast(GPS.EmployeeID as varchar) + cast(GPS.WBSCode as varchar)) left outer Join (select * from (select a.* ,row_number() over (partition by Account order by Account) as rn1 from AccountMaster as a) as b where rn1=1 ) as AM on HAR.Account = AM.Account Where CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '2011-02-14' and HAR.PersonNo is not null

LEFT OUTER JOIN (SELECT *
FROM (SELECT a.*,
ROW_NUMBER()
OVER(PARTITION BY (WBSCode + EmployeeID) ORDER BY EmployeeID) AS rn1
FROM GPSF AS a WHERE WBSCode IS NOT NULL
AND EmployeeID IS NOT NULL) AS b
WHERE rn1 = 1
AND CONVERT(VARCHAR(10),UploadDate,120) = '2011-02-14') AS GPS
ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(PC.ProjectTLE AS VARCHAR)) = (CAST(GPS.EmployeeID AS VARCHAR) + CAST(GPS.WBSCode AS VARCHAR))

SELECT * FROM
(
SELECT <field lists>, <ROW_NUMBER() function over whatever field you needed to make the count over needed data>
FROM
<table> join with <table> join with table.....
WHERE [filter condition to removed join without needed data.]
) AS SUB
WHERE RN = 1

Hello JoeNuvo:
can you change the below snapshot as per your idea
i tried but giving me error

left outer Join (select * from (select a.* ,row_number() over (partition by (WBSCode + EmployeeID)
order by EmployeeID) as rn1 from GPSF as a) as b where rn1 = 1 and
CONVERT(VARCHAR(10),UploadDate, 120) = '2011-02-14') as GPS on
(cast(HAR.PersonNo as varchar) + cast(PC.ProjectTLE as varchar)) =
(cast(GPS.EmployeeID as varchar) + cast(GPS.WBSCode as varchar))

SELECT HAR.PersonNo AS PersonNo, HAR.EmployeeName AS EmployeeName, HAR.Period AS Period, HAR.ProfitCenter AS ProfitCenter, HAR.CostCenter AS CostCenter, HAR.ProjectTime AS ProjectTime, YTC.TEXT AS TEXT, NULL AS Status, NULL AS Remark, HAR.ACCOUNT AS ACCOUNT, HAR.ProjectNumber AS ProjectNumber, CONVERT(VARCHAR(10),HAR.DATE,120) AS DATE, PC.ProjectTLE AS ProjectTLE, EMD.BaseLocation AS BaseLocation, ETES.ManagerName AS ManagerName, EMD.ORGManager1 AS ORGManager1, HAR.Vertical AS Vertical, ICE.Manual_Auto AS Manual_Auto, ICE.GDS_NonGDS AS GDS_NonGDS, CONVERT(VARCHAR(10),GPS.ResourceStartDate,120) AS ResourceStartDate, CONVERT(VARCHAR(10),GPS.ResourceEndDate,120) AS ResourceEndDate, GPS.Status AS Status_Act, GPS.BillingKey AS BK, PC.SoldToPt AS SoldToPt, AM.AccountDescription AS AccountDescription, ETES.EmployeeEmailID AS EmployeeEmail, ETES.ManagerEmailID AS EtesManagerEmail, CONVERT(VARCHAR(10),HAR.UploadDate,120) AS UploadDate FROM WeeklyHARReport HAR LEFT OUTER JOIN (SELECT * FROM (SELECT YTC.*, ROW_NUMBER() OVER(PARTITION BY (CAST(YTC.PersonNo AS VARCHAR) + CAST(CAST(YTC.ObjectID AS DATETIME) AS VARCHAR)) ORDER BY YTC.objectid) AS rn FROM YTCError AS YTC JOIN WeeklyHARReport HAR ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(CAST(HAR.DATE AS DATETIME) AS VARCHAR)) = (CAST(YTC.PersonNo AS VARCHAR) + CAST(CAST(YTC.ObjectID AS DATETIME) AS VARCHAR))) AS y WHERE rn = 1) AS YTC ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(CAST(HAR.DATE AS DATETIME) AS VARCHAR)) = (CAST(YTC.PersonNo AS VARCHAR) + CAST(CAST(YTC.ObjectID AS DATETIME) AS VARCHAR)) LEFT OUTER JOIN (SELECT * FROM (SELECT PC.*, ROW_NUMBER() OVER(PARTITION BY PC.ShortIdentification ORDER BY PC.ShortIdentification) AS rn1 FROM ProjectConsolidated AS PC JOIN WeeklyHARReport HAR ON HAR.ProjectNumber = PC.ShortIdentification) AS b WHERE rn1 = 1) AS PC ON HAR.ProjectNumber = PC.ShortIdentification LEFT OUTER JOIN (SELECT * FROM (SELECT ETES.*, ROW_NUMBER() OVER(PARTITION BY ETES.EmpID ORDER BY ETES.EmpID) AS rn1 FROM ETesMgrEmp AS ETES JOIN WeeklyHARReport HAR ON HAR.PersonNo = ETES.EmpID) AS b WHERE rn1 = 1) AS ETES ON HAR.PersonNo = ETES.EmpID LEFT OUTER JOIN (SELECT * FROM (SELECT EMD.*, ROW_NUMBER() OVER(PARTITION BY EMD.EmployeeID ORDER BY EMD.EmployeeID) AS rn1 FROM EmanageDump AS EMD JOIN WeeklyHARReport HAR ON HAR.PersonNo = EMD.EmployeeID) AS b WHERE rn1 = 1) AS EMD ON HAR.PersonNo = EMD.EmployeeID LEFT OUTER JOIN (SELECT * FROM (SELECT ICE.*, ROW_NUMBER() OVER(PARTITION BY ICE.SoldToParty ORDER BY ICE.SoldToParty) AS rn1 FROM ICEntities AS ICE JOIN ProjectConsolidated AS PC ON PC.SoldToPt = ICE.SoldToParty JOIN WeeklyHARReport HAR ON HAR.ProjectNumber = PC.ShortIdentification) AS b WHERE rn1 = 1) AS ICE ON PC.SoldToPt = ICE.SoldToParty LEFT OUTER JOIN (SELECT * FROM (SELECT GPS.*, ROW_NUMBER() OVER(PARTITION BY (GPS.WBSCode + GPS.EmployeeID) ORDER BY GPS.EmployeeID) AS rn1 FROM ProjectConsolidated AS PC JOIN WeeklyHARReport HAR ON HAR.ProjectNumber = PC.ShortIdentification JOIN GPSF AS GPS ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(PC.ProjectTLE AS VARCHAR)) = (CAST(GPS.EmployeeID AS VARCHAR) + CAST(GPS.WBSCode AS VARCHAR)) WHERE CONVERT(VARCHAR(10),GPS.UploadDate,120) = '2011-02-14') AS b WHERE rn1 = 1) AS GPS ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(PC.ProjectTLE AS VARCHAR)) = (CAST(GPS.EmployeeID AS VARCHAR) + CAST(GPS.WBSCode AS VARCHAR)) LEFT OUTER JOIN (SELECT * FROM (SELECT AM.*, ROW_NUMBER() OVER(PARTITION BY AM.ACCOUNT ORDER BY AM.ACCOUNT) AS rn1 FROM AccountMaster AS AM JOIN WeeklyHARReport HAR ON HAR.ACCOUNT = AM.ACCOUNT) AS b WHERE rn1 = 1) AS AM ON HAR.ACCOUNT = AM.ACCOUNT WHERE CONVERT(VARCHAR(10),HAR.UploadDate,120) = '2011-02-14' AND HAR.PersonNo IS NOT NULL

LEFT OUTER JOIN (SELECT *
FROM (SELECT a.*,
ROW_NUMBER()
OVER(PARTITION BY (WBSCode + EmployeeID) ORDER BY EmployeeID) AS rn1
FROM GPSF AS aWHERE WBSCode IS NOT NULL AND WBSCode<>''
AND EmployeeID IS NOT NULL AND EmployeeID<>'') AS b
WHERE rn1 = 1
AND CONVERT(VARCHAR(10),UploadDate,120) = '2011-02-14') AS GPS
ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(PC.ProjectTLE AS VARCHAR)) = (CAST(GPS.EmployeeID AS VARCHAR) + CAST(GPS.WBSCode AS VARCHAR))

You have LEFT JOIN with lot of tables. From those tables, you want only one row which is already present in WeeklyHARReport table. So for those tables, do an INNER JOIN with WeeklyHARReport table first to get only the required records and then apply row_number. Finally LEFT JOIN all these derived tables with your original tables WeeklyHARReport.

SELECT HAR.PersonNo AS PersonNo, HAR.EmployeeName AS EmployeeName, HAR.Period AS Period, HAR.ProfitCenter AS ProfitCenter, HAR.CostCenter AS CostCenter, HAR.ProjectTime AS ProjectTime, YTC.TEXT AS TEXT, NULL AS Status, NULL AS Remark, HAR.ACCOUNT AS ACCOUNT, HAR.ProjectNumber AS ProjectNumber, CONVERT(VARCHAR(10),HAR.DATE,120) AS DATE, PC.ProjectTLE AS ProjectTLE, EMD.BaseLocation AS BaseLocation, ETES.ManagerName AS ManagerName, EMD.ORGManager1 AS ORGManager1, HAR.Vertical AS Vertical, ICE.Manual_Auto AS Manual_Auto, ICE.GDS_NonGDS AS GDS_NonGDS, CONVERT(VARCHAR(10),GPS.ResourceStartDate,120) AS ResourceStartDate, CONVERT(VARCHAR(10),GPS.ResourceEndDate,120) AS ResourceEndDate, GPS.Status AS Status_Act, GPS.BillingKey AS BK, PC.SoldToPt AS SoldToPt, AM.AccountDescription AS AccountDescription, ETES.EmployeeEmailID AS EmployeeEmail, ETES.ManagerEmailID AS EtesManagerEmail, CONVERT(VARCHAR(10),HAR.UploadDate,120) AS UploadDate FROM WeeklyHARReport HAR LEFT OUTER JOIN (SELECT * FROM (SELECT YTC.TEXT,YTC.PersonNo,YTC.ObjectID, ROW_NUMBER() OVER(PARTITION BY (CAST(YTC.PersonNo AS VARCHAR) + CAST(CAST(YTC.ObjectID AS DATETIME) AS VARCHAR)) ORDER BY YTC.objectid) AS rn FROM YTCError AS YTC JOIN WeeklyHARReport HAR ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(CAST(HAR.DATE AS DATETIME) AS VARCHAR)) = (CAST(YTC.PersonNo AS VARCHAR) + CAST(CAST(YTC.ObjectID AS DATETIME) AS VARCHAR))) AS y WHERE rn = 1) AS YTC ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(CAST(HAR.DATE AS DATETIME) AS VARCHAR)) = (CAST(YTC.PersonNo AS VARCHAR) + CAST(CAST(YTC.ObjectID AS DATETIME) AS VARCHAR)) LEFT OUTER JOIN (SELECT * FROM (SELECT PC.ProjectTLE,PC.SoldToPt,PC.ShortIdentification, ROW_NUMBER() OVER(PARTITION BY PC.ShortIdentification ORDER BY PC.ShortIdentification) AS rn1 FROM ProjectConsolidated AS PC JOIN WeeklyHARReport HAR ON HAR.ProjectNumber = PC.ShortIdentification) AS b WHERE rn1 = 1) AS PC ON HAR.ProjectNumber = PC.ShortIdentification LEFT OUTER JOIN (SELECT * FROM (SELECT ETES.ManagerName,ETES.EmployeeEmailID,ETES.ManagerEmailID,ETES.EmpID, ROW_NUMBER() OVER(PARTITION BY ETES.EmpID ORDER BY ETES.EmpID) AS rn1 FROM ETesMgrEmp AS ETES JOIN WeeklyHARReport HAR ON HAR.PersonNo = ETES.EmpID) AS b WHERE rn1 = 1) AS ETES ON HAR.PersonNo = ETES.EmpID LEFT OUTER JOIN (SELECT * FROM (SELECT EMD.BaseLocation,EMD.ORGManager1,EMD.EmployeeID, ROW_NUMBER() OVER(PARTITION BY EMD.EmployeeID ORDER BY EMD.EmployeeID) AS rn1 FROM EmanageDump AS EMD JOIN WeeklyHARReport HAR ON HAR.PersonNo = EMD.EmployeeID) AS b WHERE rn1 = 1) AS EMD ON HAR.PersonNo = EMD.EmployeeID LEFT OUTER JOIN (SELECT * FROM (SELECT ICE.Manual_Auto,ICE.GDS_NonGDS,ICE.SoldToParty, ROW_NUMBER() OVER(PARTITION BY ICE.SoldToParty ORDER BY ICE.SoldToParty) AS rn1 FROM ICEntities AS ICE JOIN ProjectConsolidated AS PC ON PC.SoldToPt = ICE.SoldToParty JOIN WeeklyHARReport HAR ON HAR.ProjectNumber = PC.ShortIdentification) AS b WHERE rn1 = 1) AS ICE ON PC.SoldToPt = ICE.SoldToParty LEFT OUTER JOIN (SELECT * FROM (SELECT GPS.ResourceStartDate,GPS.ResourceEndDate,GPS.Status,GPS.BillingKey,GPS.EmployeeID,GPS.WBSCode, ROW_NUMBER() OVER(PARTITION BY (GPS.WBSCode + GPS.EmployeeID) ORDER BY GPS.EmployeeID) AS rn1 FROM ProjectConsolidated AS PC JOIN WeeklyHARReport HAR ON HAR.ProjectNumber = PC.ShortIdentification JOIN GPSF AS GPS ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(PC.ProjectTLE AS VARCHAR)) = (CAST(GPS.EmployeeID AS VARCHAR) + CAST(GPS.WBSCode AS VARCHAR)) WHERE CONVERT(VARCHAR(10),GPS.UploadDate,120) = '2011-02-14') AS b WHERE rn1 = 1) AS GPS ON (CAST(HAR.PersonNo AS VARCHAR) + CAST(PC.ProjectTLE AS VARCHAR)) = (CAST(GPS.EmployeeID AS VARCHAR) + CAST(GPS.WBSCode AS VARCHAR)) LEFT OUTER JOIN (SELECT * FROM (SELECT AM.AccountDescription,AM.ACCOUNT, ROW_NUMBER() OVER(PARTITION BY AM.ACCOUNT ORDER BY AM.ACCOUNT) AS rn1 FROM AccountMaster AS AM JOIN WeeklyHARReport HAR ON HAR.ACCOUNT = AM.ACCOUNT) AS b WHERE rn1 = 1) AS AM ON HAR.ACCOUNT = AM.ACCOUNT WHERE CONVERT(VARCHAR(10),HAR.UploadDate,120) = '2011-02-14' AND HAR.PersonNo IS NOT NULL