move table due in 10 days time

[I-AM-OBODO]

Hi all.

I have an issue and don't know how to go about it. I have a table that contains user orders. I want to move only orders that will be due in 10 days time to another table. How can I achieve that?

 

thanks

[requinix]

Don't do that - have multiple tables for the same thing. The big clue that it's a bad idea is that you have to run some automated thing to manage your database.

 

Any particular reason you want them in another table? Is there a problem with just querying the table directly?

[I-AM-OBODO]

The reason to move to another table is for easy reference. so that I'll create a link that will contain only transaction to come up in 10 days time. that's what the client wants.

 

thanks

[Frank_b]

SELECT * FROM orders WHERE DATEDIFF( duedate, CURRENT_DATE()) <= 10

That is enough to show only orders that are equal or less to 10 days before the column duedate.

 

Like requinix said: Do not move them to another table. That is bad practise.

 

ps. i assumed that you have a duedate column with the type DATE or DATETIME.

Edited October 14, 2014 by Frank_b

[I-AM-OBODO]

Thanks and yes I do.

[ginerjm]

Hopefully you have realized that good database organization is how you properly manage any and all data.  By having it well designed you are then able to write queries to answer any question at any time without having to do things like you proposed in your post.  No need to move data to another table, no need to remove data when it 'expires'.  You simply store your data and query it and format the output to answer any question that is asked.  Yes - you eventually may remove data that is beyond use, but I suggest that you develop a process to archive the old data so that you can analyze it over long periods of time if that is the nature of your activity/business.

[requinix]

If the client is at all technical, like they want to query the database directly, then you can make a view of whatever the appropriate query is. It will look like a regular table for the most part.

[I-AM-OBODO]

Thanks all. Sometimes clients just want to have it there way even when you try to discourage them of bad practices.

 

Thanks very much. will keep your advise in mind.

[requinix]

Thanks all. Sometimes clients just want to have it there way even when you try to discourage them of bad practices.

And part of your job is to teach them why it's bad.