I have two tables I have to join. Table1 contains tasks, Table2 is a group table that
contains tasks that are grouped from the Table1. Table one as a field called group_id
which houses the coresponding Task_id from Table2 as the foreign key. Table1 tasks
contain all the accounting information. When you call Table2 to get the data for the
group you mathmatically sum up the amounts fields. I also need to get a count of the
total of groups not the total of the tasks within the group.
Problem: I get the sql to return correctly except my count is based on the total
number of tasks. So if I had 3 groups of 5 tasks each I want to return count of 3,
instead I get of 15.
Included is my SQL:
SELECT t.status as rf1,COUNT(*) as rf0,ROUND(SUM(th.current_amt),3) as
rf2,DATEDIFF(dd,min(t.start_dte),getdate()) as
OLDEST,DATEDIFF(dd,max(t.start_dte),getdate()) as NEWEST
FROM task_group t,task_header th
WHERE t.task_id = th.group_id
AND th.owner ='B62994'
AND t.owner = th.owner
AND (t.task_type ='ALL'
OR t.task_type = 'DB'
OR t.task_type = 'DD'
OR t.task_type = 'OAC'
OR t.task_type = 'PDI'
OR t.task_type = 'UCD')
AND t.status not in ('Closed','CR')
AND (t.status <> '' or t.status <> ' ')
GROUP BY t.status
....................................
Get your own free email account from
http://www.popmail.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
