January 15, 2012 at 11:43 pm
I have two views in the database.
One of the views will give me the status of the call and the other view will give me the rest of the call details.
In the first view, for each call ID, an unique entry is made on the basis of the status of the call.
How can I join these two views to get me the data from the second view, and only one of the status from the First view.
Like say for example, the last call status is : Pending.
Previous statuses were Open, Awaiting, Doingsomethingelse, etc.
Now in the first view, there is one record for each of the statuses against the Call ID.
If I say, Call Id is 19080, the I am expecting the following output:
19080 - Pending
Now I need to know the latest status of the call against the call id.
Can someone help me here?
January 16, 2012 at 3:02 am
Please post table scripts (CREATE TABLE statements) for all tables / views in your query.
Make sure to provide some sample data and describe the expected output based on your sample data.
If in doubt, see the article linked in my signature line.
-- Gianluca Sartori
January 17, 2012 at 5:49 am
I strongly recommend you DO NOT join the two views together. Joining one view to another or nesting views within each other leads to incredibly complex execution plans which can cause very poor performance.
Instead, identify the logic needed from each view and combine it in a new query. That's the best way to get this done, even if it's not the easiest.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2012 at 6:03 am
Ok.. Agreed! I will not join two views.. I will join the underlying tables .. But!! What logic do I use ? Because be it a table/ view the problem is the same. I get two rows for one Call ID.
Below is the sample Data:
Table 1 :
Call_Id ||Call_Description || Call_Other_Details
1 ||Test call || xxx
Table 2 :
Call_Id ||Call_Status ||Other_Details
1 ||Response Violated || yyy
1 ||Resolution Violated || bbb
Now, I need to know if the Call ID =1 is response violated(yes/no) , resolution violated (yes/no)
I hope this is more clear than my previous description.
January 17, 2012 at 6:18 am
Simply joining the tables, yes, you're going to return two rows. So, what are the filter criteria that makes it so you'll only get one? Add that to a WHERE clause or to the ON clause of the JOIN (logically, if it's a filter, put it in the WHERE clause).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2012 at 6:37 am
You have mentioned that if it is filter criteria, put it in the WHERE condition. However, the expectation is like this:
Against the Call ID, I would need to give the details if the call has been Response violated or not, Resolution Violated or not. This actually means, I would have to convert two rows in one table into two columns. Should I be using a PIVOT here ? Can a PIVOT solve my problem?
January 17, 2012 at 6:39 am
Pivot? Yup.
DECLARE @Table1 TABLE (
Call_Id int NOT NULL,
Call_Description nvarchar(50),
Call_Other_Details nvarchar(50)
)
INSERT INTO @Table1 VALUES(1, 'Test call', 'xxx')
DECLARE @Table2 TABLE (
Call_Id int NOT NULL,
Call_Status nvarchar(50),
Other_Details nvarchar(50)
)
INSERT INTO @Table2 VALUES(1, 'Response Violated', 'yyy')
INSERT INTO @Table2 VALUES(1, 'Resolution Violated', 'bbb')
SELECT *
FROM @Table1
CROSS APPLY (
SELECT Call_Id, ISNULL([Response Violated],'N') AS Response, ISNULL([Resolution Violated],'N') AS Resolution
FROM (
SELECT Call_Id, 'S' AS value, Call_Status
FROM @Table2
) AS src
PIVOT ( MIN(value) FOR Call_Status IN([Response Violated],[Resolution Violated]) ) AS pvt
) AS src2
-- Gianluca Sartori
January 17, 2012 at 6:52 am
Perfect! Perfect! Perfect! This should work. I will check this on my query and I will update if this displays the result set the way I want it to be..
Thank you very much!
--
Regards
Sharada
January 17, 2012 at 7:49 am
Good to see you got the answer. Thanks Gianluca
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2012 at 7:53 am
Thanks to you as well Grant. 🙂
--
Regards
Sharada
January 17, 2012 at 8:16 am
sharada.mn8 (1/17/2012)
Perfect! Perfect! Perfect! This should work. I will check this on my query and I will update if this displays the result set the way I want it to be..Thank you very much!
--
Regards
Sharada
You're welcome.
Glad I could help
-- Gianluca Sartori
January 25, 2012 at 2:11 am
Well.. I ended up using CTE instead of PIVOT and still gave me correct results.. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply