PostgreSQL, min, max and count of dates in range
This question is based of two previous here and here.
I am trying very hard to get those two queries:
SELECT min(to_date(nullif(mydatetext,''), 'DD.MM.YYYY')) AS dmin,
max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) AS dmax
FROM mytable
and
SELECT count(*)
FROM mytable
WHERE
to_date(nullif(mydatetxt,'')) 'ERROR HERE
BETWEEN
max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
AND
min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
in single one so I can read result as minimal date, maximal date, count of
dates between and including min and max dates. But here are few problems.
Second query don't work as expected or don't work at all so have to be
improved. If those two queries can be writen in single query (?) can I use
dmin and dmax variables from first part as variables in second part? Like
this:
SELECT count(*)
FROM mytable
WHERE
to_date(nullif(mydatetxt,'')) 'ERROR HERE
BETWEEN
dmin
AND
dmax
Please help to solve this situation finally.
Workable code:
Using cmd As New NpgsqlCommand("SELECT my_id, mydate FROM " & mytable, conn)
Using dr As NpgsqlDataReader = cmd.ExecuteReader()
While dr.Read()
mydate = CStr(dr(1))
If IsDate(mydate) Then
Dim dat As Date = CDate(mydate.Substring(6, 4) & "/" &
mydate.Substring(3, 2) & "/" & mydate.Substring(0, 2))
If dat < mindate Or mindate = Nothing Then
mindate = dat
End If
If dat > maxddate Or maxdate = Nothing Then
maxdate = dat
End If
count += 1
End If
End While
End Using
End Using
No comments:
Post a Comment