Can anyway help me write this query? I've created a DB to help me
manage delivery logistics for items I rent out. I have a table that i
use to record delivery date, delivery time, pickup date, pickup time,
and quantity (of items to drop off). All of the items are identical so
i don't need to distinguish by type. I have 80 deliveries and 80
pickups over 7 days. I would like to generate a query that will list
the total number of items out each day. I do not need to account for
the specific times throught the day.
How to project inventory using time blocks
Can anyway help me write this query? I've created a DB to help me
manage delivery logistics for items I rent out. I have a table that i
use to record delivery date, delivery time, pickup date, pickup time,
and quantity (of items to drop off). All of the items are identical so
i don't need to distinguish by type. I have 80 deliveries and 80
pickups over 7 days. I would like to generate a query that will list
the total number of items out each day. I do not need to account for
the specific times throught the day.
(e-mail address removed),
I couldn't specifically identify a primary key.
For the purposes of this example, I used the first five columns,
though this required that no two ItemID values ever arrived and
departed at the same times.
Table:
CREATE TABLE Items
(ItemID INTEGER
,DeliveryDate DATETIME
,DeliveryTime DATETIME
,PickupDate DATETIME
,PickupTime DATETIME
,Qty INTEGER
,CONSTRAINT pk_ItemID
PRIMARY KEY (ItemID
,DeliveryDate
,DeliveryTime
,PickupDate
,PickupTime)
)
Sample Data
1, 08/01/2006, 00:01, 09/01/2006, 00:01, 5
1, 08/02/2006, 00:02, 09/01/2006, 00:02, 5
2, 08/03/2006, 00:01, 09/04/2006, 00:01, 20
2, 08/03/2006, 00:01, 09/05/2006, 00:01, 25
3, 08/06/2006, 00:01, 09/08/2006, 00:01, 30
3, 08/07/2006, 00:01, 09/09/2006, 00:01, 35
4, 08/10/2006, 00:02, 09/11/2006, 00:04, 40
4, 08/10/2006, 00:03, 09/12/2006, 00:05, 45
5, 08/10/2006, 00:06, 09/10/2006, 00:06, 100
5, 08/10/2006, 00:07, 09/10/2006, 00:06, 150
Query: ProjectInvetoryUsingTimeBlocks
SELECT I1.PickupDate
,SUM(I1.Qty) AS ItemsOut
FROM Items AS I1
GROUP BY I1.PickupDate
Results:
PickupDate ItemsOut
09/01/2006 10
09/04/2006 20
09/05/2006 25
09/08/2006 30
09/09/2006 35
09/10/2006 250
09/11/2006 40
09/12/2006 45
This appears to show the "items out each day" as far as I can tell.
Sincerely,
Chris O.
