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)  Tough SQL OutPut

Author  Topic  sree432 Starting Member 7 Posts Posted - 2004-11-26 : 06:34:03 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. Thanks in advance Regards Sreedhar Reddy