WITH Result_CTE (DateValue,Part,TotalNumber)
AS (
SELECT [Date] as DateValue, Part, Sum(Number) as TotalNumber
FROM <tableName>
WHERE .... if you want to put some filtering ....
GROUP BY Date, Part
)
SELECT r.DateValue, r.Part, r.TotalNumber, m.MaxNumber
FROM Result_CTE r INNER JOIN (SELECT Part,MAX(TotalNumber) AS MaxNumber FROM Result_CTE GROUP BY Part) m on r.Part=m.Part;
Create a command (you can also use this in a view or stored procedure) , paste the code above in it and it will return Date, Part , TotalNumber per Part per Date and Max number per part for the period. I guess this is all you need to show. As far as I can see there are 9 lines of code - I cannot wait to see the solution with arrays in Crystal reports.SELECT [Date] as DateValue, Part, Sum(Number) as TotalNumber
FROM <tableName>
WHERE .... if you want to put some filtering ....
GROUP BY Date, Part
Your main report will get the data directly :SELECT [Date] as DateValue, Part, Sum(Number) as TotalNumber
INTO #T FROM <tableName>
WHERE .... if you want to put some filtering ....
GROUP BY Date, Part
SELECT * FROM #T
SELECT Part,MAX(TotalNumber) AS MaxNumber FROM #T GROUP BY Part
SELECT Part, MAX(Number) as Number
FROM <tableName>
WHERE .... if you want to put some filtering ....
GROUP BY Part
With other databases the syntax might be slightly different