Query to total up monthly projections

[marcoose]

Hi,

I have 3 tables :
Customers (a)
Product Categories (b)
Monthly Projections (c)

There can be more than one projection made for each category per month (for each customer).

Firstly, I would like to take the most recent monthly projection per category per customer. This can be done via a date field however how do I get the last monthly projection made for a category (for each customer)? NB I have tried using "Max (c.DateofProjection)" but I get all the projections made, not the latest in a given category (for a given customer).

Currently the query that is not working looks like :

SELECT a.CustomerName,b.CategoryName,c.MonthlyProjection, max(c.DateofProjection)

Secondly, I would like to total the monthly projections to reach an annual projection.

Is there an efficient way of doing this ?

Thanks in advance.

 

[tlbroadbent]

What are the relationships between the tables? Can you post the rest of the query, showing the JOIN attributes? It will help if you show us the structure of the tables.

Thanks, Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[marcoose]

Sorry,

The query looks like :

SELECT a.CustomerID, b.CategoryID, c.Projected_Month, c.Projected_Amount, c.Date_Projection_Made
FROM b INNER JOIN (a INNER JOIN c ON a.CustomerID = c.CustomerID) ON b.CategoryID = c.CategoryID
Group By a.CustomerID, b.CategoryID, c.Projected_Month, c.Projected_Amount, c.Date_Projection_Made;

The three tables are joined like :

Customer to MonthlyProjection (on field CustomerID)
Category to MonthlyProjection (on field CategoryID)

Both are One to Many relationships

Thanks

 

[tlbroadbent]

If I understand the tables structures correctly, you can get everything you need from the MonthlyProjection table. Here is a suggested query.

[tt]Select a.CustomerID, a.CategoryID, a.Date_Projection_Made, a.Projected_Month, a.Projected_Amount
From MonthlyProjection As a
Inner Join (Select CustomerID, CategoryID, max(Date_Projection_Made) As LatestDate
From MonthlyProjection
Group By CustomerID, CategoryID) As b
On a.CustomerID=b.CustomerID
And a.CategoryID=b.CategoryID[/tt]

Let me know if I've missed something. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[marcoose]

In response to your last post :

The problem is that I would like to pick up some other fields in the query e.g. Customer Name (which sits on the Customers table), Category Name (which sits on the category table). This means I have to take fields from the three tables (as indicated in the full query shown above). If I was just using the ID fields then I could take everything from the monthly projection field.

Any thoughts ?
Mark

 

[tlbroadbent]

Select a.CustomerID, a.CategoryID, a.Date_Projection_Made, a.Projected_Month, a.Projected_Amount, c.CustomerName, d.CategoryName
From MonthlyProjection As a
Inner Join (Select CustomerID, CategoryID, max(Date_Projection_Made) As LatestDate
From MonthlyProjection
Group By CustomerID, CategoryID) As b
On a.CustomerID=b.CustomerID
And a.CategoryID=b.CategoryID
Inner Join Customers c On a.CustomerID=c.CustomerID
Inner Join Category d on a.CategoryID=d.CategoryID Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker