June 18, 2012 at 8:14 am
Table structures
DECLARE @team table (emp_code int,old_emp_code int, emp_name varchar(20))
insert into @team (emp_code,old_emp_code, emp_name)
select 1, 100,'ABA' UNION ALL
select 2, 200,'ABB' UNION ALL
select 3, 300,'ABC' UNION ALL
select 4, 400,'ABD' UNION ALL
select 5, 500,'ABE' UNION ALL
select 6, 600,'ABF' UNION ALL
Note: in @team emp_code is the new employee identifier while old_emp_code is old identifier.
declare @detail table (emp_code int, emp_amount decimal(32,2))
insert into @detail (emp_code , emp_amount)
select 1, 400.00 UNION ALL
select 2, 900.00 UNION ALL
select 100, 500.00 UNION ALL
select 200, 2300.00 UNION ALL
select 300, 4300.00 UNION ALL
select 600, 2300.00 UNION ALL
Note: in @detail emp_code contains entry from emp_code and old_emp_code of @team table.
Note: want to @team.emp_code and @team.old_emp_code to compare @detail.emp_code
My expected output
emp_code| old_emp_code | emp_name | emp_amount
1100'ABA' '500' or 400 (pick 1)
2200'ABB' 2300 or 900 (pick 1)
3300'ABC' 4300
4400'ABD' 0.00
5500'ABE' 0.00
6600'ABF' 2300
Many thanks.
June 18, 2012 at 8:42 am
Okay, so what is the question/problem?
June 18, 2012 at 8:55 am
The question is how can I achieve the expected/desired result with tsql using the 2 table structures I specified.
Thanks Boss
June 18, 2012 at 8:58 am
Olalekan Adewale (6/18/2012)
The question is how can I achieve the expected/desired result with tsql using the 2 table structures I specified.Thanks Boss
Now, show us what you have done in an effort to solve your problem.
June 18, 2012 at 9:03 am
I fail to see the point of this. Care to enlighten me? Why does it not matter which "emp_amount" is displayed? Data integrity is always important - I can't imagine that this is the first case where it isn't.
How's this?
SELECT a.emp_code, a.old_emp_code, a.emp_name, MAX(ISNULL(b.emp_amount,0.00)) AS emp_amount
FROM @team a
LEFT OUTER JOIN @detail b ON a.emp_code = b.emp_code OR a.old_emp_code = b.emp_code
GROUP BY a.emp_code, a.old_emp_code, a.emp_name;
Returns -
emp_code old_emp_code emp_name emp_amount
----------- ------------ -------------------- ---------------------------------------
1 100 ABA 500.00
2 200 ABB 2300.00
3 300 ABC 4300.00
4 400 ABD 0.00
5 500 ABE 0.00
6 600 ABF 2300.00
June 18, 2012 at 9:06 am
Olalekan Adewale (6/18/2012)
Table structures
DECLARE @team table (emp_code int,old_emp_code int, emp_name varchar(20))
insert into @team (emp_code,old_emp_code, emp_name)
select 1, 100,'ABA' UNION ALL
select 2, 200,'ABB' UNION ALL
select 3, 300,'ABC' UNION ALL
select 4, 400,'ABD' UNION ALL
select 5, 500,'ABE' UNION ALL
select 6, 600,'ABF' UNION ALL
Note: in @team emp_code is the new employee identifier while old_emp_code is old identifier.
You do know that the code post above has a problem and won't run as is, right? Can you tell me what is wrong with your code?
declare @detail table (emp_code int, emp_amount decimal(32,2))
insert into @detail (emp_code , emp_amount)
select 1, 400.00 UNION ALL
select 2, 900.00 UNION ALL
select 100, 500.00 UNION ALL
select 200, 2300.00 UNION ALL
select 300, 4300.00 UNION ALL
select 600, 2300.00 UNION ALL
Note: in @detail emp_code contains entry from emp_code and old_emp_code of @team table.
Note: want to @team.emp_code and @team.old_emp_code to compare @detail.emp_code
My expected output
emp_code| old_emp_code | emp_name | emp_amount
1100'ABA' '500' or 400 (pick 1)
2200'ABB' 2300 or 900 (pick 1)
3300'ABC' 4300
4400'ABD' 0.00
5500'ABE' 0.00
6600'ABF' 2300
Many thanks.
June 19, 2012 at 3:04 am
It could be done as follows:
;With CTE
As
(Select a.emp_code, a.old_emp_code, a.emp_name, b.emp_amount,
ROW_NUMBER() Over (Partition By a.emp_code Order by (Select NULL) ) As rn
From @team As a
Left JOIN @detail As b ON (a.emp_code = b.emp_code OR a.old_emp_code = b.emp_code) )
Select x.emp_code, x.old_emp_code, x.emp_name,
(Case When x.emp_amount IS NULL AND y.emp_amount IS NULL Then ''
When x.emp_amount IS NOT NULL AND y.emp_amount IS NULL Then IsNULL(Cast(x.emp_amount AS Varchar(30)), '')
Else CAST(x.emp_amount As Varchar(30)) +' OR '+ CAST(y.emp_amount As varchar(30))
End) As Amount
From CTE As x
Left JOIN CTE As y ON x.emp_code = y.emp_code AND y.rn = (x.rn + 1)
Where x.rn = 1
It was a little more complex than it actually looked.
But, I am sure that the SQL Gurus at SSC would come up with a better and less complex solution.
Still hope that this helps.
June 19, 2012 at 4:25 am
Easy, peasy!
Just for fun, I'm going to take the OP's formatting literally.
DECLARE @team table (emp_code int,old_emp_code int, emp_name varchar(20))
insert into @team (emp_code,old_emp_code, emp_name)
select 1, 100,'ABA' UNION ALL select 2, 200,'ABB' UNION ALL
select 3, 300,'ABC' UNION ALL select 4, 400,'ABD' UNION ALL
select 5, 500,'ABE' UNION ALL select 6, 600,'ABF'
declare @detail table (emp_code int, emp_amount decimal(32,2))
insert into @detail (emp_code , emp_amount)
select 1, 400.00 UNION ALL select 2, 900.00 UNION ALL
select 100, 500.00 UNION ALL select 200, 2300.00 UNION ALL
select 300, 4300.00 UNION ALL select 600, 2300.00
SELECT emp_code, old_emp_code, emp_name
,CAST(ISNULL((
SELECT emp_amount
FROM @detail d
WHERE d.emp_code = t.old_emp_code), 0) AS VARCHAR)
+ CASE WHEN empamount IS NULL
THEN '' ELSE ' or ' + CAST(empamount AS VARCHAR) + ' (pick 1)' END
FROM @team t
CROSS APPLY (
SELECT
(SELECT emp_amount FROM @detail d WHERE d.emp_code = t.emp_code)
) x(empamount)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 5:04 am
June 19, 2012 at 6:58 am
Performance wise, solution given by Cadavre is good and Dwain's query is also good if the CASE statement is avoided. The CASE statement is adding some weight to the query's performance.
--Ravi.
Regards,
Ravi.
June 19, 2012 at 8:33 am
Thanks all..
Cadavre , vinu512 ,dwain.c, Ravi Kumar-191606
.. you all are wonderful .. God bless.
June 19, 2012 at 10:03 pm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply