Help with a numbering thing... kinda

• robert.wiglesworth

  SSCommitted

  Points: 1790

  More actions

  February 7, 2017 at 8:12 am

  #340297

  The table I am working with has something like this:

  OrderNumber rtype   jobnumber
  8316                J         1
  8316                 L        2
  8316                 K        3
  8316                 K        4
  8316                 K        5
  8316                 K        6
  8316                 K        7
  8316                 K        8
  8316                 K        9
  8316                 K      10
  8316                 K       11
  8316                 L        12
  8316                 K        13
  8316                 K        14

  I want to show record type K as a sub-part of the L order above it. So in this case, job numbers 3-11 are sub-parts of job number 2. Job numbers 13-14 are sub-parts of job 12. So I when I select from this table, I want it to display like this:
  OrderNumber rtype   jobnumber
  8316                J         1
  8316                 L        2
  8316                 K        2.3
  8316                 K        2.4
  8316                 K        2.5
  8316                 K        2.6
  8316                 K        2.7
  8316                 K        2.8
  8316                 K        2.9
  8316                 K      2.10
  8316                 K       2.11
  8316                 L        12
  8316                 K        12.13
  8316                 K        12.14

  Record type J are stand alone and should just be selected as is. Can someone help me with how to do this select statement?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 7, 2017 at 8:24 am

  #1927101

  my first guess, but i converted the copy/paste to consumable data:

  ;WITH MySampleData([OrderNumber],[rtype],[jobnumber])
AS
(
SELECT '8316','J','1' UNION ALL
SELECT '8316','L','2' UNION ALL
SELECT '8316','K','3' UNION ALL
SELECT '8316','K','4' UNION ALL
SELECT '8316','K','5' UNION ALL
SELECT '8316','K','6' UNION ALL
SELECT '8316','K','7' UNION ALL
SELECT '8316','K','8' UNION ALL
SELECT '8316','K','9' UNION ALL
SELECT '8316','K','10' UNION ALL
SELECT '8316','K','11' UNION ALL
SELECT '8316','L','12' UNION ALL
SELECT '8316','K','13' UNION ALL
SELECT '8316','K','14'
)
SELECT T1 .JobNumber + '.' + T2.JobNumber As JobSubJob,
*
FROM MySampleData T1
LEFT JOIN MySampleData T2
ON T1.OrderNumber = T2.OrderNumber AND T1.rtype ='L' AND T2.rtype = 'K'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• drew.allen

  SSC Guru

  Points: 76997

  More actions

  February 7, 2017 at 9:09 am

  #1927112

  Here is my solution using a modified version of Lowell's sample data.  (I treated numbers as numbers rather than strings.)

  
/*  SET UP THE SAMPLE DATA  */;
;WITH MySampleData([OrderNumber],[rtype],[jobnumber])
AS
(
SELECT 8316,'J',1 UNION ALL
SELECT 8316,'L',2 UNION ALL
SELECT 8316,'K',3 UNION ALL
SELECT 8316,'K',4 UNION ALL
SELECT 8316,'K',5 UNION ALL
SELECT 8316,'K',6 UNION ALL
SELECT 8316,'K',7 UNION ALL
SELECT 8316,'K',8 UNION ALL
SELECT 8316,'K',9 UNION ALL
SELECT 8316,'K',10 UNION ALL
SELECT 8316,'K',11 UNION ALL
SELECT 8316,'L',12 UNION ALL
SELECT 8316,'K',13 UNION ALL
SELECT 8316,'K',14
)
/*  The actual query  */
SELECT *,
    CASE WHEN rtype = 'K' THEN CAST(MAX(CASE WHEN rtype = 'L' THEN jobnumber END) OVER(PARTITION BY OrderNumber ORDER BY jobnumber ROWS UNBOUNDED PRECEDING) AS VARCHAR(8)) + '.'
        ELSE ''
    END + CAST(jobnumber AS VARCHAR(8))
FROM MySampleData
ORDER BY jobnumber

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• robert.wiglesworth

  SSCommitted

  Points: 1790

  More actions

  February 7, 2017 at 9:28 am

  #1927121

  Thanks, I think I got it now.