December 6, 2011 at 11:03 am
Hello -
I'm need of help figuring out how to add a new column called TASK to the following script. Currently when I run these there are two columns that come up named Resource_Name and Business_Unit which is right.
Here is column name the from the script below that I need added AND (cd3.Description = 'Paid Time Off' OR t.name = 'Holiday')
SELECT r.name as Resource_Name,rw.name as Business_Unit
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
WHERE r.deleted = 0
AND r.name NOT LIKE '(C)%'
AND r.name NOT LIKE '(I)%'
AND r.name NOT LIKE '(Gen%'
AND r.name NOT LIKE '(Con%'
AND r.name NOT LIKE 'ZZ%'
AND r.name NOT BETWEEN '1/%' AND '12/31/%'
AND rw.workgrouptypeid <> '3B08677-715C-459-A88-0C642FF263'
AND rw.workgroupid <> '1B17927-DF2-449-9E3-C5CDD736A4'
AND NOT EXISTS (
SELECT e.name as Engagement,ew.name as Workgroup,p.name as Project,t.name as Task,rs.name as Resource_Name,rw.name as Business_Unit,*
FROM TaskAssignment ta
INNER JOIN Tasks t WITH(NOLOCK) ON t.taskid = ta.taskid
INNER JOIN Resources rs WITH(NOLOCK) ON rs.Resourceid = ta.Resourceid
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = RS.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
INNER JOIN Project p WITH(NOLOCK) ON p.Projectid = t.projectid
INNER JOIN Engagement e WITH(NOLOCK) ON p.Engagementid = e.Engagementid
INNER JOIN Workgroup ew WITH(NOLOCK) ON ew.workgroupid = e.Associatedworkgroup
INNER JOIN UDFCode AS udf2 WITH(NOLOCK) ON udf2.EntityId = t.TaskId AND udf2.ItemName = 'TaskCode1'
INNER JOIN CodeDetail AS cd3 WITH(NOLOCK) ON cd3.CodeDetail = udf2.UDFCode
AND (cd3.Description = 'Paid Time Off' OR t.name = 'Holiday')
AND t.locked = 0
AND rs.resourceid = r.resourceid
AND e.Associatedworkgroup = rw.workgroupid)
UNION
SELECT r.name as Resource_Name,rw.name as Business_Unit
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
WHERE r.deleted = 0
AND r.name NOT LIKE '(C)%'
AND r.name NOT LIKE '(I)%'
AND r.name NOT LIKE '(Gen%'
AND r.name NOT LIKE '(Con%'
AND r.name NOT LIKE 'ZZ_%'
AND r.name NOT BETWEEN '1/%' AND '12/31/%'
AND rw.workgrouptypeid = '3B08067-715-459-A88-0C5642FF263'
AND NOT EXISTS (
SELECT e.name as Engagement,ew.name as Workgroup,p.name as Project,t.name as Task,rs.name as Resource_Name,rw.name as Business_Unit,*
FROM TaskAssignment ta
INNER JOIN Tasks t WITH(NOLOCK) ON t.taskid = ta.taskid
INNER JOIN Resources rs WITH(NOLOCK) ON rs.Resourceid = ta.Resourceid
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = RS.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
INNER JOIN Project p WITH(NOLOCK) ON p.Projectid = t.projectid
INNER JOIN Engagement e WITH(NOLOCK) ON p.Engagementid = e.Engagementid
INNER JOIN Workgroup ew WITH(NOLOCK) ON ew.workgroupid = e.Associatedworkgroup
INNER JOIN UDFCode AS udf2 WITH(NOLOCK) ON udf2.EntityId = t.TaskId AND udf2.ItemName = 'TaskCode1'
INNER JOIN CodeDetail AS cd3 WITH(NOLOCK) ON cd3.CodeDetail = udf2.UDFCode
AND (cd3.Description = 'Paid Time Off' OR t.name = 'Holiday')
AND p.name = 'RMG - CSG Time Off'
AND t.locked = 0
AND rs.resourceid = r.resourceid)
UNION
SELECT r.name as Resource_Name,rw.name as Business_Unit
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
WHERE r.deleted = 0
AND r.name NOT LIKE '(C)%'
AND r.name NOT LIKE '(I)%'
AND r.name NOT LIKE '(Gen%'
AND r.name NOT LIKE '(Con%'
AND r.name NOT LIKE 'ZZ_%'
AND r.name NOT BETWEEN '1/%' AND '12/31/%'
AND rw.workgroupid = '1B17967-D62-44B9-9E3-C5C8DD7D6A4'
AND rw.WorkgroupId = 'B4B23EA-F912-4890-836-B40942D30B0'
AND NOT EXISTS (
SELECT e.name as Engagement,ew.name as Workgroup,p.name as Project,t.name as Task,rs.name as Resource_Name,rw.name as Business_Unit,*
FROM TaskAssignment ta
INNER JOIN Tasks t WITH(NOLOCK) ON t.taskid = ta.taskid
INNER JOIN Resources rs WITH(NOLOCK) ON rs.Resourceid = ta.Resourceid
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = RS.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
INNER JOIN Project p WITH(NOLOCK) ON p.Projectid = t.projectid
INNER JOIN Engagement e WITH(NOLOCK) ON p.Engagementid = e.Engagementid
INNER JOIN Workgroup ew WITH(NOLOCK) ON ew.workgroupid = e.Associatedworkgroup
INNER JOIN UDFCode AS udf2 WITH(NOLOCK) ON udf2.EntityId = t.TaskId AND udf2.ItemName = 'TaskCode1'
INNER JOIN CodeDetail AS cd3 WITH(NOLOCK) ON cd3.CodeDetail = udf2.UDFCode
AND (cd3.Description = 'Paid Time Off' OR t.name = 'Holiday')
AND p.name = 'RMG - CCX Time Off'
AND t.locked = 0
AND rs.resourceid = r.resourceid)
Thanks in advance,
D
December 6, 2011 at 11:07 am
So what's wrong with the results?
PS on nolock => http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Any reason why you do UNION instead of UNION ALL (union = SELECT DISTINCT * FROM all 3 queries combined)?
December 6, 2011 at 11:27 am
So what's wrong with the results?
Sorry after rereading my post I realized that I put in the wrong Table name I need to add. I need to add a Column name TASK ID pulled from the table name TASK. Here is an example from the table. TaskID 33466396-8AAF-48A6-985D-005C3AE36963 Name Holiday when I try to add the task name to the first Select I get nothing back and when I add to the second Select I see it but no data comes back.
Any reason why you do UNION instead of UNION ALL
That would be because of my lack of T-SQL knowledge 🙂 I'm new to developing these scripts.
Thanks,
D
December 6, 2011 at 11:32 am
Get back to 1 query at a time to add those filters.
Start by selecting the columns before applying the filter.
You can dump the results (copy / paste) into excel and then apply the filter.
My guess is that you don't have the correct name in the filter or that you have no data to return.
Could also be case sensitivity (rare setup but it happens).
December 6, 2011 at 12:20 pm
Thank you for the tips I was wondering can you suggest how I would or even if I could Inner Join two tables that do not have PK related?
For example I'm breaking the script down and want to see if I can get that Task column added.
SELECT r.name as Resource_Name,rw.name as Business_Unit,t2.name
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
December 6, 2011 at 12:29 pm
There is no need for two tables having a FK relationship to be able to use it in a join condition.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 6, 2011 at 3:15 pm
david.ostrander (12/6/2011)
Thank you for the tips I was wondering can you suggest how I would or even if I could Inner Join two tables that do not have PK related?For example I'm breaking the script down and want to see if I can get that Task column added.
SELECT r.name as Resource_Name,rw.name as Business_Unit,t2.name
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
I don't see any reason this wouldn't work. If you have a specific case, please post DDL, sample data and expected output so we can provide a tested solution.
December 7, 2011 at 6:52 am
ok let me see if I can provide what is needed. Here is the script broken down with no filters added. The new column name is in bold with the Inner Join.
SELECT r.name as Resource_Name,rw.name as Business_Unit,t.name
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
INNER JOIN Tasks t WITH(NOLOCK) ON t.taskid = ta.taskid
WHERE r.deleted = 0
When I run the above script I get the following error in SSMS.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "ta.taskid" could not be bound.
If the above script runs currently without the Task two columns are returned with users name and what department they work in. The new column Task would return if they are missing a PTO or Hoilday entry. I attached an image of what the current output is.
Please let me know what else you might need.
Thank you
December 7, 2011 at 6:59 am
david.ostrander (12/7/2011)
SELECT r.name as Resource_Name,rw.name as Business_Unit,t.nameFROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
INNER JOIN Tasks t WITH(NOLOCK) ON t.taskid = ta.taskid
WHERE r.deleted = 0
When I run the above script I get the following error in SSMS.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "ta.taskid" could not be bound.
Fairly sure that you missed a table.
SELECT r.name AS Resource_Name, rw.name AS Business_Unit, t.name
FROM Resources r
INNER JOIN Workgroupmember wgm ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw ON rw.workgroupid = wgm.workgroupid
INNER JOIN TaskAssignment ta ON rs.Resourceid = ta.Resourceid
INNER JOIN Tasks t ON t.taskid = ta.taskid
WHERE r.deleted = 0
Also, read the article you were pointed at to discover why you probably shouldn't be using NOLOCK, it is not a "go faster switch" 😉 --> http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply