CODEWITHSUNDEEP
vb.netms-access
John
John

Reputation: 2852

How to get the ALL max or min values between two dates

I have a database table with 2 fields; Date and Value. The Date field stores daily dates, without duplication and Value field stores, the daily value of an instrument. This value is inserted into the field Value. This value can be either positive of negative, ranging from -15 to 15, right now.

Based on this a graph is plotted keeping a center line at ZERO. So far, I have done this. enter image description here

Now, I am facing a problem while generating a report. Say, I want to get the list of dates when the value line start rising/falling between 2 dates. For example: Between 10-jan and 2-Feb, list the dates when either the value starts rising/falling.

I know simple SQL won't help me much. Can someone suggest me where to start from?

Upvotes: 1

Views: 859

Answers (3)

shadow
shadow

Reputation: 1903

Maybe something like this?

Sub Main()
        Dim pairs = New List(Of Pair)

        ' pairs ordered by date
        pairs.Add(New Pair() With {.Date = #1/1/2016#, .Value = 1})
        pairs.Add(New Pair() With {.Date = #1/2/2016#, .Value = 2})
        pairs.Add(New Pair() With {.Date = #1/30/2016#, .Value = 3}) ' <-- high peak
        pairs.Add(New Pair() With {.Date = #2/1/2016#, .Value = 2})
        pairs.Add(New Pair() With {.Date = #2/2/2016#, .Value = 1})
        pairs.Add(New Pair() With {.Date = #2/10/2016#, .Value = -2}) ' <-- low peak
        pairs.Add(New Pair() With {.Date = #3/1/2016#, .Value = 1})
        pairs.Add(New Pair() With {.Date = #3/2/2016#, .Value = 2})
        pairs.Add(New Pair() With {.Date = #3/26/2016#, .Value = 3}) ' <-- high peak
        pairs.Add(New Pair() With {.Date = #4/1/2016#, .Value = 2})
        pairs.Add(New Pair() With {.Date = #4/5/2016#, .Value = 1}) ' <-- low peak
        pairs.Add(New Pair() With {.Date = #4/10/2016#, .Value = 2})


        For i As Integer = 0 To pairs.Count - 1
            If ((i > 0) AndAlso (i < pairs.Count - 1)) Then
                Dim previousPair As Pair = pairs(i - 1)
                Dim nextPair As Pair = pairs(i + 1)
                Dim currentPair As Pair = pairs(i)

                currentPair.IsPeak = ((previousPair.Value < currentPair.Value) AndAlso (currentPair.Value > nextPair.Value)) OrElse ((previousPair.Value > currentPair.Value) AndAlso (currentPair.Value < nextPair.Value))
            End If
        Next

        For Each p In pairs
            If p.IsPeak Then
                Console.WriteLine(p)
            End If
        Next

        Console.ReadLine()
    End Sub


    Private Class Pair
        Public Property [Date] As DateTime
        Public Property Value As Integer
        Public Property IsPeak As Boolean

        Public Overrides Function ToString() As String
            Return String.Format("{0:dd/MM/yyyy} {1}", [Date], Value)
        End Function
    End Class

Upvotes: 3

Gustav
Gustav

Reputation: 55816

A simple method would be to open the table sorted by date.

Now, loop through the records. When moving to the next record, compare the difference between current Value and Value of the previous record with the difference between Values of the previous two records. If the slopes differ, you have a hit.

Upvotes: 3

A.Bahrami Bavani
A.Bahrami Bavani

Reputation: 19

you should store tick time in database you can easily convert tick time to date and generate report from/to date with ticktime

for store "now ticktime" in database 1-you should store ticktime variable in database date field:

long ticktime = DateTime.Now.Ticks;

2-then for generate report from database Between 10-jan-1998 and 2-Feb-1998

DateTime from_dt = new DateTime(1998, 01, 10);
DateTime to_dt = new DateTime(1998, 02, 02);

3-sql query ("date is table column or field")

select * from tablex where(tablex.date>=from_dt.Ticks and tablex.date<=to_dt.Ticks)

Upvotes: 0

Related Questions