sql - PostgreSQL, min, max and count of dates in range -

-

this question based of 2 previous here , here.

i trying hard 2 queries:

select min(to_date(nullif(mydatetext,''), 'dd.mm.yyyy')) dmin,        max(to_date(nullif(mydatetxt,''), 'dd.mm.yyyy')) dmax mytable

and

select count(*) mytable to_date(nullif(mydatetxt,''))  'error here between  max(to_date(nullif(mydatetxt,''), 'dd.mm.yyyy')) , min(to_date(nullif(mydatetxt,''), 'dd.mm.yyyy'))

in single 1 can read result minimal date, maximal date, count of dates between , including min , max dates. here few problems.

second query don't work expected or don't work @ have improved. if 2 queries can writen in single query (?) can use dmin , dmax variables first part variables in second part? this:

select count(*) mytable to_date(nullif(mydatetxt,''))  'error here between  dmin , dmax

please solve situation finally.

workable code:

using cmd new npgsqlcommand("select my_id, mydate " & mytable, conn) using dr npgsqldatareader = cmd.executereader()     while dr.read()         mydate = cstr(dr(1))          if isdate(mydate)             dim dat date = cdate(mydate.substring(6, 4) & "/" & mydate.substring(3, 2) & "/" & mydate.substring(0, 2))             if dat < mindate or mindate = nothing                 mindate = dat             end if              if dat > maxddate or maxdate = nothing                 maxdate = dat             end if              count += 1         end if     end while end using end using

solution: , fast, improved version ervin kindly give: 

        using cmd new npgsqlcommand( _              "with base (" & _              "  select to_date(datum, 'dd.mm.yyyy') the_date " & _              "  " & mykalktable & " " & _              "  datum <> '') " & _              "  select min(the_date) dmin, " & _              "         max(the_date) dmax, " & _              "         count(*) ct_incl, " & _              "  (select count(*) " & _              "         base b1 " & _              "         where(b1.the_date < max(b.the_date)) " & _              "         , b1.the_date > min(b.the_date)) " & _              "         ct_excl " & _              "         base b", conn)              using dr 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

given table (like should have provided):

create temp table tbl (    id        int primary key   ,mydatetxt text  );  insert 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 2nd time

the query should produce describe:

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

with base (    select to_date(mydatetxt, 'dd.mm.yyyy') the_date      tbl     mydatetxt <> ''  -- excludes null , ''    ) select min(the_date) dmin       ,max(the_date) dmax       ,count(*) ct_incl       ,(select count(*)           base b1          b1.the_date < max(b.the_date)         ,    b1.the_date > min(b.the_date)        ) ct_excl   base b

-> sqlfiddle demo

ctes require postgres 8.4 or later.
consider upgrade latest point release of 9.1, 9.1.9.


Comments

Post a Comment

Popular posts from this blog

Issues changing the value of an element in an array in matlab -

-
i'm trying create program sum n-1 , n-2 numbers create n , store value in array. currently, output (assuming numdisp > 2 ) num1 , num2 , 0 , 0 , 0 ... size of array correct, i'm having issues reassigning elements. num1 = input('input first integer of fibonacci sequence: '); num2 = input('input second integer of fibonacci sequence: '); numsdisp = input('how many integers display? --> '); list = zeros(numsdisp,1); list(1) = num1; list(2) = num2; if (numsdisp <=0) disp('value must integer greater zero.') elseif (numsdisp == 1) disp(num1) elseif (numsdisp == 2) disp(num1) disp(num2) else disp(num1) disp(num2) index = 3:numsdisp disp(list(index)) list(index) = list((index-1)) + list((index-2)); end end example input: 3 3 4 corresponding output: 3 3 0 0 you aren't printing out final result, intermediate steps. in for loop, printing list(index) updating value, rather updat...
Read more

php - MySQLi binding parameters in a prepared statement doesn't work unless inserted after "WHERE" -

-
i have php code pulls info database. uses prepared statement, works fine written below: <?php $n = '5'; if ($stmt = $connection->prepare("select title items id = ?")) { $stmt->bind_param("s", $n); $stmt->execute(); $stmt->bind_result($title); while ($stmt->fetch()) { echo $title; } $stmt->close(); } ?> and let's echo "batman". however, when move placeholder here: "select ? items id = 5" and change: $n = 'title'; instead of echoing "batman", echoes "title". not possible use placeholder select parameter? what have done this: select "title" items id = 5 you cant use prepared statement bindings identifiers, parameters.
Read more

vb.net - Alternative to the T-SQL AS keyword -

-
please see code below: imports system.data.sqlclient imports system.configuration public class form1 private _constring string private sub form1_load(byval sender object, byval e system.eventargs) handles me.load dim objdr sqldatareader dim objcommand sqlcommand dim objcon sqlconnection dim id integer try _constring = configurationmanager.connectionstrings("testconnection").tostring objcon = new sqlconnection(_constring) objcommand = new sqlcommand("select person.urn, car.urn person inner join car on person.urn = car.urn , personid=1") objcommand.connection = objcon objcon.open() objdr = objcommand.executereader(connectionstate.closed) while objdr.read id = objdr("urn") 'line 19 loop objdr.close() ...
Read more