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 Incident7AM8AM9AM10AM11AM12PM1PM2PM3PM4PM5PM6PM7PMLocationOff-SiteOnSite PCOff-Site VirtualIssueStaticCR CR Unable to Hear callerCaller Unable to Hear CRGarbled SpeechEchoCall dispositionAble to Handle CallCaller Initiated DisconnectTransfered to 5508Transfered to CCVProviderAT&T U-verseCox CableMercuryTime WarnerVA 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*5you get: 3900 rowsSoSELECT *FROM [Time of incident] as tcross join [location] as lcross join [IssueStatic] icross join [Call disposition] ccross join [provider] pAnd the mathematical probability of any given combination would be 1:3900now if you can give weight factors to any of the values then you could come up with different chances.EDIT:Be One with the OptimizerTG |
|
|
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*5you get: 3900 rowsSoSELECT *FROM [Time of incident] as tcross join [location] as lcross join [IssueStatic] icross join [Call disposition] ccross join [provider] pAnd the mathematical probability of any given combination would be 1:3900now if you can give weight factors to any of the values then you could come up with different chances.EDIT:Be One with the OptimizerTG
|
|
|
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*5you get: 3900 rowsSoSELECT *FROM [Time of incident] as tcross join [location] as lcross join [IssueStatic] icross join [Call disposition] ccross join [provider] pAnd the mathematical probability of any given combination would be 1:3900now if you can give weight factors to any of the values then you could come up with different chances.EDIT:Be One with the OptimizerTG
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-14 : 13:36:08
|
quote: Originally posted by sergeant_timeCan 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. DDLCREATE TABLE statements that we can execute to get the same table structure as you have2. DMLInsert statements to populate the table(s) from step 1 with sample data.3. Expected resultsBased 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 OptimizerTG |
|
|
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. ExampleWhat 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 DescriptionTime 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 CCVInternet Provider 0 1 2 3 4Next 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 Disposition0 1 0 10 1 0 12 1 0 11 1 0 10 1 0 10 1 0 10 1 0 21 1 0 21 1 0 21 3 1 21 3 1 21 3 1 32 1 1 31 1 1 31 3 1 32 2 1 30 2 1 20 2 3 20 2 1 20 2 4 20 2 4 12 2 4 11 2 4 12 1 4 12 3 3 12 3 3 12 3 3 21 3 3 12 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. |
|
|
|