Results 1 to 9 of 9
  1. #1
    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.

    thanks in advance,
    rpp

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    Thank your for reply.

    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you should have specified that there might be more than 4 digits in your first post

    thank you very much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are right, i did not not understand that

    sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote 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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    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),
    address nvarchar(100),
    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),
    [address] nvarchar(100),
    zipcode nvarchar(10),
    tech_cost_per_hour money,
    travel_time_cost money,
    region_id int foreign key references region(region_id),
    end_address nvarchar(100),
    end_zipcode nvarchar(10) )
    go

    Even region table is available which contains zipcode latitude and lontitudes to calcuate the distance between customer(job) and Tech.
    if i am diverting the concept by my sequence generation pls trow light and could suggest me a better idea for approach it would be more helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •