Complicated SQL request

[straight_jacket]

Could use some help.
I have a database with several tables. I need to query the db and get a number, the problem is there are a couple calculations to get the final number.

Here is a list of the table and field names:

tblHoursWorked.Weekly_Hours_Recorded
tblMasterTrans.Time_Spent
tblTransType.Value
tblContractInfo_SubMenuTable.Num_Checks_Trans

Here is what needs to happen:

tblTransType.Value x tblContractInfo_SubMenuTable.Num_Checks_Trans = totaltrans

tblHoursWorked.Weekly_Hours_Recorded - tblMasterTrans.Time_Spent = _ ?__ / totaltrans x 8 / 100


then output this number.

can anyone help?

[CF_Oracle]

How these tables related? Do you need numbers for each row or totals?

[cf_dev2]

<offtopic>
They put you in a straight_jacket for not knowing how to write the sql query? Dang your DBA is mean ;-)
</offtopic>

[straight_jacket]

The db is not relational. I couldn't get the other queries to function with the relations and lookups in place.
Need totals.

[straight_jacket]

A straight_jacket is the only way they can keep me at my desk with this project.

[Fordian-Slip]

Have you tried creating a store proc to do all your work and call that stored proc to generate your number for you?

[straight_jacket]

Please explain what this is and how it's done.

[Fordian-Slip]

A Stored Procedure, let's say within MS SQL, is a set of instructions that you program to do all your work on the SQL server so that it does all the hard work, leaving your CF Server's resources available for other tasks.

Now then, once you create the Stored Proc on the SQL server,

your CF code would look something like:

<cfstoredproc datasource="loss_runs" procedure="usp_zz_loss_run_step_8_gn">
<cfprocparam type="In" dbvarname="@policy_symbol" value="" cfsqltype="CF_SQL_CHAR" null="Yes">
<cfprocparam type="In" dbvarname="@policy_nbr" value="#policy#" cfsqltype="CF_SQL_CHAR">
<cfprocparam type="In" dbvarname="@policy_module" value="#module#" cfsqltype="CF_SQL_CHAR">
<cfprocresult name="good_policies">
</cfstoredproc>

Basically that code would pass 3 variable to the stored proc, your proc would do all the hardwork, and in my case I get a QUERY called "good_policies".

Then you can display your results with in normal CFQUERY command
or if it's a single result, you can just do something like:

<cfoutput>#good_policies.my_answer#</cfoutput>

And that should do it.

Hope this helps.

[straight_jacket]

I did some research on stored procs and with your explaination have a basic understanding of how it will work.
The original question still stands. I don't know the sql syntax for the query.
The query results are calculated form 4 different tables. I am new to sql and this query is over my head.

[paross1]

I'm having a problem understanding your formula. Removing all of the table names I get this;

Value * Num_Checks_Trans = totaltrans

Weekly_Hours_Recorded - Time_Spent = _?__ / totaltrans x 8 / 100

So, what is _?__ , and is this actually _?__ / totaltrans x (8 / 100) or _?__ / (totaltrans x 8) / 100 or (_?__ / totaltrans) x (8 / 100), or ((_?__ / totaltrans) x 8) / 100, etc. etc. etc.

Phil

[straight_jacket]

This is what I'm trying to do.

the totaltrans is in total number of items produced

weekly_hours_recorded, minus time_spent, will show the number of hours in actual production

dividing the hours in actual production by 8 will show how what the production was for an 8 hour shift

dividing the 8 hour shift numbers by 100 will give us a percentage of how productive the shift was.

here is a breakdown of what i'm looking to acheive:

Value multiplied by Num_Checks_Trans this equals totaltrans

Weekly_Hours_Recorded minus Time_Spent equals unknown1

unknown1 divided by totaltrans equals unknown2

unknown2 multiplied by 8 equals unknown3

unknown3 divided by 100 will equal the final answer

The final answer is a production productivity percentage

thanx for any help

[paross1]

This look like the formula?

(((Weekly_Hours_Recorded - Time_Spent) / (Value * Num_Checks_Trans)) * 8 )/100

Phil

[paross1]

Since you said that your database isn't relational, then in order for you do accomplish this in a single query, you are going to need to join a series of queries using UNIONs withinn a sub-query in order to add up your values, then plug in the values in an outer select.

Phil

[Team_Source]

Thank you for the code. I am having a hard time deciphering the totals. I figure that % is what you have named results but what if you need to also output the individual numbers as well, ie. the Weekly Hours Recorded, Hourly Avg.

[paross1]

quote:

---

what if you need to also output the individual numbers as well, ie. the Weekly Hours Recorded, Hourly Avg

---

Then you would have to add more fields to the outer select and a group by. (In your original post, you specified " I need to query the db and get a number" so I didn't include totals, etc.

Phil

[Team_Source]

Thank you for your quick response. What we need it to have a list of managers with the listed under each manager. Each employee will need to have listed their Total # of Transactions, Downtime, which is in the database as Time_Spent and then Weekly Hrs Worked, which is pulled from the Tblhoursworked.Weekly_hours_recorded, their hourly avg, which is the weekly hrs worked-downtime/# of Transactions, and then the Daily Prod Avg which is the last # multiplied by 8. I am not sure where the groupby and the additional fields would go. This query is way over my head. We are easily able to pull the Manager, employee and the Total # of transactions but then get fuzzy on the rest of it.

[Team_Source]

Thank you for your quick response. What we need it to have a list of managers with the listed under each manager. Each employee will need to have listed their Total # of Transactions, Downtime, which is in the database as Time_Spent and then Weekly Hrs Worked, which is pulled from the Tblhoursworked.Weekly_hours_recorded, their hourly avg, which is the weekly hrs worked-downtime/# of Transactions, and then the Daily Prod Avg which is the last # multiplied by 8. I am not sure where the groupby and the additional fields would go. This query is way over my head. We are easily able to pull the Manager, employee and the Total # of transactions but then get fuzzy on the rest of it.

[paross1]

Hmmmm, you said that your database was "not relational". If that is true, how are you correlating managers, employees, and hours, etc.?

Phil

[Team_Source]

well it was relational at one time but we were under the impression from some literature that we read that ColdFusion does not work well with relational databases. Is this not true?

[paross1]

quote:

---

we read that ColdFusion does not work well with relational databases

---

Pure nonsense. ColdFusion works very well with relational databases.... SQL Server, MySQL, Oracle, Access, to name a few.

[paross1]

quote:

---

I couldn't get the other queries to function with the relations and lookups in place.

---

I read this from one of your earlier posts, and I can't help wondering what you actually meant. I have a feeling that you are only presenting a very small view of what you are actually trying to do, and what you have to work with. Sometimes you need to become more proficient in a particular discipline in order to be able to ask the right questions to advance further. I have a feeling that this is the case here. Your weakness in SQL (and data modeling, I assume) is preventing you from being able to ask the right questions in order to get a satisfactory answer. I wish that I could help you, but I don't think that I'm getting the full picture here. Sorry.

Phil