CODEWITHSUNDEEP
sqlsql-server-2008-r2
Srikanth
Srikanth

Reputation: 147

Sql query for start and end of a column

I am trying to write a query to get start and end serials for a particular sale

Serial    Sale_id
0001      1
0002      1
0003      1
0004      2
0005      2
0006      1
0007      1
0008      1

I am looking for a result like

Quantity     Start    End
3            0001     0003
3            0006     0008

can some one please help me get this one right

Upvotes: 2

Views: 172

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You can do this by counting the number of non-similar sales ids before each one. This is a constant for sequences where the sales ids are the same. Then use this for grouping:

select count(*), min(serial_no), max(serial_no)
from (select t.*,
             (select count(*)
              from taple  t2
              where t2.serial_no <= t.serial_no and
                    t2.sale_id <> t.sale_id
             ) as grp
      from taple  t
     ) t
group by grp;

If you only care about sale_id = 1 then put a where clause in the middle subquery query.

Here is a SQL Fiddle.

Upvotes: 1

jpw
jpw

Reputation: 44891

This query (adapted from the book SQL MVP Deep Dives) should give you what you want:

SELECT 
  Sale_id, --optional, include is you want the Sale_id

  -- if Serial is a (VAR)CHAR type use this field
  Quantity = MAX(CAST(Serial AS INT)) - MIN(CAST(Serial AS INT)) + 1, 

  -- if Serial is INT already use this field instead
  --Quantity = MAX(CAST(Serial AS INT)) - MIN(CAST(Serial AS INT)) + 1, 

  [Start] = MIN(Serial), 
  [End] = MAX(Serial)

FROM (
  SELECT 
    Sale_id, 
    Serial,
    RowNumber = 
      Serial - ROW_NUMBER() OVER (PARTITION BY Sale_id ORDER BY Serial)
  FROM YourTable
  ) a
--WHERE Sale_id = 1 -- optional limiting WHERE clause
--WHERE Sale_id IN (1,2) -- optional limiting WHERE clause
GROUP BY Sale_id, RowNumber
ORDER BY Start;

I made the assumption that Serial is stored as a character type and included a cast to INT. If it is an numeric type already, change to the line without the cast for the Quantity.

I choose to include the Sale_id column in the output, if you don't want it, just comment it out. If you want to limit the results to one or more particular Sale_id include one of the the WHERE clauses.

Sample SQL Fiddle

Sample output:

Sale_id     Quantity    Start      End
----------- ----------- ---------- ----------
1           3           0001       0003
2           2           0004       0005
1           3           0006       0008

(3 row(s) affected)

Upvotes: 2

Related Questions