January 10, 2014 at 2:31 pm
I have the following table with sample data
CREATE TABLE [dbo].[Lots](
[lot_nbr] [int] NOT NULL,
[lot_suffix] [varchar](3) NOT NULL,
[lot_nbrDec] AS (CONVERT([decimal](19,6),(CONVERT([varchar](10),[lot_nbr],(0))+'.')+case len(isnull([lot_suffix],'')) when (0) then '' when (1) then CONVERT([varchar](2),ascii(upper(isnull([lot_suffix],'0'))),(0)) when (2) then CONVERT([varchar](2),ascii(upper(isnull([lot_suffix],'0'))),(0))+CONVERT([varchar](2),ascii(upper(substring([lot_suffix],(2),(1)))),(0)) when (3) then (CONVERT([varchar](2),ascii(upper(isnull([lot_suffix],'0'))),(0))+CONVERT([varchar](2),ascii(upper(substring([lot_suffix],(2),(1)))),(0)))+CONVERT([varchar](2),ascii(upper(substring([lot_suffix],(3),(1)))),(0)) end,(0))) PERSISTED,
[lot_OwnerID] [int] NOT NULL,
CONSTRAINT [PK__Lots_878] PRIMARY KEY CLUSTERED
(
[lot_nbr] ASC,
[lot_suffix] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO
dbo.Lots
( lot_nbr, lot_suffix, lot_OwnerID )
VALUES
(1,'A',55),(2,'A',55),(2,'B',55),(4,'A',22),(5,'A',22),(7,'A',22),(8,'A',22),(9,'C',22),(9,'D',98),(11,'A',98),(11,'B',98),(14,'B',98),(15,'B',22),(16,'A',22),(16,'C',22),(19,'A',22),(20,'A',55),(21,'0',55),(22,'0',55),(25,'0',55),(25,'A',55),(25,'B',78),(25,'D',78)
I would like a set based solution (as there can me thousands of rows) to get the lot ranges (first and last lot_nbr, lot_suffix) for each grouping of each lot_OwnerID as follows
lot_OwnerIDlot_nbrFirstlot_suffixFirstlot_nbrLastlot_suffixLast
224A9C
2215B19A
551A2B
5520A25A
7825B25D
989D14B
I have a feeling that there is a relatively simple solution, but can't put my finger on it.
Suggestions please?
January 10, 2014 at 2:55 pm
What are you grouping by, other than lot_OwnerID?
January 10, 2014 at 2:55 pm
Here's a possible solution, but I don't know if it's the best.
WITH cteLots AS(
SELECT *,
ROW_NUMBER() OVER( ORDER BY lot_nbr) rn,
ROW_NUMBER() OVER( PARTITION BY lot_OwnerID ORDER BY lot_nbr, lot_nbrDec) rn2
FROM dbo.Lots
),
cteFirstLast AS(
SELECT lot_OwnerID,
rn -rn2 grouper,
CASE WHEN rn2 = MIN(rn2) OVER( PARTITION BY rn -rn2) THEN lot_suffix END AS lot_suffixFirst,
CASE WHEN rn2 = MIN(rn2) OVER( PARTITION BY rn -rn2) THEN lot_nbr END AS lot_nbrFirst,
CASE WHEN rn2 = MAX(rn2) OVER( PARTITION BY rn -rn2) THEN lot_suffix END AS lot_suffixLast,
CASE WHEN rn2 = MAX(rn2) OVER( PARTITION BY rn -rn2) THEN lot_nbr END AS lot_nbrLast
FROM cteLots c1
)
SELECT lot_OwnerID,
MAX(lot_nbrFirst) AS lot_nbrFirst,
MAX(lot_suffixFirst) AS lot_suffixFirst,
MAX(lot_nbrLast) AS lot_nbrLast,
MAX(lot_suffixLast) AS lot_suffixLast
FROM cteFirstLast
GROUP BY lot_OwnerID, grouper
ORDER BY lot_OwnerID, lot_nbrFirst
January 10, 2014 at 3:33 pm
sestell1,
Thanks for the quick response.
The only grouping is on lot_OwnerID, but they need to be contiguous groups. So there will be multiple groups for each lot_OwnerID. Or maybe we can say gouping on lot_OwnerID and first/last of each contiguous group?
Luis,
Thanks for the quick response and solution. It works well with the sample data, but on my live data, there are some nulls. I must have missed something when creating the sample, so it will take me some time to dig in and find what I missed.
January 13, 2014 at 7:41 am
Luis,
Thank you. You gave me the jumpstart that I needed. Here us what I ended up with:
SELECT
CLG.lot_OwnerID,
L2.lot_nbr AS lot_nbrFirst,
L2.lot_suffix AS lot_suffixFirst,
L3.lot_nbr AS lot_nbrLast,
L3.lot_suffix AS lot_suffixLast
FROM
(
SELECT
GRP.lot_OwnerID,
MIN(GRP.lot_nbrDec) AS FirstLot, MAX(GRP.lot_nbrDec) AS LastLot
FROM
(
SELECT L.lot_nbr, L.lot_suffix, lot_OwnerID, L.lot_nbrDec ,
ROW_NUMBER() OVER( ORDER BY lot_nbrDec) rn,
ROW_NUMBER() OVER( PARTITION BY lot_OwnerID ORDER BY lot_nbrDec) rn2
FROM dbo.Lots AS L
) AS GRP
GROUP BY
GRP.rn - GRP.rn2, GRP.lot_OwnerID
) AS
CLG INNER JOIN
dbo.Lots AS L2 ON CLG.FirstLot = L2.lot_nbrDec INNER JOIN
dbo.Lots AS L3 ON CLG.LastLot = L3.lot_nbrDec
ORDER BY CLG.lot_OwnerID, L2.lot_nbrDec
January 13, 2014 at 8:57 am
I'm glad I could help even if it wasn't a complete solution for you, but at least you got an idea that worked for you. And it's even better that I'm not just dropping code that the final user won't understand.
I'm sure that your code can be improved for performance, but if it works for you, then it's fine as we don't have the complete picture.
January 13, 2014 at 9:03 am
All true, it just wouldn't be practical to post the whole picture. Performance is good. 2 seconds on nearly 500,000 rows and it typically would not be run on that many rows.
Thanks again
January 15, 2014 at 11:53 am
Sorry, I think I'm still missing something. :unsure:
If they are contiguous groupings, shouldn't the first group in your test case for owner 22 be 4 A 5 A, since there is no lot_nbr 6 for owner 22?
January 15, 2014 at 12:03 pm
As I understood, it's not about consecutive lot_nbr values. It's about the groups for owner id resulting when you order by lot_nbr.
If you run the following query, it should become clear.
SELECT *
FROM dbo.Lots
ORDER BY lot_nbr
January 15, 2014 at 12:08 pm
I'm probably using the word contiguous very loosely. In any case, my modification of Luis' solution does just what I need with good performance.
January 15, 2014 at 12:12 pm
I see what you are doing now.
Thanks both of you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply