CODEWITHSUNDEEP
sqlpostgresqltypescountdate-range
Wine Too
Wine Too

Reputation: 4655

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

SOLUTION: And this is finally very fast, improved version which Ervin kindly give: 

        Using cmd As New NpgsqlCommand( _
             "WITH base AS (" & _
             "  SELECT TO_DATE(datum, 'DD.MM.YYYY') AS the_date " & _
             "  FROM " & myKalkTable & " " & _
             "  WHERE datum <> '') " & _
             "  SELECT MIN(the_date) AS dmin, " & _
             "         MAX(the_date) AS dmax, " & _
             "         COUNT(*) AS ct_incl, " & _
             "  (SELECT COUNT(*) " & _
             "         FROM base b1 " & _
             "         WHERE(b1.the_date < max(b.the_date)) " & _
             "         AND b1.the_date > min(b.the_date)) " & _
             "         AS ct_excl " & _
             "         FROM base b", conn)

            Using dr As NpgsqlDataReader = cmd.ExecuteReader()
                While dr.Read()
                    mindate = CType(CDate(CStr(dr(0))), Date)
                    maxdate = CType(CDate(CStr(dr(1))), Date)
                    count = CInt(dr(2))
                End While
            End Using
        End Using

Upvotes: 3

Views: 25411

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656982

Given this table (like you should have provided):

CREATE TEMP TABLE tbl (
   id        int PRIMARY KEY
  ,mydatetxt text
 );

INSERT INTO tbl VALUES
  (1, '01.02.2011')
 ,(2, '05.01.2011')
 ,(3, '06.03.2012')
 ,(4, '07.08.2011')
 ,(5, '04.03.2013')
 ,(6, '06.08.2011')
 ,(7, '')             -- empty string
 ,(8, '02.02.2013')
 ,(9, '04.06.2010')
 ,(10, '10.10.2012')
 ,(11, '04.04.2012')
 ,(12, NULL)          -- NULL
 ,(13, '04.03.2013'); -- min date a 2nd time

The query should produce what you describe:

result as minimal date, maximal date, count of dates between and including min and max dates

WITH base AS (
   SELECT to_date(mydatetxt, 'DD.MM.YYYY') AS the_date
   FROM   tbl
   WHERE  mydatetxt <> ''  -- excludes NULL and ''
   )
SELECT min(the_date) AS dmin
      ,max(the_date) AS dmax
      ,count(*) AS ct_incl
      ,(SELECT count(*)
        FROM   base b1
        WHERE  b1.the_date < max(b.the_date)
        AND    b1.the_date > min(b.the_date)
       ) AS ct_excl
FROM   base b

-> SQLfiddle demo

CTEs require Postgres 8.4 or later.
Consider to upgrade to the latest point release of 9.1, which is currently 9.1.9.

Upvotes: 8

Related Questions