I have this report that adds the Total number of column instead of the value of the exact column.
e.g
Company Exact
TUSKEEGEE 100
TUSKEEGEE 100
My code add the number of column which is 4 instead of the value which is 400.
My Code
CREATE PROCEDURE [dbo].[Total_MealLog]
@Start VARCHAR(10),
@End VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 'S/N',*
INTO #Data
FROM
(
SELECT DISTINCT DATENAME(dw, Time1)+', ' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPN,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN')*(SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPNCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULK,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULKCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREY,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREYCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECH,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECHCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOG,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOGCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULK,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULKCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOG,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOGCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEE,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEECOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULK,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULKCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITOR,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITORCOST,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOL,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOLCOST
FROM MealLog ml
WHERE Time1 >= @Start and Time1 <= @End
GROUP BY Time1, Company, Exact
)t
SELECT * FROM #Data
UNION ALL
SELECT '','Total',SUM(ARPN),SUM(ARPNCOST),SUM(ARPNBULK),SUM(ARPNBULKCOST),
SUM(AGGREY),SUM(AGGREYCOST),SUM(CRIMETECH),SUM(CRIMETECHCOST),SUM(DELOG),SUM(DELOGCOST),
SUM(DELOGBULK),SUM(DELOGBULKCOST),SUM(RDELOG),SUM(RDELOGCOST),SUM(TUSKEEGEE),SUM(TUSKEEGEECOST),
SUM(TUSKEEGEEBULK),SUM(TUSKEEGEEBULKCOST),SUM(VISITOR),SUM(VISITORCOST),SUM(MOPOL),SUM(MOPOLCOST)
FROM #Data
DROP TABLE #Data
END
you can see that
Row no 372804 Exact - 100
Row no 372805 Exact -200
Row no 372806 Exact -300
Row no 372807 Exact -400
Total Exact -1000
Please help