Sorting by month with two dates input

classic Classic list List threaded Threaded
7 messages Options
jic
Reply | Threaded
Open this post in threaded view
|

Sorting by month with two dates input

jic

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by month with two dates input

John McKown
On Sun, Aug 31, 2014 at 11:02 PM, jic <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by month with two dates input

Igor Tandetnik-2
In reply to this post by jic
On 9/1/2014 12:02 AM, jic wrote:
> SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t
>
> 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|

How is "absence of billdate" represented in the database - as NULL or as
empty string? I suspect it's the latter. coalesce() only treats nulls as
"special", not empty strings. See how the answer changes if you replace
coalesce(billdate,bdate) with

(case when billdate != '' then billdate else bdate end)

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by month with two dates input

Petite Abeille-2

On Sep 1, 2014, at 6:00 PM, Igor Tandetnik <[hidden email]> wrote:

> (case when billdate != '' then billdate else bdate end)

Or, more succinctly:

coalesce( nullif( billdate, ‘’ ), bdate )

(To OP: empty strings are E V I L. Don’t use them. Ever.)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by month with two dates input

Keith Medcalf

>On Sep 1, 2014, at 6:00 PM, Igor Tandetnik <[hidden email]> wrote:
>
>> (case when billdate != '' then billdate else bdate end)
>
>Or, more succinctly:
>
>coalesce( nullif( billdate, '' ), bdate )
>
>(To OP: empty strings are E V I L. Don't use them. Ever.)

Unless of course it is one or more spaces and not an empty string ...

coalesce(nullif(rtrim(billdate), ''), bdate)

an empty string is indistinguishable from a string of one or more spaces unless one makes efforts to tell the difference.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by month with two dates input

Petite Abeille-2

On Sep 1, 2014, at 9:19 PM, Keith Medcalf <[hidden email]> wrote:

>
>> On Sep 1, 2014, at 6:00 PM, Igor Tandetnik <[hidden email]> wrote:
>>
>>> (case when billdate != '' then billdate else bdate end)
>>
>> Or, more succinctly:
>>
>> coalesce( nullif( billdate, '' ), bdate )
>>
>> (To OP: empty strings are E V I L. Don't use them. Ever.)
>
> Unless of course it is one or more spaces and not an empty string ...
>
> coalesce(nullif(rtrim(billdate), ''), bdate)
>
> an empty string is indistinguishable from a string of one or more spaces unless one makes efforts to tell the difference.

(To OP: if this is the case, you get what you deserve! :D )
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by month with two dates input

jose isaias cabrera
In reply to this post by Igor Tandetnik-2
"Igor Tandetnik" wrote...

> On 9/1/2014 12:02 AM, jic wrote:
>> SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t
>>
>> 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|
>
> How is "absence of billdate" represented in the database - as NULL or as
> empty string? I suspect it's the latter. coalesce() only treats nulls as
> "special", not empty strings. See how the answer changes if you replace
> coalesce(billdate,bdate) with
>
> (case when billdate != '' then billdate else bdate end)
Yep, you're right.  It's the NULL value that is not working.  Your
suggestion works.  But, I have to test the various results to make sure that
all deliver the same.  Thanks..

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users