Difficult counting question. (counting by day when date entry for that day is not present) [message #334175] |
Tue, 15 July 2008 12:24  |
noeltazz
Messages: 3 Registered: July 2008
|
Junior Member |
|
|
The tables orders is where we track the products. Basically I'm trying to create a query/view that lists the total numbers of items sitting in inventory on any particular day (example below).
With only the first day the item checks in, and the last day the item checks in. If the item has a LastDate entry of today (like item AAAAA) then it is still inventory.
However if the LastDate entry is a date in the past then we know the item is gone and was sold on that day (eg. BBBBB first came into inventory on 7/5/2008, we sold it
on 7/10/2008)
Table: ORDERS
FirstDate LastDate ItemID Price
7/1/2008 7/15/2008 AAAAA $38,225
7/5/2008 7/10/2008 BBBBB $27,991
7/12/2008 7/14/2008 CCCCC $27,925
7/2/2008 7/3/2008 DDDDD $53,875
7/5/2008 7/5/2008 EEEEE $23,584
Table: Inventory
Date Inventory (Result that we want)
7/1/2008 1
7/2/2008 2
7/3/2008 2
7/4/2008 1
7/5/2008 3
7/6/2008 2
7/7/2008 2
7/8/2008 2
7/9/2008 2
7/10/2008 2
7/11/2008 1
7/12/2008 2
7/13/2008 2
7/14/2008 2
7/15/2008 1
Not sure if this is possible to do. Another way of structuring that sold table is having one column for both First and Last Date.
FirstDate ] ItemID Price
7/1/2008 AAAAA $38,225
7/15/2008 AAAAA $38225
7/5/2008 BBBBB $27991
7/10/2008 BBBBB $27,991
7/12/2008 CCCCC $27925
7/14/2008 CCCCC $27,925
7/2/2008 DDDDD $53875
7/3/2008 DDDDD $53,875
7/5/2008 EEEEE $23584
7/5/2008 EEEEE $23,584
[Mod-Edit: Frank added code-tags to improve readability]
[Updated on: Tue, 15 July 2008 12:31] by Moderator Report message to a moderator
|
|
|
|
|
Re: Difficult counting question. (counting by day when date entry for that day is not present) [message #334178 is a reply to message #334177] |
Tue, 15 July 2008 12:38   |
noeltazz
Messages: 3 Registered: July 2008
|
Junior Member |
|
|
I've tried a select DATEADD(DAY, 0, DATEDIFF(DAY, 0, column) as Day,
COUNT(DISTINCT id) as NumOfSales
from table
first, because i thought it wolud have to add up each day automatically because its not listed in the column (only first and latest day are listed), but I got nowhere, then i tried an
if (select
type query but was havnt been able to figure it out because there are missing dates.
My first guess would be that its something like...
if date for product AAAAA falls between the firstdate for AAAAA and lastdate for AAAAAA then count it as present for that day, then add 1 day to date, and repeat, do it for every item
but I have no idea how to write that out in code.
[Updated on: Tue, 15 July 2008 12:39] Report message to a moderator
|
|
|
|
|
|
|