Hi lingers,
Refer the below query. I have modified and simplified the query.
You need to use sub query to get the output.
SQL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 'S/N',*
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 = 'MOBOL') MOBOL,
(SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOBOL')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOBOL') MOBOLCOST
FROM MealLog ml
WHERE Time1 >= '2021-12-21' and Time1 <= '2021-12-22'
GROUP BY Time1, Company, Exact
)t
Output
S/N |
QUERYDATE |
ARPN |
ARPNCOST |
ARPNBULK |
ARPNBULKCOST |
AGGREY |
AGGREYCOST |
CRIMETECH |
CRIMETECHCOST |
DELOG |
DELOGCOST |
DELOGBULK |
DELOGBULKCOST |
RDELOG |
RDELOGCOST |
TUSKEEGEE |
TUSKEEGEECOST |
TUSKEEGEEBULK |
TUSKEEGEEBULKCOST |
VISITOR |
VISITORCOST |
MOBOL |
MOBOLCOST |
1 |
Tuesday, Dec 21, 2021 |
4 |
2400 |
4 |
2400 |
3 |
1800 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
2400 |
4 |
1600 |
2 |
Wednesday, Dec 22, 2021 |
4 |
2400 |
4 |
2400 |
3 |
1800 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
1600 |
4 |
2400 |
4 |
1600 |