Re: [sqlite] Sorting by month with two dates input

update LSOpenProjects set billdate = null where len(rtrim(billdate)) < 10; >Thanks for this, but how do I set a value to null? I thought null and '' >were the same, but now I see it is not. '' means that "the value is an empty string" whereas null means there is no value/the value is

Re: [sqlite] Sorting by month with two dates input

"Igor Tandetnik" wrote... On 9/1/2014 12:02 AM, jic wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t But this is not working correctly, as the beginning of the result, there is no number because billdate is empty. So, I get a list like this:

Re: [sqlite] Sorting by month with two dates input

"Darren Duncan" wrote... On 2014-09-01, 6:50 PM, jose isaias cabrera wrote: Thanks for this, but how do I set a value to null? insert into foo (myfield) values (null); That's one way. If you're using some wrapper API, then the host language's analagy of an undefined value, eg undef in

Re: [sqlite] Sorting by month with two dates input

"Darren Duncan" wrote... A common logical error that may be affecting you is, do your dates include a time portion or are they just year-month-day? If they include a time portion, then records from Dec 31 likely won't be counted as your 'between' arguments may be equivalent to '2013-12-31

Re: [sqlite] Sorting by month with two dates input

On 2014-09-01, 6:50 PM, jose isaias cabrera wrote: Thanks for this, but how do I set a value to null? insert into foo (myfield) values (null); That's one way. If you're using some wrapper API, then the host language's analagy of an undefined value, eg undef in Perl, should do it. I

Re: [sqlite] Sorting by month with two dates input

A common logical error that may be affecting you is, do your dates include a time portion or are they just year-month-day? If they include a time portion, then records from Dec 31 likely won't be counted as your 'between' arguments may be equivalent to '2013-12-31 00:00:00'. -- Darren Duncan

Re: [sqlite] Sorting by month with two dates input

"Stephen Chrzanowski" wrote... Untested, but first whack at it; select * from yourtables order by ifnull(billdate,bdate) Criteria met: - A date posted prioritizing billdate and then bdate - Sorted based on date yeah, I had tried that before and no cigar... :-) Thanks, though. josé

Re: [sqlite] Sorting by month with two dates input

"Keith Medcalf" wrote... I have to create some reporting and I need to report on customers amount1 and amount2 two based on the whole year of 2013. There are two dates that are to be used as input: billdate and bdate. I can do one or the other, but, I can not seem to get the correct date

Re: [sqlite] Sorting by month with two dates input

"Darren Duncan" wrote... On 2014-08-31, 9:35 PM, Darren Duncan wrote: On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN

Re: [sqlite] Sorting by month with two dates input

On Sep 1, 2014, at 9:19 PM, Keith Medcalf wrote: > >> On Sep 1, 2014, at 6:00 PM, Igor Tandetnik wrote: >> >>> (case when billdate != '' then billdate else bdate end) >> >> Or, more succinctly: >> >> coalesce( nullif( billdate, '' ), bdate ) >> >>

Re: [sqlite] Sorting by month with two dates input

>On Sep 1, 2014, at 6:00 PM, Igor Tandetnik wrote: > >> (case when billdate != '' then billdate else bdate end) > >Or, more succinctly: > >coalesce( nullif( billdate, '' ), bdate ) > >(To OP: empty strings are E V I L. Don't use them. Ever.) Unless of course it is one or

Re: [sqlite] Sorting by month with two dates input

On Sep 1, 2014, at 6:00 PM, Igor Tandetnik wrote: > (case when billdate != '' then billdate else bdate end) Or, more succinctly: coalesce( nullif( billdate, ‘’ ), bdate ) (To OP: empty strings are E V I L. Don’t use them. Ever.)

Re: [sqlite] Sorting by month with two dates input

On 9/1/2014 12:02 AM, jic wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t But this is not working correctly, as the beginning of the result, there is no number because billdate is empty. So, I get a list like this: Co01|13016.16|20024.46| How is "absence of

Re: [sqlite] Sorting by month with two dates input

On Sun, Aug 31, 2014 at 11:02 PM, jic wrote: > > Greetings! > > I have to create some reporting and I need to report on customers amount1 > and amount2 two based on the whole year of 2013. There are two dates that > are to be used as input: billdate and bdate. I can do

[sqlite] Sorting by month with two dates input

Greetings! I have to create some reporting and I need to report on customers amount1 and amount2 two based on the whole year of 2013. There are two dates that are to be used as input: billdate and bdate. I can do one or the other, but, I can not seem to get the correct date when using

Re: [sqlite] Sorting by month with two dates input

Untested, but first whack at it; select * from yourtables order by ifnull(billdate,bdate) Criteria met: - A date posted prioritizing billdate and then bdate - Sorted based on date On Mon, Sep 1, 2014 at 1:55 AM, Keith Medcalf wrote: > > >>I have to create some reporting

Re: [sqlite] Sorting by month with two dates input

>>I have to create some reporting and I need to report on customers amount1 >>and amount2 two based on the whole year of 2013. There are two dates that >>are to be used as input: billdate and bdate. I can do one or the other, >>but, I can not seem to get the correct date when using both. The

Re: [sqlite] Sorting by month with two dates input

On 2014-08-31, 9:35 PM, Darren Duncan wrote: On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY

Re: [sqlite] Sorting by month with two dates input

On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust; I'm surprised that even

[sqlite] Sorting by month with two dates input

Greetings! I have to create some reporting and I need to report on customers amount1 and amount2 two based on the whole year of 2013. There are two dates that are to be used as input: billdate and bdate. I can do one or the other, but, I can not seem to get the correct date when using both.