February 25, 2015 at 7:35 pm
Hi SQL Gurus!
I am having some difficulty getting a query to output an alpha numeric range grouping.
I have this data set:
Despatch_id Sample_ID
MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757
MIR00831 MCR0005758
MIR00831 MCR0005759
MIR00831 MCR0005760
MIR00831 MCR0005761
MIR00831 MCR0005762
MIR00831 MCR0005803
MIR00831 MCR0005804
MIR00831 MCR0005805
MIR00831 MCR0005806
MIR00831 MCR0005808
MIR00831 MCR0005809
MIR00831 MCR0005810
MIR00831 MCR0005811
MIR00831 MCR0005812
MIR00831 MCR0005813
Output:
DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
--------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01551')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01552')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01553')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01554')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01555')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01556')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01557')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01558')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01559')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01560')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01561')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01562')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01563')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01564')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01565')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01566')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01567')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01568')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01569')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01570')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01571')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01572')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01573')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01574')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01575')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01576')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01577')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01578')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01579')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01580')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01581')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01582')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01583')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01584')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01586')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01587')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01588')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01589')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01590')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01591')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01592')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01593')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01594')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01595')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01596')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01597')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01598')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01599')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01600')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01601')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01602')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01603')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01604')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01605')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01606')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01607')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01608')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01609')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01610')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01611')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01612')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01613')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01614')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01615')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01616')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01617')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01618')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01620')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01621')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01622')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01623')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01624')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01625')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01626')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01627')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01628')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01629')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01630')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01631')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01632')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01633')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01634')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01635')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01636')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01637')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01638')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01639')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01640')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01641')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01642')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01643')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01644')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01645')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01646')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01647')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01648')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01649')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01650')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01651')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01652')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01653')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR01654')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02001')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02002')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02003')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02004')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02005')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02006')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02007')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02008')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02009')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02010')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02011')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02012')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02013')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02014')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02015')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02016')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02017')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02018')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02019')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02020')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02021')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02022')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02023')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02024')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02026')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02027')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02028')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02029')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02030')
INSERT INTO SAMPLE_TABLE VALUES('MIR00001', 'MRR02031')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005752')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005753')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005754')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005755')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005756')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005757')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005758')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005759')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005760')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005761')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005762')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005803')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005804')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005805')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005806')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005808')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005809')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005810')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005811')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005812')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005813')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005819')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005820')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005821')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005822')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005824')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005825')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005826')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005830')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005865')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005867')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005868')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005869')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005870')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005871')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005872')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005873')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005876')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005877')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005878')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005879')
INSERT INTO SAMPLE_TABLE VALUES('MIR00831', 'MCR0005880')
February 25, 2015 at 8:28 pm
I think this is just a variant of the "gaps and islands" problem, for which there are many solutions. Binoogle itzik ben-gan gaps and islands to find a number of solutions. Replace itzik with dwain camps to find some great stuff too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2015 at 9:02 pm
ibbo14 (2/25/2015)
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch.
Will the alpha-numeric part ALWAYS be 3 characters starting at the first character?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2015 at 9:07 pm
Hi Jeff,
Thanks for the reply, as far as I can tell from the data it seems to be 3 so far.
Regards,
Ibo
February 25, 2015 at 9:52 pm
Thanks for all the inputs, they pointed me into the right direction:
I've managed to work it out using this CTE Expression:
;WITH cte
AS (
SELECT distinct *
,CAST(SUBSTRING(SAMPLE_ID, 4, LEN(SAMPLE_ID)- 3) AS INT) - ROW_NUMBER() OVER (
ORDER BY SAMPLE_ID
) AS Grp
FROM (SELECT DESPATCH_ID,SAMPLE_ID
FROM [dbo].[SAMPLE_TABLE]
)A
WHERE DESPATCH_ID = 'MIR00831'
)
SELECT distinct DESPATCH_ID
,min(SAMPLE_ID) AS FirstSample
,max(SAMPLE_ID) AS LastSamp
,count(*) AS Quantity
FROM cte
GROUP BY DESPATCH_ID
,Grp
March 1, 2015 at 2:34 am
-- SwePeso
WITH cteData
AS (
SELECTDESPATCH_ID,
SAMPLE_ID,
SUBSTRING(SAMPLE_ID, PATINDEX('%[0-9]%', SAMPLE_ID), LEN(SAMPLE_ID)) - ROW_NUMBER() OVER (PARTITION BY DESPATCH_ID ORDER BY SAMPLE_ID) AS grp
FROMdbo.SAMPLE_TABLE
)
SELECTDESPATCH_ID,
MIN(SAMPLE_ID) AS FromID,
MAX(SAMPLE_ID) AS ToID
FROMcteData
GROUP BYDESPATCH_ID,
grp
ORDER BYDESPATCH_ID,
grp;
N 56°04'39.16"
E 12°55'05.25"
March 1, 2015 at 4:59 pm
Nice one! Thanks for that buddy!
March 2, 2015 at 5:03 pm
TheSQLGuru (2/25/2015)
I think this is just a variant of the "gaps and islands" problem, for which there are many solutions. Binoogle itzik ben-gan gaps and islands to find a number of solutions. Replace itzik with dwain camps to find some great stuff too.
:blush:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply