Grouping records with dynamic groups for report

Question

Grouping records with dynamic groups for report

Chuck S

Old Hand

Points: 302
More actions
January 18, 2018 at 8:36 am

#325496

Hello All,
I'm not sure what is the best what to do this. I need to create groups for banding on a report. The groups are dynamic and are always changing. Here is the criteria for the groups.
If ExplodedKitItem is ‘Y’ andSkipPrintCompline = ‘N’ Then Beginning of group.
Group continues as long as SkipPrintCompLine= ‘Y’
If ExplodedKitItem = ‘N’ andSkipPrintCompLine = ‘N’ The this is a group.

If itemcode = ‘/G’ thenBeginning of group
Group continues till Itemcode =‘/GE’

Sample Data script.
CREATE TABLE #temptable (SalesOrderNo VARCHAR(7),
                         LineKey VARCHAR(6),
                         LineSeqNo VARCHAR(14),
                         ItemCode VARCHAR(30),
                         ItemType VARCHAR(1),
                         ItemCodeDesc VARCHAR(4096),
                         CommentText VARCHAR(2048),
                         ExplodedKitItem VARCHAR(1),
                         SkipPrintCompLine VARCHAR(1),
                         ReportGroup VARCHAR(1));
INSERT INTO #temptable
VALUES ('0202179', '000020', '00002100000000',
        'PT45P1A2P01-D-T-C3', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -30 inHg to 0
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass
Options: 3pt. Calibration Cert',
        '', 'Y', 'N', '1'),
       ('0202179', '000021', '00002200000000',
        'PT45P1A2P01VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 psi
Label as : PT45P1A2P01-D-T',
        '', 'N', 'Y', '1'),
       ('0202179', '000022', '00002300000000',
        'CCALPG-3', '1',
        'Certificate of Calibration, 3 Points Cert with Sticker
',
        '', 'N', 'Y', '1'),
       ('0202179', '000032', '00003300000000',
        'SG40ATC20P18', '1',
        '4" Sanitary Pressure Gauge
2" Triclamp Bottom Mount
316 SS Body and Diaphragm
Range: 0 to 100 psi
Dry Case (Field Fillable)
Lens:     Plastic
Seal Fill: Glycerin',
        '', 'Y', 'N', '2'),
       ('0202179', '000033', '00003400000000',
        'PR40S1A4P18VH', '1',
        '4" Stainless Steel Bayonet Case
Stainless Steel Tube and Socket
1/4" NPT Bottom Mount
0-100 psi
Label as : PR40S1A4P18-D-T',
        '', 'N', 'Y', '2'),
       ('0202179', '000034', '00003500000000',
        'DSTC20SS4-WLVR', '1',
        'Sanitary Triclamp Diaphragm Seal
Process: 2"Triclamp
Instrument: 1/4" NPS Female
316 SS Body and Diaphragm
with Welding Lip
(2.0 dia., .02 disp.)
Design required as shown in Dwg 50120 Rev X1
',
        '', 'N', 'Y', '2'),
       ('0202179', '000040', '00004100000000',
        'W51521FFS-TTDTD-AS', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon
Seal System Fill Fluid: Silicone DC200® Standard Fluid',
        '', 'Y', 'N', '1'),
       ('0202179', '000041', '00004200000000',
        'W51521FFS-TT-VZ', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon',
        '', 'N', 'Y', '2'),
       ('0202179', '000042', '00004300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y', '2'),
       ('0202179', '000054', '00003850000000',
        '/G', '4', 'begin group', 'begin group',
        'N', 'N', '1'),
       ('0202179', '000055', '00003975000000',
        'PT45P1A2A04-D-T', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -100 to 0 to 400 kPa
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass',
        '', 'Y', 'N', '1'),
       ('0202179', '000056', '00004037500000',
        'PT45P1A2P04VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 - 60 psi
Label as : PT45P1A2P04-D-T',
        '', 'N', 'Y', '1'),
       ('0202179', '000057', '00004068750000',
        'PXDIALPREPR', '1',
        'DIAL SWAP (W/RECAL) 2.5" TO 4.5" GAUGE
',
        '', 'N', 'Y', '1'),
       ('0202179', '000058', '00004084375000',
        'PXDP-A04-REO-45', '1', 'Dial Print', '',
        'N', 'Y', '1'),
       ('0202179', '000059', '00004400000000',
        '/GE', '4', 'end group', 'end group',
        'N', 'N', '1'),
       ('0202179', '000061', '00003225000000',
        'PMIREPORT-PG', '1',
        'Positive Material Identification Report
Pressure Gauge Socket Material
Per SP# 162',
        '', 'N', 'N', '2'),
       ('0202179', '000066', '00005900000000',
        'W9FFWR31S-B20-AS-R1', '1',
        'W9FF: Flanged Flush-Face Diaphragm Seal
W: Low-Volume Connection for Smart Transmitters
R: Sealing Face - Raised Face
31: Flange Size & Rating - 3" 150#
S: Wetted Parts - 316LSS Diaphragm and Flange
B20: Seal-to-instrument Connection - 20 feet of Armored Capillary, Welded to Seal
AS: Seal system Fill Fluid - Silicone DC200®
R1: Option - Direct Mount to Inline Transmitter',
        '', 'Y', 'N', '1'),
       ('0202179', '000067', '00006000000000',
        'DSXW9FF-WR31S', '1',
        'Flanged Flush Face Seal Body
3" 150# ANSI Raised Face Flange
316L
89mm diaphragm',
        '', 'N', 'Y', '1'),
       ('0202179', '000068', '00006100000000',
        'D316-89', '1',
        'Diaphragm, 89mm x .08mm 316L
per Dwg 10028 Rev B',
        '', 'N', 'Y', '1'),
       ('0202179', '000069', '00006200000000',
        'DXCWW20A', '1',
        'Capillary Assembly Armored Stainless Steel
2 Weld Connections
20 feet',
        '', 'N', 'Y', '1'),
       ('0202179', '000070', '00006300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y', '1'),
       ('0202179', '000071', '00006400000000',
        'DXCTRANS-G1', '1',
        'Single Diaphragm Seal Mount to Smart Transmitter
Gauge Pressure System
Mounted to In-Line/Direct Mount Transmitter',
        '', 'N', 'Y', '1'),
       ('0202179', '000072', '00006500000000',
        'CCALTRAN-GP', '1',
        'Certificate Of Calibration -Smart Transmitter
Gauge Pressure, 5 points',
        '', 'N', 'Y', '1');
