Need some direction

Forum|Forum|17 years ago
September 19, 2008
5 replies
489 views

Can someone point me in the right direction as to what type of code I should try using? I need to add up some totals from different tables in the database.

I have the following table structure, they have a lot more fields but I will add the important ones:

Table: associates
Fields: id, assoc_id, initials, hourlyrate

Table: caseactivity
Fields: id, assoc_id, descripofservice, initials, hoursworked

I am outputing all of my data from "caseactivity", but at the bottom of that I need to find a way to total up the hoursworked and get a total cost for the hours worked based on the initials of the person that entered the info. There will be different hourlyrate's I need to pull from the associates table. So I'm not sure how to add up. Any help in the right direction would be appreciated. Thanks!

This topic has been closed for replies.

Correct answer brianism

Thank you everyone for your help and guidance. I was able to adjust the code to the following and I get the exact results I needed!!!

<cfquery name="qtime" datasource="mydb">
select a.initials, a.hourlyrate, c.hoursworked, sum(c.hoursworked) * a.hourlyrate as totalcost
from associates a join caseactivity c on a.assoc_id = c.assoc_id

where a.initials = c.initials AND case_id = #qcases.id#
group by a.initials, c.hoursworked
</cfquery>

B

brianismAuthorCorrect answer

Inspiring

Thank you everyone for your help and guidance. I was able to adjust the code to the following and I get the exact results I needed!!!

<cfquery name="qtime" datasource="mydb">
select a.initials, a.hourlyrate, c.hoursworked, sum(c.hoursworked) * a.hourlyrate as totalcost
from associates a join caseactivity c on a.assoc_id = c.assoc_id

where a.initials = c.initials AND case_id = #qcases.id#
group by a.initials, c.hoursworked
</cfquery>

N

Newsgroup_User

Inspiring

> I changed the code to this and I get an error:

> Error:
>
> Column 'initials' in field list is ambiguous

Well... what does the error message *say*? I think that's a fairly good
indication of what's wrong. I don't mean to be obtuse... the error message
is telling you exactly what's wrong. What do you reckon it could be on
about?

--
Adam

B

brianismAuthor

Inspiring

Thank you very much for the help, I think I see what the code is doing now.

I changed the code to this and I get an error:

<cfquery name="qinitials" datasource="mydb">
SELECT * FROM associates WHERE assoc_id = #session.assoc_id#
</cfquery>

<cfquery name="qtime" datasource="mydb">
select hourlyrate, initials, sum(hoursworked) * hourlyrate as totalcost
from associates a join caseactivity c on a.assoc_id = c.assoc_id

where initials = #qinitials.initials#
</cfquery>

Error:

Column 'initials' in field list is ambiguous

26 : where initials = #qinitials.initials#

D

Dan_Bracuk

Inspiring

According to your opening post it's in the caseactivity table.

D

Dan_Bracuk

Inspiring

select field1, field2, sum(hoursworked) * hourlyrate as TheNumberYouWant
from associates a join caseactivity c on a.assoc_id = c.assoc_id

where initials = something
group by field1, field2

Now all you need to do is figure out what to do when two people have the same initials.

B

brianismAuthor

Inspiring

quote:

Originally posted by: Dan Bracuk
select field1, field2, sum(hoursworked) * hourlyrate as TheNumberYouWant
from associates a join caseactivity c on a.assoc_id = c.assoc_id

where initials = something
group by field1, field2

Now all you need to do is figure out what to do when two people have the same initials.

Thanks Dan. The variable hoursworked is not in the associates table though....

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded