Pl Help Me in Getting This SQL

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

SQL Server Development (2000)

Pl Help Me in Getting This SQL

Author

Topic

sree432
Starting Member

7 Posts

Posted - 2004-12-01 : 03:10:59

Input Table ‘T1’ Contains Following Data
CNAME PARTTYPE PARTID JDATE QTY
LUCENT TELECOM TB1 01/12/70 1000
TEXAS TELECOM TB2 05/02/75 2000
TEXAS AUTO AM1 10/25/85 1000
LUCENT AUTO AM2 07/18/92 1500
TEXAS AUTO AM3 01/17/93 3500
LUCENT TELECOM TB3 09/28/97 2500

Output Data Should Be Like Following

CNAME PARTTYPE PARTID JDATE QTY CID PID WEEKS
LUCENT TELECOM TB1 01/12/70 1000 C2 P2.3 135
TEXAS TELECOM TB2 05/02/75 2000 C1 P2.2 34
TEXAS AUTO AM1 10/25/85 1000 C1 P1.3 16
LUCENT AUTO AM2 07/18/92 1500 C2 P1.2 67
TEXAS AUTO AM3 01/17/93 3500 C1 P1.1 13
LUCENT TELECOM TB3 09/28/97 2500 C2 P2.1 25

For CID
Is an incremental field with ‘C’ as prefix
It is derived based on ‘Qty’ field
So, C1 is given to a Company (based on CNAME) where it got max Qty
Like that C2 for next highest Company etc.,

For PID
Is got ‘Pa.b’ format, Where ‘P’ Prefix
How to get a :
Based on ‘PARTTYPE’ a = 1, when it has got max qty
How to get b :
Based on ‘PARTID’ b = 1, when it has got max qty with in ‘PARTTYPE’

For WEEKS
How many weeks from ‘JDATE’ to Current Date

Any Method you can Use to Get Output, But It Should Be In an Optimal way.

Please help me in getting this SQL

Thanks in advance for all participents

Regards
Sreedhar Reddy

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-01 : 22:13:02

Whatever you do... Dont look at the execution plan on this one - It is perhaps the largest one I have witnessed

create table t1 (CNAME varchar(20), PARTTYPE varchar(20), PARTID varchar(10), JDATE datetime, QTY int)

insert into t1
select 'LUCENT', 'TELECOM', 'TB1', '01/12/04', 1000 union all
select 'TEXAS', 'TELECOM', 'TB2', '05/02/75', 2000 union all
select 'TEXAS', 'AUTO', 'AM1', '10/25/85', 1000 union all
select 'LUCENT', 'AUTO', 'AM2', '07/18/92', 1500 union all
select 'TEXAS', 'AUTO', 'AM3', '01/17/93', 3500 union all
select 'LUCENT', 'TELECOM', 'TB3', '09/28/97', 2500

select 
	t1.*,
	CID ,
	d2.PID + '.' + 
	(
		select cast(count(1)+1 as varchar(10)) 
		from t1 t 
		where t.parttype = t1.parttype and t.qty > t1.qty
	) PID,
	datediff(ww,jdate,getdate()) WEEKS
from t1
join
(
	select 
		cname,
		(	select 'C' + cast(count(1)+ 1 as varchar(5)) 
			from 
			(
				select top 100 percent cname, sum(qty) qty 
				from t1 
				group by cname 
				order by sum(qty) desc
			) d 
			where d.qty > d2.qty
		) CID
	from
	(
		select top 100 percent cname, sum(qty) qty 
		from t1 
		group by cname
		order by sum(qty) desc
	) d2
) d on d.cname = t1.cname

join
(
	select 
		parttype,
		(	select 'P' + cast(count(1)+ 1 as varchar(5)) 
			from 
			(
				select top 100 percent parttype, sum(qty) qty 
				from t1 
				group by parttype 
				order by sum(qty) desc
			) d 
			where d.qty > d3.qty
		) PID	
	from
	(
		select top 100 percent parttype, sum(qty) qty 
		from t1 
		group by parttype
		order by sum(qty) desc
	) d3 
) d2 on t1.parttype = d2.parttype

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources