Counting missing date intervals

[jja]

Hello guys,

I need some help. I'm doing a project that calculates what was missing in the warehouse but I'm stuck a bit. I will describe what i want to achieve.

I have a table with the fields.
1. Date
2. Freebalance

The Date field contains of every day for a period that i specify so for example if i specify 2010.06.01 - 2010.06.30 it will list me day by day of the month 06

The field Freebalance contains of a qty of some kind of warehouse goods.
Example:
Date Freebalance
2010.06.01 10
2010.06.02 15
2010.06.03 25
2010.06.04 85
2010.06.05 40
2010.06.06 25
2010.06.07 30
2010.06.08 58
2010.06.09 87
2010.06.10 0
2010.06.11 25
2010.06.12 58
2010.06.13 47
2010.06.14 58
2010.06.15 58
2010.06.16 44
2010.06.17 19
2010.06.18 0
2010.06.19 -10
2010.06.20 -10
2010.06.21 -25
2010.06.22 15
2010.06.23 14
2010.06.24 25
2010.06.25 4
2010.06.26 -67
2010.06.27 -154
2010.06.28 - -300
2010.06.29 -500
2010.06.30 - 1600

I want to write a procedure which could count me the number of intervals this good was out of stock(out of stock means it was<=0)

i can see that by the date 2010.06.10 this was 0 and next day it was on stock again so it means the counter of missing intervals is 1

Next i can see that by the date 2010.06.18 it again set to 0 but i can see that it back to stock 2010.06.22. So it means that it is again an interval of missing(2010.06.18 - 2010.06.21). So now i plus the counter i i already have 2 intervals.

Next i see that it is out of stock starting 2010.06.26 and till the end of my analyzing period is out also(2010-06.26-2010.06.30). So the counter is set to 3.

So it means whern i analyze the period for 2010.06.01-2010.06.30 for that good i have to get result of missing intervals 3.
Any help would be greatful.
Thanks

 

[nathansav]

Get the information into a recordset, then either loop through and count the 0's or a sql statement to count all 0's between these dates. Is this what you wanted "4 instances of no stock" or do you want "no stock for 1 day from 10/6 and 3 days from 13/6"

 

[jja]

Yes i want no stock for 06/10, then no stock for period 06/18-06/21 and no stock for 06/25 - 06/30. 3 "no stock" totals". I have some coding with recordset can you help me with loop and counting those intervals
Function GetINT() As String
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LINT As String

Set db = CurrentDb()

LSQL = "select * from table"
Set Lrs = db.OpenRecordset(LSQL)
.....Some loop code here....
End Function

 

[nathansav]

If i have understood right, this should do it. Uses ADO

<CODE>
Sub nathans_test()
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim con As ADODB.Connection
Dim dtStock0Start, dtStock0End As Date
Dim blnStock0Recording As Boolean
Set con = Application.CurrentProject.Connection
blnStock0Recording = False
strSQL = "Select * from tbl_Stock Order by Stock_Date;"
rst.Open strSQL, con, adOpenStatic
While Not rst.EOF
If rst.Fields("Stock_level").Value = 0 And blnStock0Recording = False Then
dtStock0Start = rst.Fields("Stock_Date").Value
blnStock0Recording = True
Else
If rst.Fields("Stock_level").Value > 0 And blnStock0Recording = True Then
dtStock0End = rst.Fields("Stock_Date").Value
Debug.Print "Zero stock from " & dtStock0Start & " to " & dtStock0End
blnStock0Recording = False
End If
End If
rst.MoveNext
Wend
Set rst = Nothing
Set con = Nothing
End Sub
</CODE>

Result:

Zero stock from 02/01/2010 to 05/01/2010
Zero stock from 07/01/2010 to 08/01/2010

 

[jja]

OK i see. But how i can return those results to a query. Because i will be analyzing stock code for period. for example how i could show in query like this:

stockcode: missing
220022510 3

the debug print doesn't help me at this time. I need to show the number.

 

[nathansav]

Change it to a function, like below

Function count_stock_zeros() As Integer
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim con As ADODB.Connection
Dim dtStock0Start, dtStock0End As Date
Dim blnStock0Recording As Boolean
Dim intStockGaps As Integer
Set con = Application.CurrentProject.Connection
blnStock0Recording = False
intStockGaps = 0
strSQL = "Select * from tbl_Stock Order by Stock_Date;"
rst.Open strSQL, con, adOpenStatic
While Not rst.EOF
If rst.Fields("Stock_level").Value = 0 And blnStock0Recording = False Then
blnStock0Recording = True
Else
If rst.Fields("Stock_level").Value > 0 And blnStock0Recording = True Then
intStockGaps = intStockGaps + 1
blnStock0Recording = False
End If
End If
rst.MoveNext
Wend
count_stock_zeros = intStockGaps
Set rst = Nothing
Set con = Nothing
End Function

Then set where you need to show it to =count_stock_zeros()

so, me.txtStock0LevelCount=count_stock_zeros()

This should be a good start.

 

[jja]

Hi again,
Thanks a lot for help but as a result i get 1 if i use it in function. The counter i think somehow doesn't count.

 

[jja]

Finally i got it to work!!!