summation issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

summation issues

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-14 : 13:04:35

I have following tables

taskgroup table


taskgroup
   taskgroupid
   project
   task

groupaccounts table


groupaccounts
   groupaccountid
   taskgroupid
   GLaccount
   groupname

groupbudgets table


groupbudgets
  groupbudgetid
  taskgroupid
  budgetamount

the groupaccounts table is the detail general ledger transactions, whereas groupbudgets is a way of grouping the general ledgers into one group to get the planned budget for each distinct group of general ledgers. I have been racking my brain to so the summation for each distinct group for a certain project and task .
So I want to see
Project
Task
groupname,
totalexpended

Hope it is clear.

Thanks

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 13:51:38

Jo, Can you show us some sample data and expected output.

goodsolution
Starting Member

38 Posts

Posted - 2009-12-14 : 14:08:04

select
tg.project
,tg.task
,ga.groupname
,SUM(totalexpended)
from taskgroup tg join
groupaccount ga
on tg.taskgroupid = ga.taskgroupid
join groupbudgets tb
on ga.taskgroupid = tb.taskgroupid
group by
tg.project
,tg.task
,ga.groupname

-Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-14 : 14:59:11

ok nice solution thank you! but now to throw a wrench in the mix, I want that two summations . one all activities for a specific date range and the other one . for all activities regardless of date.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

goodsolution
Starting Member

38 Posts

Posted - 2009-12-14 : 15:10:34

just add having clause as shown below

select
tg.project
,tg.task
,ga.groupname
,SUM(totalexpended)
from taskgroup tg join
groupaccount ga
on tg.taskgroupid = ga.taskgroupid
join groupbudgets tb
on ga.taskgroupid = tb.taskgroupid
group by
tg.project
,tg.task
,ga.groupname
having date_range betweeen 'start_date' and 'End_Date'

-Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-14 : 16:07:19

thank you, but I was looking to get two summations . one all activities for a specific date range and the other one . for all activities regardless of date.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

goodsolution
Starting Member

38 Posts

Posted - 2009-12-14 : 16:14:31

Can you please provide an example how result should look like?

-Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-14 : 19:03:46

thank you all! this is the result I would like to see


Expense Type         ExpenseToDate        ExpenseThisPeriod
Medical Expenses     10,000               230
Travel                9,548               12

How do you do that from one expense table ?

Thanks!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-15 : 12:17:28

ok I got this figured out. I did a subquery as follows

Select a,b,c
(Select SUM() From Trans t1 where t1.groupid = x.groupid) as ExpenseToDate,
(Select SUM() From Trans t2 where t2.groupid = x.groupid and t2.Date >= @fromdate and t2.Date<= @todate ) ExpenseThisPeriod
from x

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-15 : 12:20:45

If you have a really large data set you may want to consider making that ExpenseToDate field come from a derived table instead of subquerying, from what I understand this will re-run the query over and over for every line where the derived table will create everything in 1 run and pull back data when the criteria is matched.

For a small set I don't see much difference.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-15 : 13:34:51

good thinking there. thanks for pointing that out. the data for transactions is huge! I mean FAT! let me try the derived table approach which is a better design approach.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-15 : 14:51:43

well derived tables are nice appraoch but in my scenario it is problematic because I am dealing with views with underlying tables that dot not have the necessary indexes. They do not want to add these indexes at this point. So maybe a temp table or better yet table variable with index

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources