title: PostgreSQL, min, max and count of dates in range
body: <p>This question is based of two previous <a href="https://stackoverflow.com/questions/18183744/postgresql-getting-min-and-max-date-from-text-column">here</a> and <a href="https://stackoverflow.com/questions/18185576/postgresql-count-of-rows-between-min-and-max-dates">here</a>.</p>
  
  <p>I am trying very hard to get those two queries:</p>
  
  <pre><code>SELECT min(to_date(nullif(mydatetext,''), 'DD.MM.YYYY')) AS dmin,
         max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) AS dmax
  FROM mytable
  </code></pre>
  
  <p>and</p>
  
  <pre><code>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'))
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT count(*)
  FROM mytable
  WHERE
  to_date(nullif(mydatetxt,''))  'ERROR HERE
  BETWEEN 
  dmin
  AND
  dmax
  </code></pre>
  
  <p>Please help to solve this situation finally.</p>
  
  <p>Workable code:</p>
  
  <pre><code>Using cmd As New NpgsqlCommand("SELECT my_id, mydate FROM " &amp; 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) &amp; "/" &amp; mydate.Substring(3, 2) &amp; "/" &amp; mydate.Substring(0, 2))
              If dat &lt; mindate Or mindate = Nothing Then
                  mindate = dat
              End If
  
              If dat &gt; maxddate Or maxdate = Nothing Then
                  maxdate = dat
              End If
  
              count += 1
          End If
      End While
  End Using
  End Using
  </code></pre>
  
  <p>SOLUTION:
  And this is finally very fast, improved version which Ervin kindly give:  </p>
  
  <pre><code>        Using cmd As New NpgsqlCommand( _
               "WITH base AS (" &amp; _
               "  SELECT TO_DATE(datum, 'DD.MM.YYYY') AS the_date " &amp; _
               "  FROM " &amp; myKalkTable &amp; " " &amp; _
               "  WHERE datum &lt;&gt; '') " &amp; _
               "  SELECT MIN(the_date) AS dmin, " &amp; _
               "         MAX(the_date) AS dmax, " &amp; _
               "         COUNT(*) AS ct_incl, " &amp; _
               "  (SELECT COUNT(*) " &amp; _
               "         FROM base b1 " &amp; _
               "         WHERE(b1.the_date &lt; max(b.the_date)) " &amp; _
               "         AND b1.the_date &gt; min(b.the_date)) " &amp; _
               "         AS ct_excl " &amp; _
               "         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
  </code></pre>