SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query for Permutation & Combination
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 05/12/2014 :  14:33:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 05/12/2014 :  14:43:53  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 05/12/2014 :  14:49:03  Show Profile  Reply with Quote
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

Edited by - TG on 05/12/2014 14:49:41
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 05/14/2014 :  09:20:52  Show Profile  Reply with Quote
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

Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 05/14/2014 :  09:21:41  Show Profile  Reply with Quote
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

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 05/14/2014 :  13:36:08  Show Profile  Reply with Quote
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
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 05/15/2014 :  10:01:12  Show Profile  Reply with Quote
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




Go to Top of Page

gbritton
Constraint Violating Yak Guru

420 Posts

Posted - 05/15/2014 :  10:17:42  Show Profile  Reply with Quote
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.
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 05/15/2014 :  10:33:45  Show Profile  Reply with Quote
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.

Go to Top of Page

gbritton
Constraint Violating Yak Guru

420 Posts

Posted - 05/26/2014 :  12:41:00  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000