Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query to pick first row of data

Posted on 2011-02-20
15
Medium Priority
?
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34940368
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
ID: 34940376
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
ID: 34940437
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34940714
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
ID: 34940737
No it wont do ..... :(
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34940785
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
ID: 34940810
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
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34940938
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 41

Expert Comment

by:Sharath
ID: 34943992
>> 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
ID: 34948111
Sharath_123 i commented on your post
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34948213
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
ID: 34949248
Hello Sharath_123:
Can we make this query work a little fast as it is generating 4000 rows in 4 minutes.

TIA
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34949360
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
ID: 34949588
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
ID: 34957330
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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