Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: Create range from list of numbers

    Hello All,
    I have a database that contains a list of processed serial numbers. All of the serial numbers are sequential, but they are not processed an sequential order. As they are processed they are written to a MS SQL2005 DB table.

    When I generate a "processed" report currently it displays all Serial numbers individually (this amounts to many pages on the report.)

    I would like to create a entry for each number range on the report. I'm currently using MS Access for the report. I can't think of a creative way to do this using SQL or VBscript.

    Current report example:

    SN
    H01030
    H01031
    H01032
    H01033
    H01038
    H01039
    H01040
    H01048

    What I'd like to accomplish example:
    SN
    H01030 - H01033
    H01038 - H01040
    H01048

    Thank you all for any help,
    -Ken

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what language are you using to create your report? asp? asp net? php?

    that would be where you can collapse the ranges while looping over the sorted query results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    6
    I'm currently using MS Access for the reporting. It began as a fairly simple report and this small change is looking to make it complicated.
    My co-worker codes in ASP .Net but when I asked her about this she was not sure of a good direction to take.
    I'm guessing that Access is probably not the answer, but if you have any ideas for ASP .NET (or Access) I'm open to anything.

    Thanks again,
    -Ken

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that this isn't even VAGUELY standard ANSI SQL, so I'm moving it to the Microsoft SQL forum.

    Code:
    CREATE TABLE #foo (
       bar		NVARCHAR(9)
       )
    
    INSERT INTO #foo (bar)
       SELECT 'H01030' UNION ALL
       SELECT 'H01031' UNION ALL
       SELECT 'H01032' UNION ALL
       SELECT 'H01033' UNION ALL
       SELECT 'H01038' UNION ALL
       SELECT 'H01039' UNION ALL
       SELECT 'H01040' UNION ALL
       SELECT 'H01048'
    
    SELECT b.bar, MIN(e.bar)
       FROM #foo AS b
       JOIN #foo AS e
          ON (b.bar <= e.bar
          AND NOT EXISTS (SELECT *
             FROM #foo AS z1
             WHERE  z1.bar = Left(e.bar, 1) + Right(100001 
    +           SubString(e.bar, 2, 9), 5)))
       WHERE NOT EXISTS (SELECT *
          FROM #foo AS z2
          WHERE z2.bar = LEFT(b.bar, 1) + RIGHT(99999 
    +        SUBSTRING(b.bar, 2, 9), 5))
       GROUP BY b.bar
    
    
    DROP TABLE #foo
    -PatP
    Last edited by Pat Phelan; 12-20-10 at 17:21.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2010
    Posts
    6
    PatP,
    Thanks for the reply (and the thread relocate).
    I just ran your code and this is exactly what I need! Thank you for the help.

    I'll try to run this against my 'Packout' table and 'SerialNumber' column.
    Code:
    SELECT b.SerialNumber, MIN(e.SerialNumber)
       FROM Packout AS b
       JOIN Packout AS e
          ON (b.SerialNumber <= e.SerialNumber
          AND NOT EXISTS (SELECT *
             FROM Packout AS z1
             WHERE  z1.SerialNumber = Left(e.SerialNumber, 1) + Right(100001 
    +           SubString(e.SerialNumber, 2, 9), 5)))
       WHERE NOT EXISTS (SELECT *
          FROM Packout AS z2
          WHERE z2.SerialNumber = LEFT(b.SerialNumber, 1) + RIGHT(99999 
    +        SUBSTRING(b.SerialNumber, 2, 9), 5))
       GROUP BY b.SerialNumber
    I'm going to keep at this in the mean time, I just wanted to post this to make sure I'm heading in the right direction (I'm pretty green with T-SQL... but working hard to understand it )
    Thanks again,
    -Ken
    Last edited by kenmcintosh; 12-20-10 at 18:41.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    I have an idea for a cool trick, let me think about this...

  7. #7
    Join Date
    Jun 2005
    Posts
    319
    unfortunately I ran out of time, need to take off, but my thinking was look at the gaps and come up with the ranges that the gaps identify - pretty easy to do with a cursor to finish it off, but I bet some smart guy here can solve this riddle w/ a neat CTE.

    -- change the permanent tables to temp tables if you prefer..
    Code:
    create table Foo
    (
    sn	int not null primary key
    )
    
    create table notInFoo
    (
    sn	int not null primary key
    )
    
    create table possibleFoo
    (
    sn	int not null primary key
    )
    
    create table reportFoo
    (
      reportFooId	int not null identity(1,1) primary key
    , snLow		int not null
    , snHigh		int not null
    )
    
    INSERT INTO Foo (sn)
    SELECT CAST(RIGHT(bar, 5) as int)
    from
    (
       SELECT 'H01030' AS bar UNION ALL
       SELECT 'H01031' UNION ALL
       SELECT 'H01032' UNION ALL
       SELECT 'H01033' UNION ALL
       SELECT 'H01038' UNION ALL
       SELECT 'H01039' UNION ALL
       SELECT 'H01040' UNION ALL
       SELECT 'H01048'
    ) f
    
    DECLARE @minSn int
    DECLARE @maxSn int
    
    SELECT 
    	  @minSn = min(sn)
    	, @maxSn = max(sn)
    FROM
    	Foo
    
    ; WITH CTE_Foo (bar)
      AS (SELECT @minSn
    	UNION ALL 
    	SELECT bar + 1 FROM CTE_Foo
    	WHERE bar < @maxSn
    	)
    INSERT possibleFoo(sn)
    SELECT
    	F.bar
    FROM
    	CTE_Foo F	
    
    INSERT notInFoo(sn)
    SELECT
    	P.sn
    FROM
    	possibleFoo P
    LEFT OUTER JOIN Foo F
    	ON P.sn = F.sn 
    WHERE
    	F.sn IS NULL

  8. #8
    Join Date
    Dec 2010
    Posts
    6
    Here's what I have now (Thanks PatP)
    *See Attached*
    Is it possible to give the second column a name? and to not have it populate if the Serial Numbers are the same?

    Thanks,
    -Ken
    Attached Thumbnails Attached Thumbnails 12-20-2010 4-45-36 PM.jpg  

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, it is possible to name the column and it is kind of possible to suppress the second value using SQL. Both of these operations are better handled using MS-Access though.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you get any results from:
    Code:
    SELECT SerialNumber
       FROM Packout
       WHERE SerialNumber NOT LIKE '_[0-9][0-9][0-9][0-9][0-9]'
    This shouldn't return any rows. My first guess is that you've got trailing spaces, but that's only a guess.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Dec 2010
    Posts
    6
    I'm not getting any results from that query.

    I am unable to get the working query (PatP's query above) to run directly from Access using it's SQL query editor.

    The problem I am running into now is I want to be able query by date, the packout table has a date column.
    I currently use the variable criteria in Access to limit the query to a date with a operator prompt.
    In the code below I am trying to include the date column. My hope is to get each range to be separated out by the day they were written to the DB.
    I am not getting accurate results form the following query. *See attached picture*
    Code:
    SELECT b.SerialNumber, MIN(e.SerialNumber) as EndRange, LEFT(CONVERT(VARCHAR, b.date, 120), 10) as ShipDate
       FROM Packout AS b
       JOIN Packout AS e
          ON (b.date = e.date
          and b.SerialNumber <= e.SerialNumber
          AND NOT EXISTS (SELECT *
             FROM Packout AS z1
             WHERE  z1.SerialNumber = Left(e.SerialNumber, 1) + Right(100001 
    +           SubString(e.SerialNumber, 2, 9), 5)))
       WHERE NOT EXISTS (SELECT *
          FROM Packout AS z2
          WHERE z2.SerialNumber = LEFT(b.SerialNumber, 1) + RIGHT(99999 
    +        SUBSTRING(b.SerialNumber, 2, 9), 5))
       GROUP BY b.SerialNumber, b.dater
    Thanks,
    -Ken
    Attached Thumbnails Attached Thumbnails 12-21-2010 1-42-08 PM.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •