January 31, 2012 at 12:18 pm
I am having an issue with a join and was hoping one of you could help me. Here is a little background. FYI this is pretty confusing so the odds of me explaining this clearly is LOW, but I will do my best.
Table1
TradeId (unique Id)
ext (employee ext)
CallDate (self explanatory)
phonenumber (self explanatory)
duration (self explanatory)
For ease of reporting I created a view based off of this query (Select tradeId, ext, max(calldate) as LastCall from t_CallLogs group by TradeId, ext).
A little more background, our brokers have certain clients assigned to them, but it is possible that any broker could talk to them. Ownership wants to see when the broker responsible last spoke to them, not the last time anybody talked to them. So it is possible that id 123456 will have more than 1 record in that view.
Now I need to query all the brokers accounts and the last time the specific broker contacted them.
Table 2 (simplified)
TradeId
CompName
Broker
if I query just that table based on Broker = 'NC', I get 140 rows.
if I take my attempt at the join I get 331 rows. This is my actual query so the field names above may not match exact
SELECT t1.Sales_ID, t1.tradeid, t1.compname, t1.ActualBalance, t1.Sales, t1.Purch, t1.Last_Purchase, t1.Last_Sale, t2.CallDate, t2.BrokerId
FROM Table1 AS t1 LEFT OUTER JOIN
Table2 AS t2 ON t1.tradeid = t2.TradeId
WHERE (t1.Sales_ID = 'nc')
My confusion is how do I link to the broker. if I add t2.BrokerId = 222, then I get 118 rows. (Here is some more data, of those 140 rows stated above, 126 have been contacted, 14 have not.) So the 118 may be just the ones broker 222 contacted, but I lose the ones that have NOT been contacted and the ones contacted but not by THIS broker.
I hope this makes sense and I hope someone has a thought on it.
John
January 31, 2012 at 12:49 pm
Well you were correct that you wouldn't be able to explain it clearly. 🙂 I certainly got lost three of four time through the explanation. However there is hope. The best thing you can do to explain questions in a forum such as this is to provide some details so others can help. This is best done by posting ddl (create table scripts), sample data (insert statements) and desired output based on the sample data. For details about the best way to post this info and how to put it all together take a look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2012 at 1:04 pm
Sean Lange (1/31/2012)
Well you were correct that you wouldn't be able to explain it clearly. 🙂 I certainly got lost three of four time through the explanation. However there is hope. The best thing you can do to explain questions in a forum such as this is to provide some details so others can help. This is best done by posting ddl (create table scripts), sample data (insert statements) and desired output based on the sample data. For details about the best way to post this info and how to put it all together take a look at the first link in my signature.
John...I totally agree with Sean's comment above.
The better the data you provide now, the better (and quicker) you will get tried and tested answers....also it limits any confusion going fwd because we are all working on the same set of data...hth
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply