Following is the part of data, in Access:
icode soldQty rackQty dt
---------------------------------------
14000 10 50 03/17/22 20:35
15000 1 45 03/17/22 15:35
16000 3 55 03/17/22 08:22
14000 30 48 03/18/22 14:05
15000 18 62 03/17/22 13:35
16000 3 47 03/17/22 15:23
14000 1 49 03/19/22 16:35
17000 1 49 03/17/22 15:13
14000 24 26 03/17/22 10:35
15000 10 33 03/17/22 20:37
There are more than 100 icodes, I am putting just 3 as an example. I want to generate a Weekly (or between some dates) report based on a certain time period. Say, a weekly report between 10:00 to 17:00 for 3 items, 14000, 15000 and 16000. The output, I am expecting is:
icode soldQty rackQty
14000 54 74
15000 19 107
16000 4 96
Using this query, I am able to get the sum.
select icode, sum(soldQty), sum(rackQty) from sales
group by icode having icode between 14000 and 16000
order by icode
I am confused where to put the where clause
so that I can have a condition for the
timing (10:00 to 17:00) constraint?
CodePudding user response:
Try this:
select icode, sum(soldQty), sum(rackQty)
from sales
where TimeValue(dt) between #10:00:00# and #17:00:00#
group by icode
having icode between 14000 and 16000
order by icode
CodePudding user response:
All the conditions should be placed in the WHERE
clause.
The HAVING
clause is used for conditions that contain aggregated values like SUM()
or COUNT()
and it is processed after the aggregation:
SELECT icode,
SUM(soldQty) AS total_soldQty,
SUM(rackQty) AS total_rackQty
FROM sales
WHERE icode BETWEEN 14000 AND 16000
AND TimeValue(dt) BETWEEN #10:00:00# AND #17:00:00#
GROUP BY icode
ORDER BY icode;