asked on

Need help with SQL

I just don't do enough SQL to effectively know what I am doing wrong. I could use some help.

I'll set-out the tables as best I can, as follows:

WORKITEMS
workitem_id PK
workitem_type_id FK
acount_id FK
due_date
assigned_user_id

ACCOUNTS
account_id PK
name

WORKITEM_TYPES
workitem_type_id PK
name

WORKITEM_ACTIONS
workitem_action_id PK
workitem_id FK
start_date
end_date
action_id
reason_id

ACTION_REASONS
action_reason_id
action_id
reason_id

REASONS
reason_id PK
name
description

What I want is to select all rows from WORKITEM where assigned_user_id is a certain number.

Then I want to join to WORKITEM to ACCOUNTS so I can get the account name for each workitem

Then I want to join to WORKITEM to WORKITEM_TYPES so I can get the external_name

Then I want to join to WORKITEMS to WORKITEM_ACTIONS so I can find-out the end_date and action_id

Then I want to join to WORKITEM_ACTIONS to ACTION_REASONS so I can find out the reason_id

Then I want to join to REASONS to get the friendly name (the reason for the action)

AND

I want to do all of this based upon action_id being 1 or 3

Here is my current SQL attempt, which is not returning any rows...althoug based on the data in the tables I think it should be returning something (see snippet):

BEGIN
SELECT				
		cmsa.[name] as 'Account Name',		
		wt.external_name as 'Work Item Type',
		w.due_date as 'Due Date',
		wa.end_date as 'Pause/On Hold Date',
		r.[name] as 'Pause/On Hold Reason'
	FROM
		WORKFLOW..WORKITEMS (NOLOCK) w
			JOIN
		CMS..ACCOUNTS (NOLOCK) cmsa
			 ON w.account_id = cmsa.account_id
				JOIN
		WORKFLOW..WORKITEM_TYPES (NOLOCK) wt 
			ON w.workitem_type_id = wt.workitem_type_id
				JOIN
		WORKFLOW..WORKITEM_ACTIONS (NOLOCK) wa
			ON w.workitem_id = wa.workitem_id
				JOIN
		WORKFLOW..ACTION_REASONS (NOLOCK) ar
			ON wa.action_id = ar.action_id
				JOIN
		WORKFLOW..REASONS (NOLOCK) r
			ON ar.reason_id = r.reason_id
	WHERE
		w.assigned_user_id = @AssignedUserID
END

Open in new window

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

AielloJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Knowlton

ASKER

AielloJ:

If you are willing to work with me, that would be great.

Here is the first spoonfull of elephant:

select wt.external_name, w.workitem_id from workflow..workitems w
join workitem_types wt on w.workitem_type_id = wt.workitem_type_id
where w.assigned_user_id = 936

yields:

external_name workitem_id
NULL        6
NULL        8
Link Copy Creation 3      191
NULL        192
NULL        193
NULL        194

How do I exclude the nulls on the left?

Tom Knowlton

ASKER

Making progress.

select cmsa.[name], wt.external_name, w.workitem_id from workflow..workitems w
join workflow..workitem_types wt on w.workitem_type_id = wt.workitem_type_id
join cms..accounts cmsa on w.account_id = cmsa.account_id
where w.assigned_user_id = 936 and wt.external_name is not null

yields:

name external_name workitem_id
OrangeSoda, Inc. Link Copy Creation 3 191

So far so good....

Tom Knowlton

ASKER

I think I have it:

BEGIN
      SELECT cmsa.[name] as 'Account Name',
            wt.external_name as 'Work Item Type',
            w.due_date as 'Due Date',
            wa.end_date as 'Pause/On Hold Date',
            r.[name] as 'Pause/On Hold Reason'
      FROM WORKFLOW..WORKITEMS w
            JOIN WORKFLOW..WORKITEM_TYPES wt ON w.workitem_type_id = wt.workitem_type_id
            JOIN CMS..ACCOUNTS cmsa on w.account_id = cmsa.account_id
            JOIN WORKFLOW..WORKITEM_ACTIONS wa on w.workitem_id = wa.workitem_id
            JOIN WORKFLOW..REASONS r on wa.reason_id = r.reason_id
      WHERE
            w.assigned_user_id = @AssignedUserID and wt.external_name is not null
            and
            w.workitem_status_id in (30,40)
END

AielloJ

knowlton:

I was offline when you were doing most of your work. Didn't mean to not respond to your "If you are willing to work with me, that would be great." Looks like you found your answer.

JRA