September 15, 2006 at 9:26 am
Hi all,
I have got table deisgn as follows
Employee InvoiceTotals
EmpId InvoiceNo
Emp Name Date
TotalNet
EmpID
Values in Employee Table
EmpId Emp Name
1 ABC
2 XYZ
3 MNO
Values in InvoiceTotals
InvoiceNo Date TotalNet EmpId
1234 15/09/2006 1000 1
2356 15/09/2006 1000 2
4567 15/09/2006 500 1
5678 15/09/2006 700 2
How can I get the Following Result by one query
Emp Name Total Net
ABC 1500
XYZ 1700
MNO 0
I have got the query that will give me above result but without last row where the records of Emp is not in InvoiceTotals Table.
Mitesh
September 15, 2006 at 9:33 am
You are probably using an inner join in your query and since there's no match for "MNO" in the "InvoiceTables" you don't see a result for that employee - change it to a "left join" instead...
**ASCII stupid question, get a stupid ANSI !!!**
September 15, 2006 at 9:33 am
Mitesh
You haven't posted your original query, but I'm guessing you used an INNER JOIN to join the Employee table to the InvoiceTable totals. Try using a LEFT OUTER JOIN, and the COALESCE function to turn the NULLS into 0s where appropriate.
John
September 15, 2006 at 9:41 am
Here is the Original Query where [Nominal Code] is Employee Id
select sum([Total Net]) as TotalNet,sum([Total CostBC]) as TotalCost,
SD.[Nominal Code],SD.[Employee]
from Invoicetotals,(select [Nominal Code],Employee
from staffdetails
where department ='Sales' and Active ='Y') as SD
where Invoicetotals.[Nominal Code]= SD.[Nominal Code]
and (Invoicetotals.[Invoice Date] between '15/09/2006' and '15/09/2006')
group by Invoicetotals.[Nominal code],SD.[Nominal Code],SD.[Employee]
order by SD.[Nominal Code]
Mitesh
September 15, 2006 at 10:06 am
Here is the modified Query
select
StaffDetails.[Employee],
ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet,
ISNULL
(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost
from
StaffDetails Left Join InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code]
where
StaffDetails.Active ='Y' and StaffDetails.[Department]='Sales'
and
(InvoiceTotals.[Invoice Date] between '15/09/2006' and '15/09/2006')
group
by StaffDetails.[Employee]
but still the same results.
Mitesh
September 15, 2006 at 10:56 am
Try this:
Select StaffDetails.EmpId,StaffDetails.EmpName,EmpTotals.SumTotalNet,EmpTotals.SumTotalCost
from StaffDetails LEFT OUTER JOIN (
select EmpId,sum([Total Net]) as SumTotalNet,sum([Total CostBC]) as SumTotalCost
from InvoiceTotals
group by EmpId ) EmpTotals on StaffDetails.EmpId = EmpTotals.EmpId
Thanks
Sreejith
September 15, 2006 at 11:13 am
I'm not quite sure but I think it has to do with the "order of execution"...
Try this as well...
select StaffDetails.[Employee], ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet, ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost from StaffDetails Left Join InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code] and (InvoiceTotals.[Invoice Date] between '15/09/2006' and '15/09/2006') where StaffDetails.Active ='Y' and StaffDetails.[Department]='Sales' group by StaffDetails.[Employee]
A couple of other observations...
1) You may want to consider using an ISO format for the date.
2) Spaces in column names (or any other object names) are highly avoidable.
**ASCII stupid question, get a stupid ANSI !!!**
September 15, 2006 at 11:40 am
I don't have time to test the query I posted but it should work based on the order of execution of a select statement...here's the outlined sequence from the (in)famous J.C..
a) Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The table expression> AS option allows you give a name to this working table which you then have to use for the rest of the containing query. b) Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE clause is applied to the working set in the FROM clause. c) Go to the optional GROUP BY clause, make groups and reduce each group to a single row, replacing the original working table with the new grouped table. The rows of a grouped table must be group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or (4) an expression made up those three items. d) Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group. e) Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The "AS" operator can also give names to expressions in the SELECT list. These new names come into existence all at once, but after the WHERE clause, GROUP BY clause and HAVING clause has been executed; you cannot use them in the SELECT list or the WHERE clause for that reason. If there is a SELECT DISTINCT, then redundant duplicate rows are removed. For purposes of defining a duplicate row, NULLs are treated as matching (just like in the GROUP BY). f) Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.
**ASCII stupid question, get a stupid ANSI !!!**
September 19, 2006 at 2:38 am
Firstly, thank you very much guys for all your help and secondly my apologies for not getting back very promptly.
Here is the query that worked for. I really appreciate the suggestion have provided.
Select StaffDetails.[Nominal Code],StaffDetails.[Employee] as Reference, ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet,
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost,ISNULL(SUM(InvoiceTotals.[Total Net]), 0) - ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalProfit,
case ISNULL(SUM(InvoiceTotals.[Total Net]), 0)
when 0 then 0.0
else ((ISNULL(SUM(InvoiceTotals.[Total Net]), 0) -
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0)) /
ISNULL(SUM(InvoiceTotals.[Total Net]), 0)) * 100
end AS Margin,
ISNULL(SUM(InvoiceTotals.[Total Net]), 0) - ISNULL(SUM(InvoiceTotals.[Total CostLC]), 0)
AS TotalProfitLC
from StaffDetails Left Join
InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code] and (InvoiceTotals.[Invoice Date] between @StartDate and @EndDate) where StaffDetails.Active ='Y' and staffDetails.[Department]='Sales'
group by StaffDetails.[Nominal code], StaffDetails.[Employee]
order by StaffDetails.[Nominal Code]
Mitesh
September 19, 2006 at 3:20 am
Mitesh
This part of your query worries me:
((ISNULL(SUM(InvoiceTotals.[Total Net]), 0) -
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0)) /
ISNULL(SUM(InvoiceTotals.[Total Net]), 0)) * 100
If all the values in [Total Net] are NULL, then you're going to have a division by zero error.
John
September 19, 2006 at 3:42 am
select StaffDetails.[Employee],
ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet,
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost
from StaffDetails
Left Join InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code] and (InvoiceTotals.[Invoice Date] between '15/09/2006' and '15/09/2006')
where StaffDetails.Active ='Y' and StaffDetails.[Department]='Sales'
group by StaffDetails.[Employee]
_____________
Code for TallyGenerator
September 19, 2006 at 5:02 am
John - I believe the query is checking for "InvoiceTotals.[Total Net]" not being zero first...
**ASCII stupid question, get a stupid ANSI !!!**
September 19, 2006 at 5:04 am
hmm...weird...here's what I'd actually posted...
John - I believe the query is checking for "InvoiceTotals.[Total Net]" not being zero first...
**ASCII stupid question, get a stupid ANSI !!!**
September 19, 2006 at 5:37 am
Sushila
Yes - you're right - I hadn't noticed the CASE statement higher up that traps the 0.
John
[Edited when realisation dawned on me!]
September 19, 2006 at 5:46 am
You are right John i had that problem but case statement will handle that.
case ISNULL(SUM(InvoiceTotals.[Total Net]), 0)
when 0 then 0.0
else ((ISNULL(SUM(InvoiceTotals.[Total Net]), 0) -
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0)) /
ISNULL(SUM(InvoiceTotals.[Total Net]), 0)) * 100
end AS Margin,
Mitesh
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply