Home » SQL & PL/SQL » SQL & PL/SQL » Difficult counting question. (counting by day when date entry for that day is not present)
Difficult counting question. (counting by day when date entry for that day is not present) [message #334175] Tue, 15 July 2008 12:24 Go to next message
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 #334176 is a reply to message #334175] Tue, 15 July 2008 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Difficult counting question. (counting by day when date entry for that day is not present) [message #334177 is a reply to message #334175] Tue, 15 July 2008 12:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Write out your algorithm first. Write in pseudo-code what your logic is.
Then search here for calendar to find a way to generate dates that are not in your table.
Then try again.

If you do it in any other order, you will probably fail and never truly understand.
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 Go to previous messageGo to next message
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

Re: Difficult counting question. (counting by day when date entry for that day is not present) [message #334180 is a reply to message #334178] Tue, 15 July 2008 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select DATEADD(DAY, 0, DATEDIFF(DAY, 0, column) as Day

These are NOT Oracle functions.
Are you working on Oracle?
Please note this is an Oracle forum.

Regards
Michel

[Updated on: Tue, 15 July 2008 13:17]

Report message to a moderator

Re: Difficult counting question. (counting by day when date entry for that day is not present) [message #334182 is a reply to message #334180] Tue, 15 July 2008 13:04 Go to previous messageGo to next message
noeltazz
Messages: 3
Registered: July 2008
Junior Member
Michel Cadot wrote on Tue, 15 July 2008 12:45

These are NOT Oracle functions.
Are you working on Oracle?
Please note this an Oracle forum.

Regards
Michel



well that would explain why that didnt work Laughing yes im on oracle. even a point in the right direction on how to solve something like this would be extremely helpful
Re: Difficult counting question. (counting by day when date entry for that day is not present) [message #334185 is a reply to message #334182] Tue, 15 July 2008 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first direction has already been given by Frank: search for "calendar".
Then use SUM function in its analytical form.
I think it will work.
Of course, if you posted a test case, I could test it.

Regards
Michel
Re: Difficult counting question. (counting by day when date entry for that day is not present) [message #334189 is a reply to message #334177] Tue, 15 July 2008 14:08 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Tue, 15 July 2008 19:33

If you do it in any other order, you will probably fail and never truly understand.

Hate to say it, but I told you so..
Previous Topic: Is there a way to schedule a Stored Procedure using Oracle-Without unix Script
Next Topic: Collection return Null
Goto Forum:
  


Current Time: Thu Dec 08 16:29:42 CST 2016

Total time taken to generate the page: 0.06649 seconds