SET based operation
-
Hi !
I have data like this:
CREATE TABLE TEMP_CASE_DTL (CASE_NBR VARCHAR(10), SKU_ID VARCHAR(10));
INSERT INTO TEMP_CASE_DTL VALUES ('C1', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C1', 'S2');
INSERT INTO TEMP_CASE_DTL VALUES ('C2', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C2', 'S2');
INSERT INTO TEMP_CASE_DTL VALUES ('C3', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C3', 'S4');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S3');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S4');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S5');
INSERT INTO TEMP_CASE_DTL VALUES ('C5', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C5', 'S4');
And the desired output is:
SKU CASE
------ -----
S1
S2
C1
C2
-----
S1
S4
C3
C5
-----
S3
S4
S5
C4
-----
So, essentially, the SKU's and cases need to be grouped together and the counts need to match exactly i.e. cases with the same SKUs need to be grouped together and the cases in that group should have the exact same SKUs...if not, then they will be present separately like in C4 in the above example.
I can do this via a looping logic but is there a SET based approach to this ?
Thanks.
-
I have some code that produces the output you want. It turned out to be more complicated than I thought it would be at first. My code requires that the max number of sku-values in each group is 3 - I hope that's OK. I leave it with you to replace my table variables with properly indexed temp tables and test whether this performes better than your original solution
CREATE TABLE TEMP_CASE_DTL (CASE_NBR VARCHAR(10), SKU_ID VARCHAR(10));
INSERT INTO TEMP_CASE_DTL VALUES ('C1', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C1', 'S2');
INSERT INTO TEMP_CASE_DTL VALUES ('C2', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C2', 'S2');
INSERT INTO TEMP_CASE_DTL VALUES ('C3', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C3', 'S4');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S3');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S4');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S5');
INSERT INTO TEMP_CASE_DTL VALUES ('C5', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C5', 'S4');
declare @tbl table(id int identity(1, 1), seqno int, CASE_NBR VARCHAR(10), SKU_ID VARCHAR(10))
insert @tbl (seqno, CASE_NBR, SKU_ID) select 0, CASE_NBR, SKU_ID from TEMP_CASE_DTL
order by CASE_NBR, SKU_ID
update t set t.seqno = t.id - dt.id + 1
from @tbl t
inner join
(
select CASE_NBR, min(id) as id from @tbl group by CASE_NBR
)
dt
on t.CASE_NBR = dt.CASE_NBR
declare @tbl2 table(CASE_NBR1 varchar(10), CASE_NBR2 varchar(10), SKU_ID VARCHAR(10))
insert @tbl2
select dt1.CASE_NBR, dt2.CASE_NBR, ''
from
(select distinct CASE_NBR from TEMP_CASE_DTL)
dt1
inner join
(select distinct CASE_NBR from TEMP_CASE_DTL)
dt2
on dt1.CASE_NBR < dt2.CASE_NBR
left join @tbl ta1
on
ta1.CASE_NBR = dt1.CASE_NBR and ta1.seqno = 1
left join @tbl ta2
on
ta2.CASE_NBR = dt1.CASE_NBR and ta2.seqno = 2
left join @tbl ta3
on
ta3.CASE_NBR = dt1.CASE_NBR and ta3.seqno = 3
left join @tbl tb1
on
tb1.CASE_NBR = dt2.CASE_NBR and tb1.seqno = 1
left join @tbl tb2
on
tb2.CASE_NBR = dt2.CASE_NBR and tb2.seqno = 2
left join @tbl tb3
on
tb3.CASE_NBR = dt2.CASE_NBR and tb3.seqno = 3
where
isnull(ta1.SKU_ID, 'Dummy') = isnull(tb1.SKU_ID, 'Dummy')
and isnull(ta2.SKU_ID, 'Dummy') = isnull(tb2.SKU_ID, 'Dummy')
and isnull(ta3.SKU_ID, 'Dummy') = isnull(tb3.SKU_ID, 'Dummy')
delete @tbl2 where CASE_NBR1 in (select CASE_NBR2 from @tbl2)
insert @tbl2 select distinct CASE_NBR, CASE_NBR, '' from TEMP_CASE_DTL
where CASE_NBR not in (select CASE_NBR2 from @tbl2)
insert @tbl2
select distinct c.CASE_NBR, '', c.SKU_ID
from @tbl2 t inner join TEMP_CASE_DTL c
on t.CASE_NBR1 = c.CASE_NBR
select SKU_ID, CASE_NBR2 from @tbl2 order by CASE_NBR1, CASE_NBR2
drop table TEMP_CASE_DTL
go
-
Thanks Jesper...but the problem is that the max number of SKUs is not fixed...it can be as big as 200.
-
OK - how about this one then? It uses a function that creates a comma separated list of SKU_IDs for a given CASE_NBR. To work correctly, ',' should not be part of any sku_id. You may of course use ';' or even '#*#' instead of ',' if this is a problem.
CREATE TABLE TEMP_CASE_DTL (CASE_NBR VARCHAR(10), SKU_ID VARCHAR(10));
INSERT INTO TEMP_CASE_DTL VALUES ('C1', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C1', 'S2');
INSERT INTO TEMP_CASE_DTL VALUES ('C2', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C2', 'S2');
INSERT INTO TEMP_CASE_DTL VALUES ('C3', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C3', 'S4');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S3');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S4');
INSERT INTO TEMP_CASE_DTL VALUES ('C4', 'S5');
INSERT INTO TEMP_CASE_DTL VALUES ('C5', 'S1');
INSERT INTO TEMP_CASE_DTL VALUES ('C5', 'S4');
go
create function sku_string(@CASE_NBR varchar(10))
returns varchar(8000)
as
begin
declare @STR varchar(8000)
select @STR = case when @STR is null then '' else @STR + ',' end + SKU_ID
from TEMP_CASE_DTL where CASE_NBR = @CASE_NBR
return @STR
end
go
declare @sku_strings table(CASE_NBR varchar(10), SKU_STRING varchar(8000))
insert into @sku_strings
select CASE_NBR, dbo.sku_string(CASE_NBR) from TEMP_CASE_DTL group by CASE_NBR
select dt.mincase, '' as SKU_ID, s.CASE_NBR
from
@sku_strings s inner join
(
select SKU_STRING, min(CASE_NBR) as mincase from @sku_strings group by SKU_STRING
)
dt
on
dt.SKU_STRING = s.SKU_STRING
union all
select dt.mincase, t.SKU_ID, ''
from
TEMP_CASE_DTL t inner join
(
select SKU_STRING, min(CASE_NBR) as mincase from @sku_strings group by SKU_STRING
)
dt
on
dt.mincase = t.CASE_NBR
order by dt.mincase, s.CASE_NBR, SKU_ID
drop function sku_string
go
drop table TEMP_CASE_DTL
go
-
Works great. I added an order by on SKU_ID in the function in order to make sure that the SKU_STRING gets generated the same way for the cases.
Thanks Jesper
-
Good to hear - and you are absolutely right, I forgot "order by SKU_ID" in the function...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply