Query for Permutation & Combination

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query for Permutation & Combination

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2014-05-12 : 14:33:14

I have one table and I am trying to accomplish two tasks, generate all possible combinations and the probability of these combinations occuring. My sample data is below:

Time of Incident
7AM
8AM
9AM
10AM
11AM
12PM
1PM
2PM
3PM
4PM
5PM
6PM
7PM

Location
Off-Site
OnSite PC
Off-Site Virtual

IssueStatic
CR
CR Unable to Hear caller
Caller Unable to Hear CR
Garbled Speech
Echo

Call disposition
Able to Handle Call
Caller Initiated Disconnect
Transfered to 5508
Transfered to CCV

Provider
AT&T U-verse
Cox Cable
Mercury
Time Warner
VA Network

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-12 : 14:43:53

Do a cross join of all the fields. If it is equal probability, the probability of any one combination would be 1/Number of Rows.

SELECT * FROM 
( VALUES ('Off-site'),('Onsite PC'),('Off-site Virtual'))Locations(location)
CROSS JOIN 
( VALUES ('Unable to Hear caller'),('Caller Unable to Hear CR'),('Garbled Speech'),('Echo') ) Issues(Issue)
... and so on

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-12 : 14:49:03

you say you have "one table" but your sample data looks to be five tables - so that is confusing.
To generate all possible combinations you just need to CROSS JOIN all the tables. So if you multiple all the row counts in your five tables:
13*3*5*4*5
you get: 3900 rows

So
SELECT *
FROM [Time of incident] as t
cross join [location] as l
cross join [IssueStatic] i
cross join [Call disposition] c
cross join [provider] p

And the mathematical probability of any given combination would be 1:3900

now if you can give weight factors to any of the values then you could come up with different chances.

EDIT:

Be One with the Optimizer
TG

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2014-05-14 : 09:20:52

Can you explain further please?

quote:
Originally posted by TG

you say you have "one table" but your sample data looks to be five tables - so that is confusing.
To generate all possible combinations you just need to CROSS JOIN all the tables. So if you multiple all the row counts in your five tables:
13*3*5*4*5
you get: 3900 rows

So
SELECT *
FROM [Time of incident] as t
cross join [location] as l
cross join [IssueStatic] i
cross join [Call disposition] c
cross join [provider] p

And the mathematical probability of any given combination would be 1:3900

now if you can give weight factors to any of the values then you could come up with different chances.

EDIT:

Be One with the Optimizer
TG

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2014-05-14 : 09:21:41

I have one table and these are my columns.

quote:
Originally posted by TG

you say you have "one table" but your sample data looks to be five tables - so that is confusing.
To generate all possible combinations you just need to CROSS JOIN all the tables. So if you multiple all the row counts in your five tables:
13*3*5*4*5
you get: 3900 rows

So
SELECT *
FROM [Time of incident] as t
cross join [location] as l
cross join [IssueStatic] i
cross join [Call disposition] c
cross join [provider] p

And the mathematical probability of any given combination would be 1:3900

now if you can give weight factors to any of the values then you could come up with different chances.

EDIT:

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-14 : 13:36:08

quote:
Originally posted by sergeant_time
Can you explain further please?

I have one table and these are my columns.

I think you're the one that needs to explain things further

The best way to explain your problem is to provide the following:

1. DDL
CREATE TABLE statements that we can execute to get the same table structure as you have

2. DML
Insert statements to populate the table(s) from step 1 with sample data.

3. Expected results
Based on the sample data that you insert in step 2 post the results you want to see.

keep the formatting if your code by enclosing it in code tags:
[code]<your DDL/DML>[/code]

Be One with the Optimizer
TG

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2014-05-15 : 10:01:12

I hope that this may help.

My question has two parts. I attached an excel file as an example.

1. How can I determine or calculate the number of possible combinations?
a) I would like to generate a list of the number possible combinations based on my Code Key.
2. How can calculate the probability of these events occurring using my combination list.
I have an example of the outcome I would like to achieve. I want order by data by Top 10.

Example

What is the probability of finding an sound issue between 7am -10pm that is Onsite and caller Unable to Hear Cr and the call was transferred.

7%
I would like to generate a list of possible combinations based onthe "Code" in my Key Code below:

Key code Description
Time of Incident
0 7am
1 8am
2 9am
3 10am
4 11am
5 1pm
6 2pm
7 3pm
8 4pm
9 5pm
10 6pm
11 7pm

Current Location
1 Off-Site PC
2 Onsite PC
3 Off-Site Virtual

Worst Problem
0 Static
1 CR Unable to Hear Caller
2 Caller Unable to Hear CR
3 Garbled Speech
4 Echo

Call Disposition
1 Transfer FP 5508
2 Able to Handle Call
3 Caller Disconnected
4 Transfered to CCV

Internet Provider 0
1
2
3
4

Next I would like to find the probability of these events occuring.

The data that I will be quering in below:

Time of Incident Current Location Worst Problem Call Disposition
0 1 0 1
0 1 0 1
2 1 0 1
1 1 0 1
0 1 0 1
0 1 0 1
0 1 0 2
1 1 0 2
1 1 0 2
1 3 1 2
1 3 1 2
1 3 1 3
2 1 1 3
1 1 1 3
1 3 1 3
2 2 1 3
0 2 1 2
0 2 3 2
0 2 1 2
0 2 4 2
0 2 4 1
2 2 4 1
1 2 4 1
2 1 4 1
2 3 3 1
2 3 3 1
2 3 3 2
1 3 3 1
2 3 1 1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 10:17:42

Still need to see your DDL, DML and expected results from your sample data. If you don't know the expected results, you can't know if the query is correct or not.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2014-05-15 : 10:33:45

I can compare the results to what I have in excel. I am able to do this in excel, but rather have it done in ssrs.

quote:
Originally posted by gbritton

Still need to see your DDL, DML and expected results from your sample data. If you don't know the expected results, you can't know if the query is correct or not.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-26 : 12:41:00

I'm sure you can compare the results. However, you've asked for help here. To do that we need a clear, executable setup (that's why we want to see the DDL (Create Table) and DML (Insert Into)). From what you've sent so far, we'd have to do it by hand.

Finally, we need to see the results you are expecting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources