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. 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. This is what I am trying... 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; 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| Co02|12280.0|110.0| Co03|550.0|0.0| Co04|1317.49|285.72| Co05|747.0|0.0| Co05|187330.3|39076.85| Co07|14148.39|0.0| Co08|156011.68|6725.09| Co09|10890.92|142.98| Co10|333.34|0.0| Co11|294.39|214.29| Co12|14857.29|0.0| Co02|402.16|522.28|2012-02-15 Co04|3506.5|3976.11|2013-01-15 Co05|270.29|351.03|2013-01-15 Co06|1273.17|0.0|2013-01-15 Co10|16337.75|21217.83|2013-01-15 but there should always be a date. Any help would be greatly appreciated. Thanks. josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users