# Thread: Generate series for combinations

1. Registered User
Join Date
Sep 2009
Posts
12

## Unanswered: Generate series for combinations

declare @a varchar(500)
set @a = '1,2,3,4'

how to generate output for all the combinations of input @a like 1,2,3,4 ; 1,2,4,3 ; 1,3,2,4 ; 1,3,4,2 ; 1,4,2,3 ; 1,4,3,2 ; 2,1,3,4; 2,1,4,3 ; 2,3,1,4...

All the numbers will be in series like for 10 digits.. 1,2,3,4,5,6,7,8,9,10.

i require all the combinations to be generated. the series i found like in the input if last (N) and last-1 (N-1) digits are interchanged, in series 1,2,3,4, second sequence wil be 1,2,4,3 ( interchange last two digits). next 1,3,2,4(N-3 digit is incremented and missing numbers 2,4 are placed).Next sequence wil be 1,3,4,2 ( interchange last two digits).. it could give all the combinations.

Could some one help me in providing solution for this. while i am using sql server 2005.

rpp

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```WITH four AS (
SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 )
SELECT t1.n, t2.n, t3.n, t4.n
FROM four AS t1
, four AS t2
, four AS t3
, four AS t4
WHERE t1.n <> t2.n
AND t1.n <> t3.n
AND t2.n <> t3.n
AND t3.n <> t4.n
AND t2.n <> t4.n
AND t1.n <> t4.n
ORDER
BY t1.n, t2.n, t3.n, t4.n```
results --
Code:
```n    n    n    n
---- ---- ---- ----
1    2    3    4
1    2    4    3
1    3    2    4
1    3    4    2
1    4    2    3
1    4    3    2
2    1    3    4
2    1    4    3
2    3    1    4
2    3    4    1
2    4    1    3
2    4    3    1
3    1    2    4
3    1    4    2
3    2    1    4
3    2    4    1
3    4    1    2
3    4    2    1
4    1    2    3
4    1    3    2
4    2    1    3
4    2    3    1
4    3    1    2
4    3    2    1

24 row(s)```

3. Registered User
Join Date
Sep 2009
Posts
12

Input series for 10 digits.. 1,2,3,4,5,6,7,8,9,10 may be provided or it can be 25.. it is based on input we provide. so all the time i cannot change the query and the where condition. so can i expect stored procedure which will return all the combinations for any input provided.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
perhaps you should have specified that there might be more than 4 digits in your first post

thank you very much

5. Registered User
Join Date
Sep 2009
Posts
12
I am sorry, my intension for the line "All the numbers will be in series like for 10 digits.. 1,2,3,4,5,6,7,8,9,10." is about the input.

May be i dint check if it sounds same.

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
you are right, i did not not understand that

sorry

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
First you will need to parse your string into individual values. This function will work:
Code:
```create function ParseString(@String varchar(500), @Delimiter char(1))
returns table
as

--function ParseString
--blindman, 5/20/2008
--Parses a string based upon a given single-character delimiter,
--without using loops or a tally table.

--test parameters
--declare	@String varchar(500)
--declare	@Delimiter char(1)
--set		@String = 'rnbqkbnr/pp1ppppp/8/2p5/4P3/5N2/PPPP1PPP/RNBQKB1R'
--set		@Delimiter = '/'
--set		@String = 'Abracadabra!, Shazam!, Presto!'
--set		@Delimiter = ','
--;

return
(
with Results as
(select	1 as Ordinal,
ltrim(left(@String, charindex(@Delimiter, @String + @Delimiter)-1)) as StringValue,
convert(varchar(500), right(@String + @Delimiter, len(@String) - charindex(@Delimiter, @String+@Delimiter) + 1)) as Remaining
UNION ALL
select	Ordinal+1,
ltrim(left(Remaining, charindex(@Delimiter, Remaining)-1)),
right(Remaining, len(Remaining) - charindex(@Delimiter, Remaining))
from	Results
where	len(Remaining) > 0)
select	Ordinal,
StringValue
from	Results
)```
Then just join the results to itself (a cross join) to get all the combinations.

8. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by r937
perhaps you should have specified that there might be more than 4 digits in your first post

thank you very much

you NEED me on that wall

9. Registered User
Join Date
Sep 2009
Posts
12
Dont mind i am coming back to the starting point.
My actual requirement: When a customer call to a call center and say he has a problem in assembling the computer and so required a Technician to fix the problem.
The call center guy fix a job taking details of customer like Customer name, address, Zip code, Region and If he require a Skill Tech, Certified Tech, any particular date and time to repair

So need to check who all the available technicians who are exactly suit to customer requirement..

so the call center guy will get a list of technicians who are available close in distance to the customer location
and the tech timings has to be available to fix that problem means he should be free at that customer chosen time or has schedule to any another customer. If so another tech has to be schedule for this job.

And so the Tech will be scheduled and appointment will be fixed.

Let us Suppose another job has come
which is more near to that customer, then we need to reschedule the tech to this job if all the requirement is ok, and more near to this new job
with same skills, certificate requirement of tech has to be allocated to the previous job.. which means we are rescheduling jobs when and every new job will arrive..

so all time all the techs has to be checked if they need to reschedule.

Bottom line of my Problem:
Compare all possible sequences to which we can assign job to Technicians available.

For finding the combinations i tried the series and trying to evaluate the possible sequences.
-- table structure
create table customer (
customer_id bigint identity(1,1) primary key,
customer_name varchar(50),
zipcode nvarchar(10),
region_id int foreign key references region(region_id))
go

create table job( job_id bigint identity(1,1) primary key,
customer_id bigint foreign key references customer(customer_id),
priority tinyint,
job_code nvarchar(20) null,
job_name nvarchar(100),
job_date datetime default(getdate()),
duration int,
job_cost money,
certification_required bit,
skill_required bit,
)
go
create table technician(tech_id int identity(1,1) primary key,
tech_name nvarchar(50),
zipcode nvarchar(10),
tech_cost_per_hour money,
travel_time_cost money,
region_id int foreign key references region(region_id),