"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 when using both. The
criteria
is that if there is a billdate, that overides the bdate, so the billdate
should be the one used and reported on. If there is no billdate, then,
bdate is used. There is always a bdate as this is the date the project
was
opened, so all projects have a bdate. But, not all projects may have a
billdate.
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;
is an improper interpretation of the problem statement.
Based on the problem statement I should think that:
SELECT cust, sum(ProjFund), sum(Xtra8), reportdate
FROM (SELECT cust, ProjFund, Xtra8, coalesce(billdate, bdate) as
reportdate
FROM LSOpenProjects
WHERE strftime('%Y', coalese(billdate, bdate)) = '2013'
) as X
GROUP BY strftime('%m', reportdate), cust;
This one took a long time and did not return anything.
or, equivalently:
SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate, bdate) as
reportdate
FROM LSOpenProjects
WHERE strftime('%Y', coalese(billdate, bdate)) = '2013'
GROUP BY strftime('%m', coalese(billdate, bdate)), cust;
This one, is looking like a winner. Thanks.
is correct.
Proper operation requires that the billdate be NULL or contain a date. If
it was set to an empty or blank string rather than null, then of course
the coalesce function will still return the empty or blank string that was
stored there rather than the bdate. If the data store is corrupted in
this fashion, then you need to fix it first, and whatever applications
caused the corruption of the data in the first place.
Alternatively you can fix such ill-conceived database contents/design by
replacing each use of
coalesce(billdate, bdate) -> CASE WHEN billdate is null or
length(rtrim(billdate) < 10)) THEN bdate ELSE billdate END
in which case fixing the broken application(s) and database design will
not be required.
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.
thanks.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
