Selecting Previous Year ??

[kevinfwb]

I will try to explain this as best as I can.  I don't really have any code to begin with because I honestly don't know where to begin. 

 

I have a database that has sales data for several restaurants.  I have an php page that queries the database and shows the given data. 

 

SELECT unitNum, Period, Week, Year, Sales FROM SalesData;

 

echo results.  This part works find but I'm trying to find a way to find the increase/decrease in sales from previous year where the period and week are the same.  Example echo results

 

Store  Period    Week  Year    Sales     

252        5          2      2006  $32,000

 

I want to pull the sales data for store 252, period 5, week 2, year 2005

and calculate the difference.  I would like to see

 

252    5    2    2006  32,000  28,000 (same period, week, previous year)

 

 

I tried doing a subquery within while($row = mysql_fetch_array($result, MYSQL_ASSOC))

but that just spits out errors.... 99% chance because I"m not sure what I'm doing

 

hopefully that made some logical sense.

 

Thanks

 

-Kevin

[emehrkay]

why cant you just write a query to handle that?

 

SELECT [fields] FROM SalesData WHERE year = AND period = .....

[kevinfwb]

I probably could.. but it's not quite that simple (at least for me)

 

In one query I need this years sales and last years sales.

 

SELECT storeNum, period, week, year, sales, sales (year-1) FROM salesData

I need to pull the sales from the prior year for the same store, period, week

 

Does that clarify at all?

[kevinfwb]

Here is the actual code that I"m working with.

 

$query = "SELECT StoreNum, Franchisee, RFC, Month, Day, Year, Period, Week, Sales FROM StoreSales ORDER BY Franchisee, Year, Period, Week";


$result = mysql_query($query);
if (!$result) {
        echo ("<P>Error performing query: " .
                mysql_error() . "</P>");
        exit();
        }

echo "<center><table border='1' cellpading='4' cellspacing='0'>";
echo "<tr> <th> Store #</th> <th> Franchisee </th> <th>Year</th> <th> Period </th> <th> Week </th> <th> Sales </th> </tr>";

$color1 = "#CCCCCC";
$color2 = "#B9B9B9";
$row_count = 0;

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{


$row_color = ($row_count % 2) ? $color1 : $color2;
                echo "<tr><td bgcolor='$row_color' nowrap>";
                echo "{$row['StoreNum']}";
                echo "</td><td bgcolor='$row_color' nowrap>";
                echo "{$row['Franchisee']}";
                echo "</td><td bgcolor='$row_color' nowrap>";
                echo "{$row['Year']}";
                echo "</td><td bgcolor='$row_color' nowrap>";
                echo "{$row['Period']}";
                echo "</td><td bgcolor='$row_color' nowrap>";
                echo "{$row['Week']}";
                echo "</td><td align='right' bgcolor='$row_color' nowrap>";
                echo "{$row['Sales']}";
                echo "</td></tr>";
        $row_count++;
}

echo "</table></center>";
?>

         

 

I want another line at the end of the echo statements to show the previous years sales and the increase/decrease.

 

Sorry for the confusion

 

-Kevin

 

[Jessica]

$sql = "SELECT storeNum, period, week, year, sales, sales, FROM salesData WHERE year = '".date("Y")."' OR year = '".(date("Y")-1)."'";

 

(I think my quotes are all matching )

[kevinfwb]

with that code would I just add another  echo "$row['Sales']"; ?

 

Does your code show the sales from the previous year in the initial query or the previous year from today?

[Jessica]

Your original code just gets everything, it doesn't specify a year, week, etc. You need to adapt it to do what you want.

 

If you want all the records from this year and last, my query will get that. If you want all the records from today, this year and last, you need to add more specifics.

[kevinfwb]

Sorry.. I'm probably not being very clear on exactly what I am looking for.

 

The query is to return all records from all fields.  But within the results of each row i need it to show the previous years sales as well with all the other data being the same.

 

Will display a bunch of results but I need a 2nd Sales figure to reflect the same unitNum, period, and week but the sales 1 year prior than the current record.

 

SELECT * FROM salesData;

 

          UnitNum, Period, Week, Year,    Sales

Returns  1234,        5,      3,    2004, $45,000

 

Then I need

 

SELECT sales FROM salesData WHERE unitNum=1234, Period=5, Week=3, year=2003(previous year minus 1)

Returns:  $40,000

 

I want my actual output to be

 

1234 - 4 - 3- 2004 - 45,000 - 40,000

 

Does that clarify what I"m looking for at all?

[Jessica]

so ORDER BY unitNum ASC, year ASC

 

Then just play with your HTML formatting till you get it to work dude.

[kevinfwb]

HTML formatting won't work because I need to do calculations between the 2 years of sales figures...

 

I was hoping something like

 

SELECT StoreNum, Period, Week, Sales, Sales AS PriorSales FROM StoreSales WHERE PriorSales=(Sales-1)

 

but that throws an error that PriorSales is unknown.

 

I'll play around with it.. Thanks.

 

-Kevin

[Jessica]

So do the calculations in PHP.

 

Now you're trying to subtract one from sales, before it was year. You're not being very clear on what you're trying to do.

Select all the data for the stores, then do your math and formatting in PHP.

[kevinfwb]

My apologies.. I'm getting flustered.  Yes it is year-1.. NOT sales.

 

Doing the calculations in PHP is what I plan on doing.  I just can't seem to figure out how to get the sales data from the previous year based on the output from the query.

 

I really do appreciate your help.

 

-Kevin

[Jessica]

Get ALL of the data where the year is this one, or the previous, like I posted. Sort it the way I posted. This will make it easiest to order in PHP.

[kevinfwb]

But that won't show me results for 2003 with the increase/decrease over 2002... does that make sense at all?

 

I'd love to show you the actual table that I'm working with, but confidentiality prevents me from showing sales figures for the restaurants.

 

The php page doesn't just show this years sales or last years.  It shows all years as well as the sales of the prior year in each row.

[Jessica]

No, that doesn't make sense, because yes it will.

 

If you select EVERYTHING for the year 2003 AND for the year 2002, then IN PHP you take the figures from 2003 and the figures from 2002 and calculate the increase and decrease.

 

If you need to keep doing this, then just select all of it and us php to loop through the years.

Then, once you've got your newly created arrays full of data with increases/decreases, print the HTML.

[kevinfwb]

I made up a crude example of what I'm trying to do with PHP/MySQL

 

Again, I thank you for your help.

 

-Kevin

 

http://68.202.148.23/Example.htm

[Jessica]

Yes, I KNOW what you're trying to do.

If you want me to actually write out the entire code, send me the file and $30. Otherwise you're going to have to use what I've said over and over and write it yourself, as I can't explain it anymore.

Best of luck.