"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 00:00:00'. -- Darren Duncan
No, they are just YYYY-MM-DD. I think that Igor hit the spot. It is NULL
vs. ''.
On 2014-09-01, 6:41 PM, jose isaias cabrera wrote:
"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 '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;
I'm surprised that even compiles, since AFAIK the SELECT clause is
evaluated
after the GROUP BY clause, and t doesn't exist yet at the GROUP BY
stage (unless
you have another one in LSOpenProjects).
Maybe try using a subquery to force correct evaluation order?
Kind of like this:
SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, 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'
) x GROUP BY substr(t,1,7), cust;
-- Darren Duncan
Actually, isn't this more what you want?
SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t
FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR
bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY t, cust;
This one also does the same thing as mine. Hmmm...
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
