March 16, 2011 at 7:38 am
I am facing the following problem. I want to make a join between two tables (lets say A, B).
A
Deal Number -Step Number
125 | 1
125 | 2
125 | 0
B
Deal Number- Deal Status- Step Number
125 | Beg | 1
125 | Pro | 1
125 | Beg | 2
125 | Opn | 0
The Join fields will be the deal number field and step number. In what concerns step no = 0 or step no = 2 there is no problem. However I want to set a condition for step no = 1 and for all the steps where for one row with a specific step in A there are more that one corresponding in B. So lets say I know that in this case if there are two or more records in B with step no = 1 then it is 'Pro' deal status tha must be selected over the "Beg" one.*
*Combination of (Deal Status + Step Number) are unique.
How can I accomplish that?
March 16, 2011 at 8:25 am
What exactly are you trying to do? Given the data you posted what is the desired output?
_______________________________________________________________
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/
March 16, 2011 at 8:41 am
Well the desired output would be a table C with the following rows
C
Deal Number - Deal Status - Step Number
125 | Pro | 1
125 | Beg | 2
125 | Opn | 0
Whta I want to do is import the deal status field in my joined table, but I dont want to have all the possible rows. In case there are more than one rows in table B with the same combination of deal number and step number then I want to select only one of them based a rule tha exists (e.g. 'Pro' deal status is newer that 'Beg' and it is the one to be selected).
I hope my question is clear. 🙂
March 16, 2011 at 9:00 am
It is a pretty odd requirement but then this is obviously not the real table structure and data.
I created the ddl statements and data inserts so that I can test this. This is something that help you get help more readily in the future as it makes it easier for somebody to test their results.
create table a
(
DealNumber int,
StepNum int
)
go
create table b
(
DealNumber int,
DealStatus char(3),
StepNum int
)
go
insert a
select 125, 1 union all
select 125, 2 union all
select 125, 0
insert b
select 125, 'Beg', 1 union all
select 125, 'Pro', 1 union all
select 125, 'Beg', 2 union all
select 125, 'Opn', 0
select a.DealNumber, case MAX(case b.DealStatus when 'Pro' then 2 when 'Beg' then 1 when 'Opn' then 0 end) when 2 then 'Pro' when 1 then 'Beg' when 0 then 'Opn' end,
a.StepNum
from a
join b on a.DealNumber = b.DealNumber and a.StepNum = b.StepNum
group by a.DealNumber, a.StepNum
_______________________________________________________________
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/
March 16, 2011 at 10:02 am
Thank you Sean! I will look into it.
March 16, 2011 at 12:24 pm
You can also put a CASE statement in your ON clause, forcing the code to make up a number or default to a value that will keep the records that don't match.
Or if Table A will always have some records that don't show up in Table B (and not vice versa), then do a LEFT OUTER JOIN and use the CASE in the SELECT statement to default the Deal Status.
March 17, 2011 at 4:44 am
Brandie Tarvin (3/16/2011)
You can also put a CASE statement in your ON clause, forcing the code to make up a number or default to a value that will keep the records that don't match.Or if Table A will always have some records that don't show up in Table B (and not vice versa), then do a LEFT OUTER JOIN and use the CASE in the SELECT statement to default the Deal Status.
Thanks Brandie!
That's what I have in mind but cannot unfortunately put it to code. What I want to do is set a control that everytime table B happens to have the same step number for two specific deal status that is should choose one of them, otherwise take the default one.
something like that
select #a.DealNumber, #b.DealStatus, #a.StepNum
from #a
join #b on #a.DealNumber = #b.DealNumber and #a.StepNum = #b.StepNum and #b.DealStatus in (Case when "there is Pro and Beg with same step Number" then 'PRO' else #b.DealStatus)
group by #a.DealNumber, #a.StepNum
March 17, 2011 at 5:39 am
hector__21 (3/17/2011)
Brandie Tarvin (3/16/2011)
You can also put a CASE statement in your ON clause, forcing the code to make up a number or default to a value that will keep the records that don't match.Or if Table A will always have some records that don't show up in Table B (and not vice versa), then do a LEFT OUTER JOIN and use the CASE in the SELECT statement to default the Deal Status.
Thanks Brandie!
That's what I have in mind but cannot unfortunately put it to code. What I want to do is set a control that everytime table B happens to have the same step number for two specific deal status that is should choose one of them, otherwise take the default one.
What are you wanting to define as default? Will the progression of DealStatus always be "Opn", "Beg", "Pro" or is there a different order?
If there will be no missing data from Table B, keep the CASE functionality in the SELECT statement. You can compare by count.
select #a.DealNumber,
CASE WHEN Count(#b.DealStatus) = 1 THEN #b.DealStatus
WHEN Count(#b.DealStatus) > 2 THEN MAX(#b.DealStatus)
--Assuming my progression is correct
ELSE "Opn" END as DealStatus, #a.StepNum
from #a
join #b
on #a.DealNumber = #b.DealNumber
and #a.StepNum = #b.StepNum
group by #a.DealNumber, #a.StepNum
As is, there is a problem with my above written code, so don't take it to the bank. Because not all DealStatus values are aggregated, they are required to be in the GROUP BY clause, which negates the Count() function. The best way to resolve the issue would be with an additional subquery joined to the record. Without knowing how you plan to default, or the progression of DealStatus, I can't fix it appropriately.
Once you figure out what you want the default to do, let us know and we'll get you code that's better than what I just posted.
March 17, 2011 at 6:14 am
Exactly the same problem I had with "count" too. Group By(and lack of experience :P) in general is killing me.
As default I want it to get the value that the field has, that is #b.DealStatus. I only want it to choose the 'Pro' status row over the 'Beg' one when two of them with the same step number exist. I hope I am making my self clear
I experimented with Sean's suggestion also and I have almost found a solution but the problem is with Group By again. If I set a specific value in the else clause it works. If on the other hand I set else to the field itself it requires the field in the group by clause and then "case" stops working.
what I have now is something like that:
SELECT a.DEAL_NO ,
b.Step_NO ,
a.amount ,
b.cust_id ,
b.amount2 ,
CASE MAX(CASE b.status
WHEN 'PRO' THEN 2
WHEN 'BEG' THEN 1
ELSE 0
END)
WHEN 2 THEN 'PRO'
WHEN 1 THEN 'BEG'
ELSE b.status
END AS Deal_Status
FROM table1 a
INNER JOIN table2 b ON a.dateid = b.dateid
AND a.DEAL_NO = b.DEAL_NO
AND a.Step_NO = b.Step_NO
AND a.cust_id = b.cust_id
WHERE a.dateid = 5
AND a.DEAL_NO = '125'
GROUP BY a.DEAL_NO ,
b.Step_NO ,
a.amount ,
b.cust_id ,
b.amount2
Deal_Status
P.S. By the way I have more than 3 different deal status so that is why I want it to get the field as default and I do not set the third one in the Else alternative.
March 17, 2011 at 8:29 am
There may be some mileage in preprocessing table b (as a derived table) before joining to table a:
SELECT a.DealNumber, b.DealStatus, a.StepNum
FROM a
LEFT JOIN (
SELECT DealNumber, DealStatus, StepNum,
IDInGroup = ROW_NUMBER() OVER(PARTITION BY DealNumber, StepNum ORDER BY DealStatus DESC)
FROM b
) b ON b.DealNumber = a.DealNumber AND b.StepNum = a.StepNum
AND (b.StepNum <> 1 OR b.IDInGroup = 1)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 17, 2011 at 9:37 am
Thank you Chris! That really helped me a lot! Think I am getting close now.:-D
March 17, 2011 at 12:26 pm
Chris's suggestion was where I was going with my subquery comment.
Consider also if you'll ever have Opn and Beg as Statuses, or Opn and Pro as opposed to the Beg and Pro you currently have in the system. That will also change how you want your code to work.
March 22, 2011 at 1:35 am
It did work after some changes so thank you all for your help!
March 22, 2011 at 5:54 am
Glad we could help. Mind posting your solution in case anyone has a similar issue?
March 22, 2011 at 6:23 am
Gladly! 🙂
What I did was create a temporary table to hold the grouped data and then selected those with the id = 1. If it was a default status it always hold 1 as a value and when there where 2 steps with the same number the 'PRO' status also possesed status 1 so i finally got the rows I wanted.
SELECT Deal_no, Deal_Status, Step_No,
GroupID = ROW_NUMBER() OVER(PARTITION BY Deal_No , Step_No ORDER BY Deal_Status DESC)
Into #b
FROM b
SELECT a.DEAL_NO ,
b.Step_NO ,
a.amount ,
b.cust_id ,
b.amount2 ,
b.Status
FROM table1 a
INNER JOIN table2 b ON a.dateid = b.dateid
AND a.DEAL_NO = b.DEAL_NO
AND a.Step_NO = b.Step_NO
AND a.cust_id = b.cust_id
AND (b.deal_no + b.status + b.step_no) in (select (deal_no + deal_status + step_no) from #b where GroupID = 1)
WHERE a.dateid = 5
AND a.DEAL_NO = '125'
GROUP BY a.DEAL_NO ,
b.Step_NO ,
a.amount ,
b.cust_id ,
b.amount2,
b.status
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply