Any idea? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Any idea?

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-19 : 04:11:58

[code]
SELECT *
INTO #temp
FROM
(
SELECT 1 as id, '11' as st_type,'return excess' as descrp , 'NK-S180001' as stk_code, '1' as qty
union all
SELECT 2 as id, 12, 'return nc', 'NK-S180001', 2
union all
SELECT 3 as id, 3, 'sales', 'NK-S180001', '1'
union all
SELECT 4 as id, 11, 'return excess', 'NK-S180002', 1
union all
SELECT 5 as id, 12, 'return nc', 'NK-S180002', 2
union all
SELECT 6 as id, 3, 'sales', 'NK-S180002', 10
) as tblTrans

select * from #temp as t1
select
t1.st_type
,t1.descrp
,t1.stk_code
,(select qty from #temp where st_type = 3 and stk_code = t1.stk_code) as sales
,t1.qty as returned
from #temp as t1
where t1.st_type <> 3
group by st_type
,t1.descrp
,t1.stk_code
,t1.qty
order by
t1.stk_code
,st_type

drop table #temp
[/code]

RESULTS....

I.
[code]
ID st_type descrp stk_code qty
-----------------------------------------------------
1 11 return excess NK-S180001 1
2 12 return nc NK-S180001 2
3 3 sales NK-S180001 1
4 11 return excess NK-S180002 1
5 12 return nc NK-S180002 2
6 3 sales NK-S180002 10
[/code]

DESIRED RESULT
II.
[code]
ID st_type descrp stk_code SALES RETURNED
-----------------------------------------------------
11 return excess NK-S180001 1 1
12 return nc NK-S180001 1 2
11 return excess NK-S180002 10 1
12 return nc NK-S180002 10 2
[/code]

Any approach that will elimanate the use of subquery?

Tnx in advance!

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-19 : 04:20:57

Like this ?

select 	t1.st_type, t1.descrp, t1.stk_code, t2.qty as sales, t1.qty as returned
from	#temp as t1
inner join
(
	select	stk_code, qty
	from	#temp
	where	st_type	= 3
) as t2
on	t1.stk_code = t2.stk_code
where	t1.st_type	<> 3
order by  t1.stk_code, st_type

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-19 : 04:33:33

thanks khtan!

yup! how about not using a subquery?

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-19 : 04:57:34

quote:
Originally posted by jonasalbert20

thanks khtan!

yup! how about not using a subquery?

Want Philippines to become 1st World COuntry? Go for World War 3...

It isn't using subquery. It is derived table

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-19 : 05:01:21

Sorry for the term i used. :D

Yup! how about not using a derived table?

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-19 : 07:12:20

What is your requirement ? What's wrong with using sub query or derived table in your query ?

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-19 : 07:21:59

Im making a query plan to speed-up my query.

Just getting idea what would be the best approach. Using a sub query or a derived table is a speed factor. I have read a post here which I recall with same requirement and im having a whole day finding that thread.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-19 : 07:40:38

run both method of the query against your table and compare the execution plan & timing. It should gives you an idea which is better.
Maybe somebody else have more efficient method.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-20 : 06:02:57

Hi all,

Jonas - khtan's method is the best way to do this, and I don't think you'll find a better method. Put an index on st_type though (if you don't have one already) - that will speed things up.

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-20 : 07:14:26

Thnx RyanRandall, but i just want to find that thread first coz im sure it was a good approach. I'll just post it again, once ive seen that thread.

But for the meantime, I've used the derived table method.

Tnx for the reply guys.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 08:00:52

It will be interesting to see a better approach to this. Do post if you ever found it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources