MySQL head scramble

jabfinger · 07-28-2005, 10:28 AM

Hi,

I have data in a table like this:

(where week2 Sold is not the actual amount of week2
Sold but the acumulation of week1 + week2, and
the actual amount of week3 is a acumulation of
week2 + week3 Sold)

ID Name Week Sold
----------------------------
1 Paul week1 120
2 Fran week1 105
3 Paul week2 245
4 Fran week2 250
5 Paul week3 375
6 Fran week3 380

IF for Paul, I subtract:

week1 Sold(120) - week0 Sold( 0 ) = week1 Sold(120)
week2 Sold(245) - week1 Sold(120) = week2 Sold(125)
week3 Sold(375) - week2 Sold(245) = week3 Sold(130)

If for Fran, I subtract:

week1 Sold(105) - week0 Sold( 0 ) = week1 (105)
week2 Sold(250) - week1 Sold(105) = week2 (145)
week3 Sold(380) - week2 Sold(250) = week3 (130)

I need data like this:

Name Week Sold
--------------------
Paul week1 120
Paul week2 125
Paul week3 130

Fran week1 105
Fran week2 145
Fran week3 130

So basicaly, I think, I have a YTD table
that shows weekly YTD data, and I need to work
backwards to find the weekly amounts that
people Sold.

If I could get the data in the form of weekly
totals it would make my life a lot easier,
but I can not.

I have bought a few books on MySQL and php, and have
read a gazillion tutorials on the web and I havn't
been able to figure this out on my own.
Im not asking for anyone to write the code, but
If someone could point me in the right direction
I would surely be grateful

nouse66 · 07-28-2005, 11:09 AM

i'm pretty sure i understand your problem...
what you need to do is loop through the weeks in order and keep track of the previous week so you can subtract. assuming you have an open mysql connection $db, i would do something like this for each person in the table:

PHP Code:



$result = mysql_query("select * from weekly where name='Fran' order by week",$db); 
$lastweek = 0; 
$sold = array(); 
while($row = mysql_fetch_object($result)) { 
    $sold[$row->week] = $row->sold - $lastweek; 
    $lastweek = $row->sold; 
} 
 
print_r($sold);

you should get 105,145, and 130 in the $sold array.

deiussum · 07-28-2005, 11:31 AM

You might be able to do some joins to get the data in the format you want directly in the SQL query too. This is just off the top of my head and totally untested, but might give you some ideas...

Code:

SELECT T1.Name, T1.Week, T1.Sold - T2.Sold as Sold
FROM Weekly T1
LEFT OUTER JOIN Weekly T2
ON T1.Name= T2.Name AND T2.Week= T1.Week - 1
WHERE T1.Name = 'Paul'
ORDER BY T1.Week

If the week is in a numeric format that you can subtract 1 from, something like the above should work. If it is really in a string form like "week1", then you might have to substitute my subtraction with some creative substring type stuff...

nouse66 · 07-28-2005, 11:58 AM

yeah, having a string for the week field really is the kicker. i went quickly to php because trying to think through the substrings in that join was making my head spin

jabfinger · 07-28-2005, 12:29 PM

Thanks Guys,

The week is actualy a timestamp in the real table.
I should have mentioned that.
I will attempt your suggestions.