Reporting Records in groups of 10

I have a SQL 2000 table with the following fields:
Order_id
cust_pn
Seq_nbr
chassis_nbr
Qty
The cust_pn ends with an "R" if it's a right-hand part otherwise it's a
left-hand part.
For a given order_id, I need to report the part numbers in groups of 10
lefts, 10 rights, 10 lefts and so one.
Does anyone know of a way to do this? Thanks!Data types, sample data, desired results?
http://www.aspfaq.com/5006
"prenfrow" <prenfrow@.discussions.microsoft.com> wrote in message
news:C7E29EAF-2CE2-44F3-A282-78149A5A949D@.microsoft.com...
>I have a SQL 2000 table with the following fields:
> Order_id
> cust_pn
> Seq_nbr
> chassis_nbr
> Qty
> The cust_pn ends with an "R" if it's a right-hand part otherwise it's a
> left-hand part.
> For a given order_id, I need to report the part numbers in groups of 10
> lefts, 10 rights, 10 lefts and so one.
> Does anyone know of a way to do this? Thanks!
>|||I hope this is ok:
CREATE TABLE [dbo].[tblOrders] (
[ORDER_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LINE_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITEM_ID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUST_PN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY] [decimal](20, 0) NULL ,
[CHASSIS_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LINE_SEQ_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BUILD_STATION] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_SCHEDULED] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CUSTOMER_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_SHIP_REQD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'164882','P1410C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'174984','P1416C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'175627','P1424C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'158190','P1435C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'174601','P1439C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'172491','P1407C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'173813','P1408C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'175620','P1411C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'169974','P1413C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'161421','P1415C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'166073','P1417C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
5','R25-1096-217111','DR
ASSY LH DL ELE
STDSTP','R25-1096-217111',1,'163603','P1437C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
4','R25-1096-216221','DR
ASSY LH DL ELE RSTSTP
BLCK','R25-1096-216221',1,'996384','P1421C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
3','R25-1096-216121','DR
ASSY LH DL ELE STDSTP
BLCK','R25-1096-216121',1,'992741','P1447C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
2','R25-1096-216111','DR
ASSY LH DL ELE
STDSTP','R25-1096-216111',1,'169264','P1444C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
1','R25-1096-113221','DR
ASSY LH DL MAN RSTSTP
BLCK','R25-1096-113221',1,'996487','P1402C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
1','R25-1096-113221','DR
ASSY LH DL MAN RSTSTP
BLCK','R25-1096-113221',1,'170399','P1406C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
0','R25-1096-113211','DR
ASSY LH DL MAN
RSTSTP','R25-1096-113211',1,'165294','P1445C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
9','R25-1096-113121','DR
ASSY LH DL MAN STDSTP
BLOCK','R25-1096-113121',1,'158190','P1435C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
8','R25-1096-113111','DR
ASSY LH DL MAN
STDSTP','R25-1096-113111',1,'167500','P1422C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
3','D5301-1025','DAYLITE
DR ASSY,ELE LH
W/BLOCK','R25-1077-2121211',1,'992577','P1445C1','82','04/13/2006','46889','
KENWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
2','D5301-1003','DAYLITE
DR ASSY, MANUAL
W/90','R25-1077-1123211',1,'995181','P1426C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
I need to be able to report line_seq_nbr, cust_pn, chassis_nbr in groups of
10 lefts, 10 rights and so on based on the last character of the cust_pn (se
e
below) for a selected order_id and order then by line_seq_nbr
Thanks for your help!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Data types, sample data, desired results?
> http://www.aspfaq.com/5006
>
> "prenfrow" <prenfrow@.discussions.microsoft.com> wrote in message
> news:C7E29EAF-2CE2-44F3-A282-78149A5A949D@.microsoft.com...
>
>|||What is the key for the table ?
Anith|||There isn't one for this table.
"Anith Sen" wrote:

> What is the key for the table ?
> --
> Anith
>
>|||I'm not entirely clear on your requirements,
however, assuming that ORDER_ID,LINE_SEQ_NBR is unique,
this should give you what you want
SELECT ORDER_ID,
LINE_SEQ_NBR,
CUST_PN,
CHASSIS_NBR
FROM (
SELECT t1.ORDER_ID,
CASE WHEN RIGHT(RTRIM(t1.CUST_PN),1)='R' THEN 1 ELSE 0 END AS
LR,
t1.LINE_SEQ_NBR,
t1.CUST_PN,
t1.CHASSIS_NBR,
(SELECT COUNT(*)/10
FROM tblOrders t2
WHERE t2.ORDER_ID=t1.ORDER_ID
AND ((RIGHT(RTRIM(t2.CUST_PN),1)='R' AND
RIGHT(RTRIM(t1.CUST_PN),1)='R') OR
(RIGHT(RTRIM(t2.CUST_PN),1)<>'R' AND
RIGHT(RTRIM(t1.CUST_PN),1)<>'R'))
AND t2.LINE_SEQ_NBR < t1.LINE_SEQ_NBR) As Num
FROM dbo.tblOrders t1
) X
ORDER BY ORDER_ID,Num,LR,LINE_SEQ_NBR|||WOW! That's exactly what I wanted!! Works perfectly!! Thanks!!!!!!
"markc600@.hotmail.com" wrote:

> I'm not entirely clear on your requirements,
> however, assuming that ORDER_ID,LINE_SEQ_NBR is unique,
> this should give you what you want
>
> SELECT ORDER_ID,
> LINE_SEQ_NBR,
> CUST_PN,
> CHASSIS_NBR
> FROM (
> SELECT t1.ORDER_ID,
> CASE WHEN RIGHT(RTRIM(t1.CUST_PN),1)='R' THEN 1 ELSE 0 END AS
> LR,
> t1.LINE_SEQ_NBR,
> t1.CUST_PN,
> t1.CHASSIS_NBR,
> (SELECT COUNT(*)/10
> FROM tblOrders t2
> WHERE t2.ORDER_ID=t1.ORDER_ID
> AND ((RIGHT(RTRIM(t2.CUST_PN),1)='R' AND
> RIGHT(RTRIM(t1.CUST_PN),1)='R') OR
> (RIGHT(RTRIM(t2.CUST_PN),1)<>'R' AND
> RIGHT(RTRIM(t1.CUST_PN),1)<>'R'))
> AND t2.LINE_SEQ_NBR < t1.LINE_SEQ_NBR) As Num
> FROM dbo.tblOrders t1
> ) X
> ORDER BY ORDER_ID,Num,LR,LINE_SEQ_NBR
>