April 15, 2004 at 6:43 am
If you replace [Daily Total] in the first select with
SUM([Daily Total]) as 'Daily Total'
and remove [Daily Total] from the GROUP BY clause
then you should get
110 2004-04-01 00:00:00 146.0 0.0 0.0 0.0 146.0 53.0 0 32.0 84 54 1574.0
for the first date.
Is this what you wanted?
Far away is close at hand in the images of elsewhere.
Anon.
April 15, 2004 at 6:47 am
No. The first record daily total should be 476. This number should be calculated after the product is calculated (146)
April 15, 2004 at 7:07 am
SELECT department,
[Date],
SUM(CASE [Product] WHEN 'E-Tech' THEN [output] else 0 END) as 'E-TECH',
SUM(CASE [Product] WHEN 'E-9' THEN [output] else 0 END) as 'E-9',
SUM(CASE [Product] WHEN 'E-7' THEN [output] else 0 END) as 'E-7',
SUM(CASE [Product] WHEN 'E-6' THEN [output] else 0 END) as 'E-6',
SUM([output]) as 'Total Output',
Manpower,
holidays,
Absent,
Overtime,
Launch,
Case Left(Datename(dw,[Date]),1)
When 'S' then SUM(Overtime)
else SUM([output]) + (SUM([Manpower]) * 8) - (SUM([Absent]) + SUM([Overtime])
End as 'Daily Total'
FROM (SELECT tblInput_Output.department,
tblInput_Output.[Date],
product,
([1st-output] + [2nd-output] + [3rd-output] + [OT-output]) as 'Output',
([1st Shift Manpower]+[2nd Shift Manpower]+[3rd Shift Manpower]) as 'Manpower',
holidays,
([Absent 1st Shift]+[Absent 2nd Shift]+[Absent 3rd Shift]) as 'Absent',
([Overtime 1st Shift]+[Overtime 2nd Shift]+[Overtime 3rd Shift]) as 'Overtime',
([Launch 1st Shift]+[Launch 2nd Shift]+[Launch 3rd Shift]) as 'Launch',
FROM tblAbsentOverLaunch
RIGHT OUTER JOIN tblProduct
INNER JOIN tblInput_Output
ON tblProduct.department = tblInput_Output.department
AND tblProduct.[Part Number] = tblInput_Output.[Part Number]
INNER JOIN tblCensusDaily
ON tblInput_Output.department = tblCensusDaily.Department
AND tblInput_Output.[Date] = tblCensusDaily.[Date]
LEFT OUTER JOIN tblHolidaySchedule
ON tblCensusDaily.[Date] = tblHolidaySchedule.[Date]
ON tblAbsentOverLaunch.[Date] = tblCensusDaily.[Date]
AND tblAbsentOverLaunch.department = tblCensusDaily.Department
WHERE ( tblInput_Output.department = N'110')
AND ( tblInput_Output.[Date] >= CONVERT(DATETIME, '2004-04-01 00:00:00', 102))
) a
GROUP BY
department,
[Date],
Manpower,
holidays,
Absent,
Overtime,
Launch
Far away is close at hand in the images of elsewhere.
Anon.
April 15, 2004 at 7:55 am
Hi,
I modified the query a little bit and now I am getting exactly what I want. Thank you very much for your replies. I would not be able to figure this out on my own.
CREATE PROCEDURE MTDPerformance4
AS
SELECT department,
[Date],
SUM(CASE [Product] WHEN 'E-Tech' THEN [output] else 0 END) as 'E-TECH',
SUM(CASE [Product] WHEN 'E-9' THEN [output] else 0 END) as 'E-9',
SUM(CASE [Product] WHEN 'E-7' THEN [output] else 0 END) as 'E-7',
SUM(CASE [Product] WHEN 'E-6' THEN [output] else 0 END) as 'E-6',
SUM([output]) as 'Total Output',
Manpower,
holidays,
Absent,
Overtime,
Launch,
Case Left(Datename(dw,[Date]),1)
When 'S' then (Overtime)
else (([Manpower]) * 8 * holidays) - [Absent] + [Overtime]
End as 'Daily Total'
FROM (SELECT tblInput_Output.department,
tblInput_Output.[Date],
product,
([1st-output] + [2nd-output] + [3rd-output] + [OT-output]) as 'Output',
([1st Shift Manpower]+[2nd Shift Manpower]+[3rd Shift Manpower]) as 'Manpower',
holidays,
([Absent 1st Shift]+[Absent 2nd Shift]+[Absent 3rd Shift]) as 'Absent',
([Overtime 1st Shift]+[Overtime 2nd Shift]+[Overtime 3rd Shift]) as 'Overtime',
([Launch 1st Shift]+[Launch 2nd Shift]+[Launch 3rd Shift]) as 'Launch'
FROM tblAbsentOverLaunch
RIGHT OUTER JOIN tblProduct
INNER JOIN tblInput_Output
ON tblProduct.department = tblInput_Output.department
AND tblProduct.[Part Number] = tblInput_Output.[Part Number]
INNER JOIN tblCensusDaily
ON tblInput_Output.department = tblCensusDaily.Department
AND tblInput_Output.[Date] = tblCensusDaily.[Date]
LEFT OUTER JOIN tblHolidaySchedule
ON tblCensusDaily.[Date] = tblHolidaySchedule.[Date]
ON tblAbsentOverLaunch.[Date] = tblCensusDaily.[Date]
AND tblAbsentOverLaunch.department = tblCensusDaily.Department
WHERE ( tblInput_Output.department = N'110')
AND ( tblInput_Output.[Date] >= CONVERT(DATETIME, '2004-04-01 00:00:00', 102))
) a
GROUP BY
department,
[Date],
Manpower,
holidays,
Absent,
Overtime,
Launch
GO
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply