This topic is locked

Need help from some MySQL experts...

1/17/2019 8:57:32 AM
PHPRunner General questions
DealerModules authorDevClub member

I am struggling to get to correct mysql result and was hoping someone can push me in the right direction...
I have an inventory received date (inv_date_received) and trying to run a query that would tell me a count of inventory that is over 1 year old.
I have tried Count with DateDiff but I am not having good results.
Anyone's input is greatly appreciated.
Thanks
Paul

HJB 1/17/2019

https://xlinesoft.com/phprunner/docs/query_designer.htm
WHERE clause (using ">" DATE rather than "=" 2004)
You can add where clause in the Filter column. If you need to add two or more conditions use Or columns.


GROUP BY clause (using COUNT in your case rather than seen AVG)
To add GROUP BY clause click the Group By button and select one of grouping function in Group By column.


Once done, you can proceed to SQL tab to see on how yours differs from the code generated as above.
Else you need to make good use of DEBUG function as per step-by-step tutorial as seen under:
https://xlinesoft.com/blog/2016/05/02/troubleshooting-sql-queries/

DealerModules authorDevClub member 1/17/2019

Will give it a try.

Thanks walk2fly

K
KevinMillican 1/22/2019

Something like this (assumes datetime field, else use CurDate() instead of NOW()) :-
SELECT

ID,

Item,

Received,

DateDiff(NOW(),Received) AS Elapsed,

IF(DateDiff(NOW(),Received)>365,1,0) AS Longer

FROM Inventory

DealerModules authorDevClub member 1/22/2019

Thanks Kevin, worked perfectly for what I was trying to achieve.

Paul