Need a query guru's help...

Not sure this is even possible in one query, but I thought I'd run it by the experts. I appreciate any help.

I have the following three tables:

Table: CurrentForecast
Fields: UserID
Fields: Sentiment
Fields: CurrentDow
Fields: CurrentNaz
Fields: CurrentSP

The CurrentForecast table stores the current forecast for each user. There is only one forecast record per user. For example, if there are 36 users, there will be only 36 CurrentForecast records.

Table: LastUpdated
Fields: lastupdated
Fields: CurrentDow
Fields: CurrentNaz
Fields: CurrentSP

The LastUpdated table stores the current value for the Dow, Nasdaq and S&P. There is only one record ever in this table. When the values of the indices change, this one record is updated.

Table: ForecastHistory
Fields: Sentiment
Fields: UserID
Fields: CurrentDow
Fields: CurrentNaz
Fields: CurrentSP

The ForecastHistory table stores previous forecasts. This table can have 0, 1 or many records for each user. This is a history file. When the user changes their sentiment, the current data is written to this table. It is a transaction log essentially.

Based on this information, here is the output I am looking for:

Sentiment---Number---History---Current
Bullish 10--175.12---987.45
Bearish 22--225.98---456.98
Neutral 4--125.28---345.76

The first column, "Sentiment" comes from the CurrentForecast table. This is a list of the distinct sentiment records. There can be at most 3 options. Bullish, Bearish or Neutral.

The second column in my result is "Number." This is the number of people that are Bullish, Bearish and Neutral. This data comes from the CurrentForecast table. If there are 36 total records, 10 are Bullish, 22 are Bearish and 4 are Neutral.

The third column is "History" which is the sum of the points contained in the ForecastHistory table. So lets say 10 people are Bullish based on the CurrentForecast table. I need to know what the total points are for those 10 people in the ForecastHistory table. I need the same calculation for the other 2 sentiment records.

The last column is "Current" which is the current points for each user. For example, the 10 Bullish people have a record in the CurrentForecast table. They are Bullish, but what was the price of the Dow, Nasdaq and S&P at that time? And what is the current price of the Dow?

Well, to calculate this I need to analyze what price they recommended the Dow at and compare it to the current price of the Dow. The current price of the Dow (and the Nasdaq and S&P) is stored in the LastUpdated table. So to determine how many points they are up or down, I need to do the following calculation:

If they are Bullish:

(LastUpdated.CurrentDow-CurrentForecast.CurrentDow).

If they are Bearish:

(CurrentForecast.CurrentDow-LastUpdated.CurrentDow).

If they are Neutral:

Don't do the calculation, because the current value = 0. They are on the sidelines essentially.

I really appreciate any help you guys can give me.

Thanks again.

Robert

I would suggest using an inner join on the currentforcast and forcasthistory tables to pull all the data for a single user at one time and pull the lastupdated data out of the db with a seperate query then perform the calculations using php outside of the querys. The reason I suggest this is that you have 'IF' logic built into the calculation model and it would be next to impossible to calculate within the query based on that calculation model. I believe this will be the easiest to handle these calcs.

I got the query almost working, but I got one problem left.

SELECT count( currentforecast.sentiment ) AS count, currentforecast.sentiment, IF ( currentforecast.sentiment = 'Bearish', SUM( currentforecast.currentdow - lastupdated.currentdow ) , IF ( currentforecast.sentiment = 'Bullish', SUM( lastupdated.currentdow - currentforecast.currentdow ) , 0 ) ) AS DowChange, IF (
currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentnaz - lastupdated.currentnaz ) * 6 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( (lastupdated.currentnaz - currentforecast.currentnaz ) * 6 ) , 0 ) ) AS NazChange, IF ( currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentsp - lastupdated.currentsp ) * 9 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( ( lastupdated.currentsp - currentforecast.currentsp ) * 9 ) , 0 ) ) AS SPChange FROM currentforecast, lastupdated GROUP BY currentforecast.sentiment

The only thing that is missing from this query is forecasthistory total. When I attempt to add this part by using a SUM on the forecasthistory.points column with a LEFT OUTER JOIN related to the userid columns in the CurrentForecast and ForecastHistory tables, the calculations get all messed up. The best I can figure is that the correct amounts for the DowChange, NazChange and SPChange are multiplied by the number of records found in the forecasthistory table.

Here's what I am attempting:

SELECT count( currentforecast.sentiment ) AS count, currentforecast.sentiment, IF ( currentforecast.sentiment = 'Bearish', SUM( currentforecast.currentdow - lastupdated.currentdow ) , IF ( currentforecast.sentiment = 'Bullish', SUM( lastupdated.currentdow - currentforecast.currentdow ) , 0 ) ) AS DowChange, IF (
currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentnaz - lastupdated.currentnaz ) * 6 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( (lastupdated.currentnaz - currentforecast.currentnaz ) * 6 ) , 0 ) ) AS NazChange, IF ( currentforecast.sentiment = 'Bearish', SUM( ( currentforecast.currentsp - lastupdated.currentsp ) * 9 ) , IF ( currentforecast.sentiment = 'Bullish', SUM( ( lastupdated.currentsp - currentforecast.currentsp ) * 9 ) , 0 ) ) AS SPChange, SUM(forecasthistory.points) as totpoints FROM currentforecast LEFT OUTER JOIN forecasthistory ON currentforecast.userid = forecasthistory.userid, lastupdated GROUP BY currentforecast.sentiment

Hope someone has the missing peice to this puzzle.

Again, thanks for the help!

I got it.