Untested, but first whack at it;
select * from yourtables order by ifnull(billdate,bdate)
Criteria met:
- A date posted prioritizing billdate and then bdate
- Sorted based on date
On Mon, Sep 1, 2014 at 1:55 AM, Keith Medcalf <kmedc...@dessus.com> 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;
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
