Solved

Query to pick first row of data

Posted on 2011-02-20
15
264 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:ExpertHelp79
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
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
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
No it wont do ..... :(
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 8

Expert Comment

by:rushShah
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
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
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
>> 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
 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
Sharath_123 i commented on your post
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
Hello Sharath_123:
Can we make this query work a little fast as it is generating 4000 rows in 4 minutes.

TIA
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
Can you replace * with required columns in the derived tables in the LEFT JOIN and check the query execution time.
0
 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
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
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
Is it possible to give some data for testing + expecting result?
(and table structure or data type of relative field on each table.)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now