Author 
Topic 

sergeant_time
Yak Posting Veteran
73 Posts 
Posted  05/12/2014 : 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 OffSite OnSite PC OffSite 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 Uverse Cox Cable Mercury Time Warner VA Network


James K
Flowing Fount of Yak Knowledge
3593 Posts 
Posted  05/12/2014 : 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 ('Offsite'),('Onsite PC'),('Offsite Virtual'))Locations(location)
CROSS JOIN
( VALUES ('Unable to Hear caller'),('Caller Unable to Hear CR'),('Garbled Speech'),('Echo') ) Issues(Issue)
... and so on 


TG
Flowing Fount of Yak Knowledge
USA
6062 Posts 
Posted  05/12/2014 : 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 
Edited by  TG on 05/12/2014 14:49:41 


sergeant_time
Yak Posting Veteran
73 Posts 
Posted  05/14/2014 : 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  05/14/2014 : 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
Flowing Fount of Yak Knowledge
USA
6062 Posts 
Posted  05/14/2014 : 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  05/15/2014 : 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 OffSite PC 2 Onsite PC 3 OffSite 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
Aged Yak Warrior
663 Posts 
Posted  05/15/2014 : 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  05/15/2014 : 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
Aged Yak Warrior
663 Posts 
Posted  05/26/2014 : 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. 



Topic 
