August 3, 2016 at 10:56 am
Can't figure out how to connect these together, or maybe I'm doing this wrong. Want this output
<staffingHours processType="merge">
<staffHours>
<employeeId>EM123456</employeeId>
<workDays>
<workDay>
<date>2016-10-01</date>
<hourEntries>
<hourEntry>
<hours>5.84</hours>
<jobTitleCode>5</jobTitleCode>
<payTypeCode>2</payTypeCode>
</hourEntry>
<hourEntry>
<hours>2.2</hours>
<jobTitleCode>7</jobTitleCode>
<payTypeCode>2</payTypeCode>
</hourEntry>
</hourEntries>
</workDay>
</workDays>
</staffHours>
</staffingHours>
Here is my code
SELECT s.ProcessType '@processType',
(
Select e.EmployeeID AS employeeID
FROM HoursPaidbyDate as e
Where e.EmployeeID = w.EmployeeID
AND Loc_ID = 'MAS'
FOR XML Path ('staffHours'), type),
(
SELECT w.DateWorked AS "workday/date"
FROM HoursPaidbyDate AS w
Where Loc_ID = 'MAS' AND h.DateWorked = w.DateWorked AND h.employeeId= w.EmployeeID
FOR XML Path ('workDay'), type) AS workDays,
(
Select FORMAT(HoursPaid, 'g18') AS "hourEntry/hours", PBJ_JobTitleCode AS "hourEntry/jobTitleCode", PBJ_Group as "hourEntry/payTypeCode"
FROM HoursPaidbyDate AS h
Where Loc_ID = 'MAS'
FOR XML PATH ('hourEntry'), type) AS hourEntries
FROM StaffingHours_xml s
Where s.StaffingHours_Id = 5
FOR XML PATH ('StaffingHours')
I feel like I am close but am getting these errors.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "w.EmployeeID" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "h.DateWorked" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "h.employeeId" could not be bound.
August 3, 2016 at 12:02 pm
You have a main query and three subqueries. The subqueries can only access fields within their scope and the parent's scope. They cannot access fields/tables from the other subqueries. The table w is defined in the second subquery, but you are trying to access it from the first subquery. The table h is defined in the third subquery, but you are trying to access it from the second subquery.
Since you didn't supply sample data, it's hard to tell you how to rewrite this to get the results that you are looking for, but you may need to nest the subqueries instead of having them all at the same level.
Another option is to use CROSS APPLY to get the various nesting levels necessary.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2016 at 12:14 pm
Post the DDL (Create table scripts) for all the tables, sample data as insert statement and the expected output, makes it much easier for us to provide you with the answer.
😎
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply