Can't figure out query for determining ages

nugod777

Hello all. I am hoping someone can help with a problem I am having. I need to pull all work orders from a table (which has a receipt date and a complete date) that were 45 days old during a given month and year. I have tried a couple of different things but I cannot wrap my head around this one.
My last attempt looks like this :

SELECT * FROM `table` WHERE 
complete IS NULL AND DATE_ADD(receipt,INTERVAL 45 DAY) BETWEEN  '$xyear-$xmonth-01' AND '$xyear-$xmonth-$xday' 
OR complete > DATE_ADD(receipt,INTERVAL 45 DAY) AND
DATE_ADD(receipt,INTERVAL 45 DAY) BETWEEN '$xyear-$xmonth-01' AND '$xyear-$xmonth-$xday'

$xyear and $xmonth are inputs from a form and $xday is calculated on the fly depending on the days in the given month. This almost does what I want but it only pulls records that are exactly 45 days old. I need 45 and older.

Any help is greatly appreciated.

-RHJ

Norman_Graham

I think you want the MySQL function TO-DAYS():

SELECT * FROM mytable WHERE TO_DAYS(NOW()) - TO_DAYS(receipt) >= 45;

Adjust as necessary. The TO_DAYS() function counts the number of days from the day Jesus Christ our Lord and Saviour was spotted walking on the waters of the Sea of Gallilee to the day stipulated in the query, give or take a few decades. It works with all date/datetime/timestamp column types. Check the manual for precise details.

Best o luck

Norm

nugod777

I agree that TO_DAYS is great for determining ages as I do use it for almost all of my age calculations... but this one is different. TO_DAYS requires a day to calculate from. I need to calculate these ages from a given month and year for anytime in the past. Calculating age from NOW() or current_date is easy but won't work in this situation. Basically I need to pull all work orders that were over 45 days old from the receipt date back in X month of X year.

Thanks for the suggestion tho.

Norman_Graham

I'm afraid I don't understand the problem then - you need to know which work orders are 45 days old and more, but you don't know which day of a given month the work orders were received? How is this going to work? Presumably that means that the date of receipt of a work order could be incorrect by up to 30 days.

Sorry not to be more helpful

Norm

nugod777

I apologize for being confusing... heh that could be why I can't figure this one out. =)

If a user selects March 2003 on a form I need to basically generate a list of work orders that were over 45 days old from their receipt date at any time during the month of March, 2003. So if a work order was 45 days old on March 1st, 2003 it would show up, a dirrerent work order became 45 days old on the 15th it would also show up.

Maybe I am looking at it the wrong way... maybe I should go by the last day of the month for any month in the past and for the current month (if chosen on the form) I would use current_date or NOW() along with TO_DAYS.

I hope I was a little clearer this time but like I said... I am having trouble wrapping my head around this one. I appreciate your time and help!!

Thanks,
RHJ

Norman_Graham

... you just said you don't know what day a work order was received, only which month it was received in, so how can you work out on what day it becomes 45 days old? It's not possible - you can only say which month it was received in and then say, OK, if we just assume it was received on the 1st day of the given month, which won't be true but have no choice, then it becomes 45 days old on about the 14th/15th of the following month.

Sorry - I'm still failing to understand how this calculation is supposed to work.

Norm 🙁

nugod777

I do have the specific day it was received. The problem is that the user wants to be able to pick a month and year and see the incomplete work orders that were over 45 days old in that month. My thinking now is that is will have to be work orders that were incomplete and 45 days old or older on the last day of the selected month if it is a month in the past and calculate age based on that last day of the month. If the current month/year is selected it will calculate based on current_date.
I apologize for presenting this in such a confusing manner and I really do appreciate you spending your time trying to help me answer this.

Thanks,
RHJ

jkohns

here's my stab at it:

SELECT * FROM table WHERE
(MONTH(complete)='$xMonth' && YEAR(complete)=$xYear &&
(TO_DAYS(complete) - TO_DAYS(receipt) >= 45))
OR
(complete IS NULL && (TO_DAYS('$xYear-$xMonth-31') - TO_DAYS(receipt) >= 45)
)

the first part of the WHERE statement catches completed orders who's receipt date were past 45 days previous during the selected month && year, the second part I think would do the trick on incomplete orders...it assumes that you are asking for orders in a range previous to the current month- therefore, complete would be NULL on the last day of the month...

Looks like it would work for me, but I didn't test it on a live table

nugod777

I am thinking it will have to be based on the last day of the month... it seems that it would be the only usable solution.

jkohns your query will almost do it... I have modified like so:

SELECT * FROM table WHERE
(MONTH(complete)>'$xMonth' && YEAR(complete)=$xYear &&
(TO_DAYS(complete) - TO_DAYS(receipt) >= 45))
OR
(complete IS NULL && (TO_DAYS('$xYear-$xMonth-$xDay') - TO_DAYS(receipt) >= 45)
)

$xDay will be calculated on the fly and be based on what month the user selects. I Changed the MONTH(complete)='$xMonth' to greater than (>) since this particular report will only deal with incomplete work for the given month/year.

I think this will do the trick.

Thanks to both Norm and jkohns.

-RHJ

jkohns

I dont' know if you actually have to go through the step of calculating the last day of the month...I use YYYY-MM-31 to represent the end of month in BETWEEN selections all the time, and it seems to be working fine...even if the 31st doesn't exist, the database simply treats it as a hypothetical date, but doesn't push the selection range to the next month...I may be wrong, but I haven't had it fail yet

nugod777

I know... but I'm the better safe than sorry type... and its such an easy thing to do that I don't mind.

$xday=date(t,mktime(0,0,0,$xmonth,1,$xyear));