jay0316 Posted July 24, 2009 Share Posted July 24, 2009 Our sales reps are required to visit a store once per quarter. I have an application (4 fields with submit buttons next to them) that is storing 4 visit dates (one for each quarter) in 4 columns (data type = date with default value of 0000-00-00) in a mysql database. The problem comes in when they add a new store. Let's say the new store is added during the 3rd quarter. There are not going to be visit dates for the 1st and 2nd quarter. Is there a way I can handle this so that when we look at the columns in the database, we'll be able to tell whether they just didn't visit the store or the field didn't get a value because the store was new? Quote Link to comment Share on other sites More sharing options...
rhodesa Posted July 24, 2009 Share Posted July 24, 2009 create another column to track when the store was added Quote Link to comment Share on other sites More sharing options...
joel24 Posted July 24, 2009 Share Posted July 24, 2009 maybe have a seperate column creationDate? and then you can determine if it was created before that quarter or if they just didnt' visit... another option would be to make the previous quarters NULL if a store is created after the first quarter.. i.e. if its made in the 3rd quarter, first and second quarter values would be NULL and the third would have a date..? i think my first suggestion is the most practical tho edit: rhodesa beat me to it! wrote too much to delete the post tho... Quote Link to comment Share on other sites More sharing options...
jay0316 Posted July 24, 2009 Author Share Posted July 24, 2009 Hey guys, I do have a column storing when the store was added. The problem is we have to manually look at the column and compare the dates when we print out a spreadsheet of the data in the database. I'm thinking about putting in an extra column for each quarter that will have a number. 0 = store was not visited 1 = store was visited 2 = store is new Then when a new store is added I could just add a 2 to the extra column for the quarters that do not need a date. When calculating the total percentage for that quarters visits, I could subtract all the stores that have a 2 in the column from the total number of stores. That would give me an accurate percentage I believe. Does that sound like it would work? or is adding the extra columns making it harder than it is? I think it would be easier to look at 0s 1s and 2s when viewing the spreadsheet than trying to compare the dates and figure it out. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted July 24, 2009 Share Posted July 24, 2009 I would make the fields NULLABLE with a default of 0000-00-00. then, when adding a store, make sure any older quarters are NULL instead of 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.