Date Range Picker: not selecting correct records when used with Sum columns

QuestionsDate Range Picker: not selecting correct records when used with Sum columns
Patricia Lawson asked 8 years ago

Hello Abu,
I am trying to get the daterangepicker working.

This query works in phpmyadmin:
SELECT v.collection as 'collection', SUM(v.Quantity) as 'ordered', SUM(v.Invoiced) as 'shipped',(CAST(SUM(v.Invoiced) AS UNSIGNED)/CAST(SUM(v.Quantity) AS UNSIGNED)) AS 'Fullfilment Rate'
FROM salesorder o
JOIN itemsales v
ON o.txnid = v.idkey
WHERE o.TxnDate BETWEEN '2017-03-01' AND '2017-03-31'
Group BY v.collection

I use this query for the grid: (no date column)
SELECT v.collection as 'collection', SUM(v.Quantity) as 'ordered', SUM(v.Invoiced) as 'shipped',(CAST(SUM(v.Invoiced) AS UNSIGNED)/CAST(SUM(v.Quantity) AS UNSIGNED)) AS 'Fullfilment Rate'
FROM salesorder o
JOIN itemsales v
ON o.txnid = v.idkey
Group BY v.collection

My date column is this:
$col = array();
$col["title"] = "Date";
$col["name"] = "txndate";
$col["width"] = "200";
$col["formatter"] = "date";
$col["editable"] = true;
$col["stype"] = "daterange";
$cols[] = $col;

I see in Inspect that the dates ARE sent through correctly:
filters:{"groupOp":"AND","rules":[{"field":"txndate","op":"bt","data":"{"start":"2017-03-01","end":"2017-03-31"}"}]}

BUT the data is never filtered.

I do not use 'txndate, txndate as txndaate2' in the Select stmt because I am summing, and teh dates are not relevent on a row-level.

Any ideas?
Thanks,Pat

1 Answers
Abu Ghufran answered 8 years ago

You can try enabling mysql query log and see what actual query is being sent to server.
Difficult to tell the reason by this.

Your Answer

7 + 6 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?