Query database for missing dates (not sunday)

[maffp]

Hi!

I have a database of shop takings for each date in it's history which I update daily. However, sometimes I forget a day and don't pick it up till much later. I was wondering if there was a way to query a database for missing dates so that I can complete an entry for it... excluding sundays (we never open on sundays).

Cheers!

[Cups]

The easiest way is probably to select all shop, dates, where takings = 0 then in your php logic test the condition that day of week is not 7.

// emulating your results set from your SQL
$rows[] = array( "shop" => "City centre", "takings" => 0, "day"=>"2010-05-02" );
$rows[] = array( "shop" => "City centre", "takings" => 0, "day"=>"2010-05-03" );

foreach( $rows as $row){
if( // check the day is not number 7
( date( 'N' , strtotime( $row['day']) ) != 7 ) 
)
{
echo 'oops, missed one ... ' . $row['shop'] . ' on ' . $row['day'] ;
}
}

// oops, missed one ... City centre on 2010-05-03 

There may be a way to do it in your SQL select, but ask that question on one of the dedicated sql forums.

Best way though would be to automate the inputting of takings somehow ...

[maffp]

Hi Cups and cheers for the reply!

I should probably have given a little more detail before. Uncompleted dates will currently not exist in the table at all as they are added on a daily basis. The table layout is like this:

[CODE]
date | nettax | tax | cash | cheque | card
[CODE]

Would love to automate it but we only use an old cash register... not an epos system.

[Cups]

Right, so I think I misunderstood, you only have one shop?

You just want to know the dates where there is no row for takings (except sunday).

Now that does sound like it is begging an SQL solution ... which I am not qualified to answer.

You could probably cook up something with PHP to get say, missing days for a particular month.

[Cups]

http://www.sitepoint.com/forums/showthread.php?t=430007

Theres an old reply about a similar question ...

Then you'd need still need to filter out Sundays.

[r937]

thanks cups