DROP TABLE #temptable;
The script below is the what the table should look like after processing.

CREATE TABLE #temptable (SalesOrderNo VARCHAR(7),
                         LineKey VARCHAR(6),
                         LineSeqNo VARCHAR(14),
                         ItemCode VARCHAR(30),
                         ItemType VARCHAR(1),
                         ItemCodeDesc VARCHAR(4096),
                         CommentText VARCHAR(2048),
                         ExplodedKitItem VARCHAR(1),
                         SkipPrintCompLine VARCHAR(1),
                         ReportGroup VARCHAR(1));
INSERT INTO #temptable
VALUES ('0202179', '000020', '00002100000000',
        'PT45P1A2P01-D-T-C3', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -30 inHg to 0
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass
Options: 3pt. Calibration Cert',
        '', 'Y', 'N', '1'),
       ('0202179', '000021', '00002200000000',
        'PT45P1A2P01VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 psi
Label as : PT45P1A2P01-D-T',
        '', 'N', 'Y', '1'),
       ('0202179', '000022', '00002300000000',
        'CCALPG-3', '1',
        'Certificate of Calibration, 3 Points Cert with Sticker
',
        '', 'N', 'Y', '1'),
       ('0202179', '000032', '00003300000000',
        'SG40ATC20P18', '1',
        '4" Sanitary Pressure Gauge
2" Triclamp Bottom Mount
316 SS Body and Diaphragm
Range: 0 to 100 psi
Dry Case (Field Fillable)
Lens:     Plastic
Seal Fill: Glycerin',
        '', 'Y', 'N', '2'),
       ('0202179', '000033', '00003400000000',
        'PR40S1A4P18VH', '1',
        '4" Stainless Steel Bayonet Case
Stainless Steel Tube and Socket
1/4" NPT Bottom Mount
0-100 psi
Label as : PR40S1A4P18-D-T',
        '', 'N', 'Y', '2'),
       ('0202179', '000034', '00003500000000',
        'DSTC20SS4-WLVR', '1',
        'Sanitary Triclamp Diaphragm Seal
Process: 2"Triclamp
Instrument: 1/4" NPS Female
316 SS Body and Diaphragm
with Welding Lip
(2.0 dia., .02 disp.)
Design required as shown in Dwg 50120 Rev X1
',
        '', 'N', 'Y', '2'),
       ('0202179', '000040', '00004100000000',
        'W51521FFS-TTDTD-AS', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon
Seal System Fill Fluid: Silicone DC200® Standard Fluid',
        '', 'Y', 'N', '1'),
       ('0202179', '000041', '00004200000000',
        'W51521FFS-TT-VZ', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon',
        '', 'N', 'Y', '2'),
       ('0202179', '000042', '00004300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y', '2'),
       ('0202179', '000054', '00003850000000',
        '/G', '4', 'begin group', 'begin group',
        'N', 'N', '1'),
       ('0202179', '000055', '00003975000000',
        'PT45P1A2A04-D-T', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -100 to 0 to 400 kPa
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass',
        '', 'Y', 'N', '1'),
       ('0202179', '000056', '00004037500000',
        'PT45P1A2P04VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 - 60 psi
Label as : PT45P1A2P04-D-T',
        '', 'N', 'Y', '1'),
       ('0202179', '000057', '00004068750000',
        'PXDIALPREPR', '1',
        'DIAL SWAP (W/RECAL) 2.5" TO 4.5" GAUGE
',
        '', 'N', 'Y', '1'),
       ('0202179', '000058', '00004084375000',
        'PXDP-A04-REO-45', '1', 'Dial Print', '',
        'N', 'Y', '1'),
       ('0202179', '000059', '00004400000000',
        '/GE', '4', 'end group', 'end group',
        'N', 'N', '1'),
       ('0202179', '000061', '00003225000000',
        'PMIREPORT-PG', '1',
        'Positive Material Identification Report
Pressure Gauge Socket Material
Per SP# 162',
        '', 'N', 'N', '2'),
       ('0202179', '000066', '00005900000000',
        'W9FFWR31S-B20-AS-R1', '1',
        'W9FF: Flanged Flush-Face Diaphragm Seal
W: Low-Volume Connection for Smart Transmitters
R: Sealing Face - Raised Face
31: Flange Size & Rating - 3" 150#
S: Wetted Parts - 316LSS Diaphragm and Flange
B20: Seal-to-instrument Connection - 20 feet of Armored Capillary, Welded to Seal
AS: Seal system Fill Fluid - Silicone DC200®
R1: Option - Direct Mount to Inline Transmitter',
        '', 'Y', 'N', '1'),
       ('0202179', '000067', '00006000000000',
        'DSXW9FF-WR31S', '1',
        'Flanged Flush Face Seal Body
3" 150# ANSI Raised Face Flange
316L
89mm diaphragm',
        '', 'N', 'Y', '1'),
       ('0202179', '000068', '00006100000000',
        'D316-89', '1',
        'Diaphragm, 89mm x .08mm 316L
per Dwg 10028 Rev B',
        '', 'N', 'Y', '1'),
       ('0202179', '000069', '00006200000000',
        'DXCWW20A', '1',
        'Capillary Assembly Armored Stainless Steel
2 Weld Connections
20 feet',
        '', 'N', 'Y', '1'),
       ('0202179', '000070', '00006300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y', '1'),
       ('0202179', '000071', '00006400000000',
        'DXCTRANS-G1', '1',
        'Single Diaphragm Seal Mount to Smart Transmitter
Gauge Pressure System
Mounted to In-Line/Direct Mount Transmitter',
        '', 'N', 'Y', '1'),
       ('0202179', '000072', '00006500000000',
        'CCALTRAN-GP', '1',
        'Certificate Of Calibration -Smart Transmitter
Gauge Pressure, 5 points',
        '', 'N', 'Y', '1');
DROP TABLE #temptable;

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

First, we need both sample data AND expected results. You've only provided half.

Second, I don't see anything about this that indicates that the groups are dynamic. In order to be dynamic, they would need to be dependent on a variable/parameter.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chuck S Old Hand Points: 302 More actions · Answer 2

Hello Drew,

Here is the fist part of the sample data. When I say dynamic I mean each group can have different items in it. There is no standard on how everything is put together. The same item can have different items underneath it.

Here is the sample data.

Thanks

CREATE TABLE #temptable (SalesOrderNo VARCHAR(7),
                         LineKey VARCHAR(6),
                         LineSeqNo VARCHAR(14),
                         ItemCode VARCHAR(30),
                         ItemType VARCHAR(1),
                         ItemCodeDesc VARCHAR(4096),
                         CommentText VARCHAR(2048),
                         ExplodedKitItem VARCHAR(1),
                         SkipPrintCompLine VARCHAR(1));
INSERT INTO #temptable
VALUES ('0202179', '000020', '00002100000000',
        'PT45P1A2P01-D-T-C3', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -30 inHg to 0
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass
Options: 3pt. Calibration Cert',
        '', 'Y', 'N'),
       ('0202179', '000021', '00002200000000',
        'PT45P1A2P01VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 psi
Label as : PT45P1A2P01-D-T',
        '', 'N', 'Y'),
       ('0202179', '000022', '00002300000000',
        'CCALPG-3', '1',
        'Certificate of Calibration, 3 Points Cert with Sticker
',
        '', 'N', 'Y'),
       ('0202179', '000061', '00003225000000',
        'PMIREPORT-PG', '1',
        'Positive Material Identification Report
Pressure Gauge Socket Material
Per SP# 162',
        '', 'N', 'N'),
       ('0202179', '000032', '00003300000000',
        'SG40ATC20P18', '1',
        '4" Sanitary Pressure Gauge
2" Triclamp Bottom Mount
316 SS Body and Diaphragm
Range: 0 to 100 psi
Dry Case (Field Fillable)
Lens:     Plastic
Seal Fill: Glycerin',
        '', 'Y', 'N'),
       ('0202179', '000033', '00003400000000',
        'PR40S1A4P18VH', '1',
        '4" Stainless Steel Bayonet Case
Stainless Steel Tube and Socket
1/4" NPT Bottom Mount
0-100 psi
Label as : PR40S1A4P18-D-T',
        '', 'N', 'Y'),
       ('0202179', '000034', '00003500000000',
        'DSTC20SS4-WLVR', '1',
        'Sanitary Triclamp Diaphragm Seal
Process: 2"Triclamp
Instrument: 1/4" NPS Female
316 SS Body and Diaphragm
with Welding Lip
(2.0 dia., .02 disp.)
Design required as shown in Dwg 50120 Rev X1
',
        '', 'N', 'Y'),
       ('0202179', '000054', '00003850000000',
        '/G', '4', 'begin group', 'begin group',
        'N', 'N'),
       ('0202179', '000055', '00003975000000',
        'PT45P1A2A04-D-T', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -100 to 0 to 400 kPa
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass',
        '', 'Y', 'N'),
       ('0202179', '000056', '00004037500000',
        'PT45P1A2P04VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 - 60 psi
Label as : PT45P1A2P04-D-T',
        '', 'N', 'Y'),
       ('0202179', '000057', '00004068750000',
        'PXDIALPREPR', '1',
        'DIAL SWAP (W/RECAL) 2.5" TO 4.5" GAUGE
',
        '', 'N', 'Y'),
       ('0202179', '000058', '00004084375000',
        'PXDP-A04-REO-45', '1', 'Dial Print', '',
        'N', 'Y'),
       ('0202179', '000040', '00004100000000',
        'W51521FFS-TTDTD-AS', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon
Seal System Fill Fluid: Silicone DC200® Standard Fluid',
        '', 'Y', 'N'),
       ('0202179', '000041', '00004200000000',
        'W51521FFS-TT-VZ', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon',
        '', 'N', 'Y'),
       ('0202179', '000042', '00004300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y'),
       ('0202179', '000059', '00004400000000',
        '/GE', '4', 'end group', 'end group',
        'N', 'N'),
       ('0202179', '000066', '00005900000000',
        'W9FFWR31S-B20-AS-R1', '1',
        'W9FF: Flanged Flush-Face Diaphragm Seal
W: Low-Volume Connection for Smart Transmitters
R: Sealing Face - Raised Face
31: Flange Size & Rating - 3" 150#
S: Wetted Parts - 316LSS Diaphragm and Flange
B20: Seal-to-instrument Connection - 20 feet of Armored Capillary, Welded to Seal
AS: Seal system Fill Fluid - Silicone DC200®
R1: Option - Direct Mount to Inline Transmitter',
        '', 'Y', 'N'),
       ('0202179', '000067', '00006000000000',
        'DSXW9FF-WR31S', '1',
        'Flanged Flush Face Seal Body
3" 150# ANSI Raised Face Flange
316L
89mm diaphragm',
        '', 'N', 'Y'),
       ('0202179', '000068', '00006100000000',
        'D316-89', '1',
        'Diaphragm, 89mm x .08mm 316L
per Dwg 10028 Rev B',
        '', 'N', 'Y'),
       ('0202179', '000069', '00006200000000',
        'DXCWW20A', '1',
        'Capillary Assembly Armored Stainless Steel
2 Weld Connections
20 feet',
        '', 'N', 'Y'),
       ('0202179', '000070', '00006300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y'),
       ('0202179', '000071', '00006400000000',
        'DXCTRANS-G1', '1',
        'Single Diaphragm Seal Mount to Smart Transmitter
Gauge Pressure System
Mounted to In-Line/Direct Mount Transmitter',
        '', 'N', 'Y'),
       ('0202179', '000072', '00006500000000',
        'CCALTRAN-GP', '1',
        'Certificate Of Calibration -Smart Transmitter
Gauge Pressure, 5 points',
        '', 'N', 'Y');

SELECT * FROM #temptable
DROP TABLE #temptable;

What is should look like.

CREATE TABLE #temptable (SalesOrderNo VARCHAR(7),
                         LineKey VARCHAR(6),
                         LineSeqNo VARCHAR(14),
                         ItemCode VARCHAR(30),
                         ItemType VARCHAR(1),
                         ItemCodeDesc VARCHAR(4096),
                         CommentText VARCHAR(2048),
                         ExplodedKitItem VARCHAR(1),
                         SkipPrintCompLine VARCHAR(1),
                         ReportGroup VARCHAR(1));
INSERT INTO #temptable
VALUES ('0202179', '000020', '00002100000000',
        'PT45P1A2P01-D-T-C3', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -30 inHg to 0
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass
Options: 3pt. Calibration Cert',
        '', 'Y', 'N', '1'),
       ('0202179', '000021', '00002200000000',
        'PT45P1A2P01VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 psi
Label as : PT45P1A2P01-D-T',
        '', 'N', 'Y', '1'),
       ('0202179', '000022', '00002300000000',
        'CCALPG-3', '1',
        'Certificate of Calibration, 3 Points Cert with Sticker
',
        '', 'N', 'Y', '1'),
       ('0202179', '000032', '00003300000000',
        'SG40ATC20P18', '1',
        '4" Sanitary Pressure Gauge
2" Triclamp Bottom Mount
316 SS Body and Diaphragm
Range: 0 to 100 psi
Dry Case (Field Fillable)
Lens:     Plastic
Seal Fill: Glycerin',
        '', 'Y', 'N', '2'),
       ('0202179', '000033', '00003400000000',
        'PR40S1A4P18VH', '1',
        '4" Stainless Steel Bayonet Case
Stainless Steel Tube and Socket
1/4" NPT Bottom Mount
0-100 psi
Label as : PR40S1A4P18-D-T',
        '', 'N', 'Y', '2'),
       ('0202179', '000034', '00003500000000',
        'DSTC20SS4-WLVR', '1',
        'Sanitary Triclamp Diaphragm Seal
Process: 2"Triclamp
Instrument: 1/4" NPS Female
316 SS Body and Diaphragm
with Welding Lip
(2.0 dia., .02 disp.)
Design required as shown in Dwg 50120 Rev X1
',
        '', 'N', 'Y', '2'),
       ('0202179', '000040', '00004100000000',
        'W51521FFS-TTDTD-AS', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon
Seal System Fill Fluid: Silicone DC200® Standard Fluid',
        '', 'Y', 'N', '1'),
       ('0202179', '000041', '00004200000000',
        'W51521FFS-TT-VZ', '1',
        'Threaded Offline Seal, Welded Diaphragm, std. size
Instr. Connection: 1/2" NPT female , mounted via Direct Thread to Instrument
Process Connection: 1" NPT Female
Diaphragm: 304L (wetted)
Lower Housing: 304 SS (wetted)
Upper Housing: 316L SS with 18-8 SS bolts
Gasket: Teflon',
        '', 'N', 'Y', '2'),
       ('0202179', '000042', '00004300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y', '2'),
       ('0202179', '000054', '00003850000000',
        '/G', '4', 'begin group', 'begin group',
        'N', 'N', '1'),
       ('0202179', '000055', '00003975000000',
        'PT45P1A2A04-D-T', '1',
        'Process Gauge, Solid Front Series PT
4.5" Dial, Phenolic Reinforced Thermoplastic Case
316 SS Tube & Socket
1/2" NPT Bottom Connection
Range: -100 to 0 to 400 kPa
Dry Case (Field Fillable)
Lens:     Tempered Safety Glass',
        '', 'Y', 'N', '1'),
       ('0202179', '000056', '00004037500000',
        'PT45P1A2P04VH', '1',
        '4.5" Phenolic Process Gauge
Stainless Steel Tube and Socket
1/2" NPT Bottom Mount
-30 inHg - 0 - 60 psi
Label as : PT45P1A2P04-D-T',
        '', 'N', 'Y', '1'),
       ('0202179', '000057', '00004068750000',
        'PXDIALPREPR', '1',
        'DIAL SWAP (W/RECAL) 2.5" TO 4.5" GAUGE
',
        '', 'N', 'Y', '1'),
       ('0202179', '000058', '00004084375000',
        'PXDP-A04-REO-45', '1', 'Dial Print', '',
        'N', 'Y', '1'),
       ('0202179', '000059', '00004400000000',
        '/GE', '4', 'end group', 'end group',
        'N', 'N', '1'),
       ('0202179', '000061', '00003225000000',
        'PMIREPORT-PG', '1',
        'Positive Material Identification Report
Pressure Gauge Socket Material
Per SP# 162',
        '', 'N', 'N', '2'),
       ('0202179', '000066', '00005900000000',
        'W9FFWR31S-B20-AS-R1', '1',
        'W9FF: Flanged Flush-Face Diaphragm Seal
W: Low-Volume Connection for Smart Transmitters
R: Sealing Face - Raised Face
31: Flange Size & Rating - 3" 150#
S: Wetted Parts - 316LSS Diaphragm and Flange
B20: Seal-to-instrument Connection - 20 feet of Armored Capillary, Welded to Seal
AS: Seal system Fill Fluid - Silicone DC200®
R1: Option - Direct Mount to Inline Transmitter',
        '', 'Y', 'N', '1'),
       ('0202179', '000067', '00006000000000',
        'DSXW9FF-WR31S', '1',
        'Flanged Flush Face Seal Body
3" 150# ANSI Raised Face Flange
316L
89mm diaphragm',
        '', 'N', 'Y', '1'),
       ('0202179', '000068', '00006100000000',
        'D316-89', '1',
        'Diaphragm, 89mm x .08mm 316L
per Dwg 10028 Rev B',
        '', 'N', 'Y', '1'),
       ('0202179', '000069', '00006200000000',
        'DXCWW20A', '1',
        'Capillary Assembly Armored Stainless Steel
2 Weld Connections
20 feet',
        '', 'N', 'Y', '1'),
       ('0202179', '000070', '00006300000000',
        'DSFSS200', '1',
        'Diaphragm Seal Fill
Standard Seal, Silicone DC200',
        '', 'N', 'Y', '1'),
       ('0202179', '000071', '00006400000000',
        'DXCTRANS-G1', '1',
        'Single Diaphragm Seal Mount to Smart Transmitter
Gauge Pressure System
Mounted to In-Line/Direct Mount Transmitter',
        '', 'N', 'Y', '1'),
       ('0202179', '000072', '00006500000000',
        'CCALTRAN-GP', '1',
        'Certificate Of Calibration -Smart Transmitter
Gauge Pressure, 5 points',
        '', 'N', 'Y', '1');

DROP TABLE #temptable;

drew.allen SSC Guru Points: 76944 More actions · Answer 3

This is close. Your expected results don't seem to match your description. Specifically rows 40-42 should be in the same group based on "Group continues as long as SkipPrintCompLine= ‘Y’", but you're showing them as being in two different groups (which throws everything following it off).

; WITH Grps AS ( SELECT *, CASE WHEN MAX(Linekey + group_indicator) OVER(ORDER BY LineKey ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) LIKE '%/G' THEN 0 WHEN SkipPrintCompLine = 'N' THEN 1 ELSE 0 END AS ReportGroup FROM #sampledata OUTER APPLY ( SELECT ItemCode WHERE ItemCode IN ('/G', '/GE') ) Grp(group_indicator) )

SELECT SalesOrderNo, LineKey, LineSeqNo, ItemCode, ItemType, ItemCodeDesc, CommentText, ExplodedKitItem, SkipPrintCompLine, 2 - SUM(ReportGroup) OVER(ORDER BY LineKey ROWS UNBOUNDED PRECEDING) % 2 FROM Grps

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chuck S Old Hand Points: 302 More actions · Answer 4

Hi Drew,

Thanks. This will get me in the ballpark.

Thanks a bunch!!!! 🙂

Chuck S Old Hand Points: 302 More actions · Answer 5

Question. Where did group_indicator come from? Did you add a column?

drew.allen SSC Guru Points: 76944 More actions · Answer 6

It comes from the CROSS APPLY. I did not alter the underlying tables, if that's what you mean.

Make sure that you thoroughly understand this code before deploying it, since you're the one who is going to have to support it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chuck S Old Hand Points: 302 More actions · Answer 7

drew.allen - Thursday, January 18, 2018 3:03 PM
It comes from the CROSS APPLY. I did not alter the underlying tables, if that's what you mean.
Make sure that you thoroughly understand this code before deploying it, since you're the one who is going to have to support it.
Drew

Ok thanks