On Sun, Aug 31, 2014 at 11:02 PM, jic <cabr...@wrc.xerox.com> 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 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é
You'll likely get some better responses when other in the US and Canada (not meaning to disparage the rest of the world) come back from Labor Day weekend. Your select looks quite good to me. However, if I were doing this, I think it might be better to use a CTE. This is rather new in SQLite. Ref: http://www.sqlite.org/lang_with.html WITH tempTable AS ( SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM LSOpenProjects ) SELECT cust, sum(ProjFund), sum(Xtra8), t FROM tempTable WHERE t BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY SUBSTR(t,1,7), cust; Now, I think you can emulate this in an older version of SQLite by using a subquery. Perhaps something like (and you'll notice the similarity to the above) SELECT cust, sum(ProjFund), sum(Xtra8), t FROM ( SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) AS t FROM LSOpenProjects ) AS tempTable WHERE t BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY SUBSTR(t,1,7), cust; the AS tempTable isn't really needed, I just put it in there because I can, and to help document the relationship with the CTE example. I will also point out that the above may be acceptable to SQLite but likely will fail when used in a data base which exactly conforms to the SQL standard. I am fairly sure that a compliant SQL system will complain that the variable "t" is not the object of an aggregate function and is not in the GROUP BY clause. You are grouping by a temporary variable: SUBSTR(t,1,7) and not the entire variable t. To me, it seems you are grouping by the "yyyy-mm" portion of the date. Perhaps you should use the same SUBSTR(t,1,7) in the upper level SELECT as well? That would satisfy the SQL standard. Oh well, I'm no expert and may be wrong on this. Unfortunately, I do not have any data to test the above to try to fix any problems such as syntax errors. Or even to verify that I'm even close to correct. But I do hope that it was of some help to you. -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
