Hello Experts Exchange
I'm trying to write a SQL script that has three tables, 1 table has a list of all the Area's, the other two tables have the data that I want to sum up.
I have a table with the Area's so that if a Area has no data it comes back as zero.
One table has a column called Total Lost Hours, the other table has Target_Lost_Hours and I want to sum up these by Area.
I have a script which is the following;
ISNULL([Total Lost Hours],0) AS [Total Lost Hours],
ISNULL([Target_Lost_Hours],0) AS [Target_Lost_Hours]
[dbo].[MachineLosses_Area] cat left join
(SELECT a.[Area], Isnull(SUM(b.[Total Lost Hours]),0) AS [Total Lost Hours], Isnull(SUM(c.[Target_Lost_Hours]),0) AS [Target_Lost_Hours]
FROM [dbo].[MachineLosses_Area] a
left outer join [dbo].[MachineLosses_View] b on a.[Area] = b.[Area/Line/Machine]
left outer join [dbo].[TargetLosses_View] c on a.[Area] = c.[Area] and b.[Date] = c.[Date]
where b.[date] between '01/10/2014' and '20/10/2014'
GROUP BY a.[Area]
) sub on cat.[Area] = sub.[Area]
[Total Lost Hours] DESC
The script comes back but the values are double what they should be.
Can you see where I can change the script to achive my goal please?