creating range between values in query

Question

creating range between values in query

luissantos

SSCertifiable

Points: 7391
More actions
April 26, 2015 at 7:57 am

#300112

Hello comunity
On a new project i need to create possible ranges between a specific interval, let me explain.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group
Group intensity
AA 1120
AB 1400
BA 1800
BB 2240
CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA
2 option : ABBA or ABBB or ABCA
3 option : BABB or BACA or BBCA
or beginning from the end
1.option CABB or CABA...
or beginning from the middle
1.option BBBA or BBAB
and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
Someone could give me an ideia about how can do that.
Many thanks,
Best regards
Luis Santos

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Hi Luis

I think that it would be easier for you to explain what you require if you can provide some create table / insert data scripts that describe your problem and what you expect the results to be.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello J Livingston

i try to create these 2 temporary table, i hope you understand what i pretend.

CREATE TABLE #article(ref VARCHAR(18))

insert INTO #article(ref)

SELECT 'LY E67F'

CREATE TABLE #BrightGroup(ref varchar(18), GROUPCod VARCHAR(2), VALUE NUMERIC (4,0), possibilities VARCHAR(4))

INSERT INTO #BrightGroup(ref,GROUPCod, VALUE, Possibilities)

SELECT 'LY E67F','AA',1120, 'AAAB'

UNION

SELECT 'LY E67F','AA',1400, 'AABA'

UNION

SELECT 'LY E67F','AB',1800, 'ABBA'

UNION

SELECT 'LY E67F','AB',2240, 'ABAA'

UNION

SELECT 'LY E67F','BA', 2800,'BAAB'

UNION

SELECT 'LY E67F','BA', 2800,'BAAA'

SELECT a.ref, GroupCod ,b.possibilities

FROM #article a

INNER join #BrightGroup b ON a.ref = b.ref

Very important, is the field Possibilities on #BrightGroup table could be automatic.

Many thanks

Luis Santos

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Quick suggestion, use CROSS JOIN, here is a quick example

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_GROUP') IS NOT NULL DROP TABLE dbo.TBL_GROUP;

CREATE TABLE dbo.TBL_GROUP

(

[Group] CHAR(2) NOT NULL CONSTRAINT PK_DBO_TBL_GROUP_GROUP PRIMARY KEY CLUSTERED

,intensity INT NOT NULL

);

INSERT INTO dbo.TBL_GROUP([Group],intensity)

VALUES

('AA',1120)

,('AB',1400)

,('BA',1800)

,('BB',2240)

,('CA',2800);

SELECT

G.[Group]

,G.intensity

,G.[Group] + G1.[Group] AS OptionX

FROM dbo.TBL_GROUP G

CROSS JOIN dbo.TBL_GROUP G1

WHERE G.intensity <> G1.intensity

ORDER BY G.[Group] ASC;

Results

Group intensity OptionX

----- ----------- -------

AA 1120 AAAB

AA 1120 AABA

AA 1120 AABB

AA 1120 AACA

AB 1400 ABAA

AB 1400 ABBA

AB 1400 ABBB

AB 1400 ABCA

BA 1800 BAAA

BA 1800 BAAB

BA 1800 BABB

BA 1800 BACA

BB 2240 BBAA

BB 2240 BBAB

BB 2240 BBBA

BB 2240 BBCA

CA 2800 CAAA

CA 2800 CAAB

CA 2800 CABA

CA 2800 CABB

luissantos SSCertifiable Points: 7391 More actions · Answer 4

Hello Eirikur

I was also considering using a "CROSS JOIN" the problem was how to do this.

with your help and your example, I think it will be easier I can get there.

I will go to test it.

Many thanks for your help,

Best regards

Luis Santos

luissantos SSCertifiable Points: 7391 More actions · Answer 5

Hello again Eirikur

Thanks a lot, this is exactly what i need, great help.

Many thanks again.

Best regards,

Luis Santos

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

luissantos (4/26/2015)
Hello again Eirikur
Thanks a lot, this is exactly what i need, great help.
Many thanks again.
Best regards,
Luis Santos

Hi Luis,

thank you for the feedback, happy to be of assistance

😎

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

I realise this has been answered but I did note in your example under options you list what appears to be the result of a triangular join.

I produced exactly those results with :

WITH myCTE AS

(

SELECT ROW_NUMBER() over (partition by NULL order by [group] ) as rowNum,

[group],

intensity

from tbl_group

)

SELECTtl.[group]+tr.[group]

frommyCTE as tl inner join myCTE as tr

on tl.rowNum<tr.rowNum

order by tl.[group]

----------------------------------------------------