Grouping records with dynamic groups for report

  • 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;
  • 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

  • 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;
  • 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

  • Hi Drew,

    Thanks. This will get me in the ballpark.

    Thanks a bunch!!!! 🙂

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

  • 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

  • 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

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

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