>>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;
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;
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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
