Prev: SQL 2005 Certification exam
Next: Which one uses less memory?
From: Plamen Ratchev on 17 Jan 2010 21:49
Here is one solution. It is a method of subtracting two sequences, one without gaps and one with gaps to create grouping
factor:

SELECT custid, itemid,
MIN(serialnumber) AS minserial,
MAX(serialnumber) AS maxserial,
COUNT(serialnumber) AS countserial
FROM (
SELECT A.custid,
A.itemid,
A.serialnumber,
A.serialnumber -
ROW_NUMBER() OVER(PARTITION BY A.custid, A.itemid ORDER BY A.serialnumber) AS grp
FROM #itemsSent AS A
WHERE NOT EXISTS
(SELECT *
FROM #itemsReceived AS B
WHERE A.itemid = B.itemid
AND A.serialnumber = B.serialnumber)) AS T
GROUP BY grp, custid, itemid;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: SQL 2005 Certification exam
Next: Which one uses less memory?