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

Open in new window

LVL 2
ExpertHelp79Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
Can you replace * with required columns in the derived tables in the LEFT JOIN and check the query execution time.
0
 
SharathData EngineerCommented:
try adding a WHERE clause.

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))
0
 
JoeNuvoCommented:
try to see if you could change your query into this form

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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ExpertHelp79Author Commented:
hello Sharath_123:

Its still not giving me the right count

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))
0
 
JoeNuvoCommented:
my idea is something like following

SELECT * FROM
(SELECT
	HAR.PersonNo, HAR.EmployeeName, HAR.Period, HAR.ProfitCenter, HAR.CostCenter, HAR.ProjectTime
	, YTC.Text, NULL as Status, NULL as Remark, HAR.Account, HAR.ProjectNumber, CONVERT(VARCHAR(10),HAR.Date, 120) as Date
	, PC.ProjectTLE, EMD.BaseLocation, ETES.ManagerName, EMD.ORGManager1, HAR.Vertical, ICE.Manual_Auto, ICE.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, AM.AccountDescription
	, ETES.EmployeeEmailID as EmployeeEmail, ETES.ManagerEmailID as EtesManagerEmail
	, CONVERT(VARCHAR(10),HAR.UploadDate, 120) As UploadDate
	, ROW_NUMBER() OVER (
PARTITION BY HAR.PersonNo, HAR.Date, HAR.ProjectNumber, PC.SoldToPt, PC.ProjectTLE, HAR.Account
ORDER BY HAR.PersonNo, HAR.Date, HAR.ProjectNumber, PC.SoldToPt, PC.ProjectTLE, HAR.Account) AS rn
FROM WeeklyHARReport HAR 
	LEFT OUTER JOIN YTCError AS YTC ON HAR.PersonNo = YTC.PersonNo AND HAR.Date = YTC.ObjectID
	LEFT OUTER JOIN ProjectConsolidated AS PC ON HAR.ProjectNumber = PC.ShortIdentification
	LEFT OUTER JOIN ETesMgrEmp AS ETES ON HAR.PersonNo = ETES.EmpID
	LEFT OUTER JOIN EmanageDump AS EMD ON HAR.PersonNo = EMD.EmployeeID 
	LEFT OUTER JOIN ICEntities AS ICE ON PC.SoldToPt = ICE.SoldToParty 
	LEFT OUTER JOIN GPSF AS GPS ON HAR.PersonNo = GPS.EmployeeID AND PC.ProjectTLE = GPS.WBSCode
	LEFT OUTER JOIN AccountMaster AS AM ON HAR.Account = AM.Account
WHERE
	CONVERT(VARCHAR(10),GPS.UploadDate, 120) = '2011-02-14'
	AND CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '2011-02-14'
	AND HAR.PersonNo is not null
) Sub
WHERE rn = 1

Open in new window


Not sure if it'll working fine on your data, since I don't know the actual data relationship
0
 
ExpertHelp79Author Commented:
No it wont do ..... :(
0
 
SharathData EngineerCommented:
Can you check this?
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

Open in new window

0
 
rushShahCommented:
try this,

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 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))
0
 
ExpertHelp79Author Commented:
It is bringing the exact figure but just want to know what are the changes before i put it in production

Thanks for the help
0
 
SharathData EngineerCommented:
>> It is bringing the exact figure but just want to know what are the changes before i put it in production

Did you comment on my post or rushShah's post?
0
 
ExpertHelp79Author Commented:
Sharath_123 i commented on your post
0
 
SharathData EngineerCommented:
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.

Does that make sense?
0
 
ExpertHelp79Author Commented:
Hello Sharath_123:
Can we make this query work a little fast as it is generating 4000 rows in 4 minutes.

TIA
0
 
ExpertHelp79Author Commented:
same amount of time it is taking ......  
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

Open in new window

0
 
JoeNuvoCommented:
Is it possible to give some data for testing + expecting result?
(and table structure or data type of relative field on each table.)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.