| Author |
Topic |
|
paul.rowling
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-21 : 05:56:26
|
| Hi,I have a transactions table which stores info on what a customer bought, how much they spent and when they bought it etc.The field which records the amount spent is "rec_pr_total" there is also a field called "rec_dept" which records the department the transaction occurred in. There are 3 other tables:DepartmentsWorkgroupsBusinessThe departments table identifies single departments, the workgroups identifies a collection of departments and the business table identifies a collection of workgroups.The keys for each table are as follows:Departments: rec_dept, group_idWorkgroups: group_id, business_idBusiness: business_idThe departments table relates to the transactions table via the rec_dept, the workgroups table relates to the departments table via the group_id, and the business table relates to the workgroups table via the business_id. This then allows me to find how much has been spent per business_id etc.What I want to do is write a query to find how much has been spent under business_id 1 for example. I have written a query below to do it but it is not calculating the amount properly, it is producing a massive figure. I think I have not joined the tables correctly in the query? Can anyone help?My query is as follows:select sum(transaction_new.REC_PR_TOTAL)FROM transaction_newINNER JOIN departments on transaction_new.rec_dept = departments.rec_deptINNER JOIN workgroups on departments.group_id = workgroups.group_idINNER JOIN business on workgroups.business_id = business.business_idwhere business.business_id = 1CheersPaul |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-21 : 06:30:06
|
| Are you sure the number is incorrect? Because the query looks okay to me, and you are looking for the grand total from an entire business unit, which might contain multiple workgroups, and each workgroup might contain many departments. Perhaps you need more filters on the transaction tables, to exclude certain records such as unapproved transactions, cancelled transactions, sales returns, etc?Owais |
 |
|
|
paul.rowling
Yak Posting Veteran
81 Posts |
Posted - 2003-08-21 : 06:51:45
|
| "Are you sure the number is incorrect? Because the query looks okay to me, and you are looking for the grand total from an entire business unit, which might contain multiple workgroups, and each workgroup might contain many departments."The number is definately incorrect, what you describe above is exactly how it should work. There are no filters required as the only transactions that shouldn't be included are stored as a negative value as an adjust "business_id".Any other ideas? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-21 : 07:51:55
|
Your query is not looking at the adjust id though, so surely the figure will be higher than expected? The code does work...create table #transaction_new (REC_PR_TOTAL int , rec_dept int)create table #Departments (rec_dept int, group_id int)create table #Workgroups (group_id int, business_id int)create table #Business (business_id int)insert into #transaction_new values (10,1)insert into #transaction_new values (10,2)insert into #transaction_new values (15,1)insert into #Departments values (1,1)insert into #Workgroups values (1,1)insert into #Workgroups values (2,1)insert into #Business values (1)select sum(#transaction_new.REC_PR_TOTAL)FROM #transaction_newINNER JOIN #departments on #transaction_new.rec_dept = #departments.rec_deptINNER JOIN #workgroups on #departments.group_id = #workgroups.group_idINNER JOIN #business on #workgroups.business_id = #business.business_idwhere #business.business_id = 1drop table #Departmentsdrop table #businessdrop table #transaction_newdrop table #Workgroups The only possible prob I could see is if a particular unit was somehow included twice in the level above, which might cause problems (IE Double the value for that unit), however this should not be possible if you have your keying right.-------Moo. :) |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-21 : 07:52:40
|
| You may find it useful to group your output by the different levels so you can look for suspicious figures at a more granular level.-------Moo. :) |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-21 : 07:58:16
|
| Paul,Your query should work because you have the correct joins on the tables. May I suggest you select the figure rather than sum it up and make a note of how many rows are returned. It is hard to help out because we don't know what kind of data you're working with. How many rows do you expect the query to return?------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-08-21 : 08:53:51
|
| It seems to me that you are summing ALL the transactions for the department in which resides the workgroup in which resides business_id = 1, which I would guess could be very large. Unless you could more closely associate the transaction to the business, I would think this could be your issue.Has this query or any type like it ever worked previously? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-21 : 09:05:12
|
| The query as written sums all the transactions for anything in business id 1 as you say, I assumed that this is as intended, as no further criteria was given. I'd expect it to be a large number, if there are a large volume of transactions.-------Moo. :) |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-08-21 : 09:33:32
|
quote:
What I want to do is write a query to find how much has been spent under business_id 1 for example.
But that is not what his query is doing. I was just pointing out what his query, as written, was doing and trying to confirm what he was trying to do. The query is doing what it is supposed to do, but I think he is trying to do something different. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-21 : 09:58:48
|
But it is though. It's showing summing all transactions that took place in any departments inside in workgroups that are in Business Id 1. Unless I'm missing something here  -------Moo. :) |
|
|
|
paul.rowling
Yak Posting Veteran
81 Posts |
Posted - 2003-08-21 : 10:23:59
|
| Panic Over!!!My query does work, it was the data which was at fault. I was expecting results based on one shop, but I actually had multiple shops data held in the table therefore making the results far greater than I expected. Sorry for the confusion.Best RegardsPaul |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-22 : 05:45:38
|
quote:
Originally posted by paul.rowling
Panic Over!!!My query does work, it was the data which was at fault. I was expecting results based on one shop, but I actually had multiple shops data held in the table therefore making the results far greater than I expected. Sorry for the confusion.Best RegardsPaul
Paul... Paul....    ------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
|
Multiple join query