Can you select future dates that do not yet exist in table?
27/12/2017 11:47
I have a program I'm working on that does has a graph to show what isthe future estimate of production of eggs from a bunch of differentfarmers.

The table I have is this: CREATE TABLE `henbatch` ( `henBatch_ID` int(11) NOT NULL auto_increment, `Barn_ID` int(11) NOT NULL, `Quantity` int(11) NOT NULL, `StartDate` date default NULL, `SlaughteredDate` date default NULL, `StartDateSet` tinyint(1) default NULL, `Received` tinyint(1) NOT NULL, PRIMARY KEY (`henBatch_ID`)) StartdateSet is there because of something that I was thinking shouldwork, but don't know anymore if it will.

What I need to do is to select from this table for future entries thatmight not yet have been entered. I need to group weekly or maybemonthly the estimated sum of hens that are still in production. Inanother table the barn has the lifetime for the batch of hens. Cansomeone give me a suggestion on how to do this? Can this be done justwith a select statement from this table? Any questions, please ask.

- It's hard to explain.

Thanks

Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
27/12/2017 11:47 - Right -- don't underestimate the usefulness of a calendar table.

Just create a table that has every date in it for a reasonable range.

You can then start with this table, outer join your other tables, andget a row for every date -- even if there's nothing in your henbatchtable.

You may find it to be useful for other things, too -- in ourapplication we use it to keep track of business days and holidays sowe know when to skip processing for a day.


Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
27/12/2017 11:47 - Thank you strawberry and ZeldorBlat. I realized last night I wouldprobably have to create the dates table. I now have that table, butI'm not quite sure yet how to query these tables with a group by sothat it will give me the proper result.

Say I have daily entries in the dates table, and I want to get aforcast for the next 2 years, grouped by month, how do I prevent onehen batch from summing up multiple times per month? Or should I addanother column to the dates table to specify that these are weekly,daily and monthly dates? Thanks

Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
27/12/2017 11:47 - Just add each date one at a time for the next millenia.... or followthis thread... http://datacharmer.blogspot.com/2006_06_01_archive.html

Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
27/12/2017 11:47 - If they haven't been entered, how can you select them? SELECT only returns what's in the database.


Source is Usenet: comp.databases.mysql
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below