Top Status Date Per Sales Date 1

[danneedham]

Hi All,

I have two tables, one for sales, and one for status. I would like to get the "top" status value PER sales date.

I have tried the following query but its dropping sales dates.

SELECT Sales.StoreID, Sales.Date, Max(Status.StatusDate) AS MaxOfStatusDate, Sum(Sales.Amount) AS SumOfAmount
FROM Sales, Status
GROUP BY Sales.StoreID, Sales.Date
HAVING (((Max(Status.StatusDate))<=[Sales].[Date]));


Does anyone have any ideas how i can acheive this?

 

[jedraw]

Could you show table structures, and a few sample records?

 

[Golom]

Before getting into producing the result you want, you need to look at how you are joining the tables.

You have a cross-join (aka a cartesian product) which means that every record in "Sales" will be matched with every record in "Status".

Do you have a field in each table (possibly "StoreID") that could be used to match records between the two tables?

The implication of your query is that MAX(Status.StatusDate) will be computed for each unique combination of "Sales.StoreID, Sales.Date" but, with the cross-join, it will produce the same value for all such combinations because ALL "Status.StatusDate" values will be included for each such combination.

 

[danneedham]

Hi There

Sales Table:
StoreID, SalesDate, Amount
A0001,01-01-01,£100
A0001,01-01-02,£200

Status Table:
StoreID, StatusDate, Status
A0001, 01-01-01, NEW
A0001, 01-01-02, LFL

Queries arent my forte as you can probably tell!

Thanks in advance...


Dan

 

[Golom]

Give this a try

SELECT S.StoreID, 
       S.SalesDate, 
       Max(T.StatusDate) AS MaxOfStatusDate, 
       Sum(S.Amount) AS SumOfAmount

FROM Sales As S INNER JOIN Status As T
     ON S.StoreID = T.StoreID

WHERE T.StatusDate =
      (Select MAX(StatusDate)
       From Status As X
       Where X.StoreID = S.StoreID)

GROUP BY S.StoreID, S.SalesDate

 

[danneedham]

Thanks Golom!

This is exactly what i needed! You really are a star!

Dan

 

[danneedham]

Hi Golom

Sorry, I did not realise... I've just tried that query but it is giving the most recent status for both sales date:

StoreID SalesDate MaxOfStatusDate SumOfAmount
A0001 01/01/2007 01/01/2008 100
A0001 01/01/2008 01/01/2008 200

What I was looking for was the max status date per sales date...

so for sales between 01/01/07 and 31/12/01 the status for A0001 would be "NEW" with a status date of 01/01/07. Sales from 01/01/02 would have the status of "LFL"

Sorry to be such a pain.


Dan

 

[Golom]

Oh! Then probably

SELECT S.StoreID, 
       S.SalesDate, 
       T.StatusDate, 
       (Select Sum(X.Amount)
        From Sales As X
        Where X.StoreID = S.StoreID
          And X.SalesDate = S.SalesDate) AS SumOfAmount

FROM Sales As S INNER JOIN Status As T
     ON S.StoreID = T.StoreID

WHERE T.StatusDate =
      (Select MAX(StatusDate)
       From Status As X
       Where X.StoreID = S.StoreID)

GROUP BY S.StoreID, S.SalesDate, T.StatusDate

I'm uncertain about exactly what you want the SUM(Amount) to calculate. Using this sub-query, it will report the SUM for each unique combination of StoreID + SalesDate.

 

[danneedham]

Hi Golom

Still no luck unfortunately... it is still showing the most recent status, rater than the most recent status per sales date...

The use of the sum for the sales amount is for the following. As a retail business - the tills poll their figures. If stores dont poll for whatever reason a manual adjustment is made. I've got the sum there to eventually filter the sales depending on whether i would like "polled" sales or sales with adjustments.

Thanks

Dan

 

[Golom]

Sorry. I didn't realize that it was supposed to be "per salesdate".

SELECT S.StoreID, 
       S.SalesDate, 
       T.StatusDate, 
       (Select Sum(X.Amount)
        From Sales As X
        Where X.StoreID = S.StoreID
          And X.SalesDate = S.SalesDate) AS SumOfAmount

FROM Sales As S INNER JOIN Status As T
     ON S.StoreID = T.StoreID

WHERE T.StatusDate =
      (Select MAX(StatusDate)
       From Status As X 
       Where X.StoreID = S.StoreID
         [red]AND X.SalesDate = S.SalesDate[/red])

GROUP BY S.StoreID, S.SalesDate, T.StatusDate

 

[danneedham]

Thanks for your help Golom,

It appears to be coming up with a parameter box for "X.SalesDate"

I promise I will be going out and buying books on queries ASAP! I didnt realise how little I knew!

Thanks

Dan

 

[Golom]

OK. That was my fault. This code is what you need.

SELECT S.StoreID, 
       S.SalesDate, 
       T.StatusDate, 
       (Select Sum(X.Amount)
        From Sales As X
        Where X.StoreID = S.StoreID
          And X.SalesDate = S.SalesDate) AS SumOfAmount

FROM Sales As S INNER JOIN Status As T
     ON S.StoreID = T.StoreID

WHERE T.StatusDate =
      (Select MAX(StatusDate)
       From Status As X
       Where X.StoreID = S.StoreID)

GROUP BY S.StoreID, S.SalesDate, T.StatusDate

The reason that it is giving you the same value for every SaleDate is that your "Status" table has a "StatusDate" only at the StoreID level.

There is no breakdown in "Status" to give you different "StatusDate" values for different "SaleDate" values.

... unless of course, there really is a "SaleDate" field in "Status" that you haven't told us about.

 

[danneedham]

Hi Golom

These tables have been created by myself, so the only columns there are:

Sales
StoreId, SalesDate, Amount

Status
StoreID, StatusDate, Status

Is there no way of producing a query which takes each entry in the sales table, and searches in the status table for the max statusdate that is equal to or less than the sales date?

Do you think this is the best way? Or as i process a days sales into the table i should append the status onto the record?

Thanks

Dan

 

[Golom]

On my way to a meeting. Try this

SELECT S.StoreID, 
       S.SalesDate, 
       T.StatusDate, 
       (Select Sum(X.Amount)
        From Sales As X
        Where X.StoreID = S.StoreID
          And X.SalesDate = S.SalesDate) AS SumOfAmount

FROM Sales As S INNER JOIN Status As T
     ON S.StoreID = T.StoreID

WHERE T.StatusDate =
      (Select MAX(StatusDate)
       From Status As X
       Where X.StoreID = S.StoreID
         [red]AND X.StatusDate <= S.SalesDate[/red])

GROUP BY S.StoreID, S.SalesDate, T.StatusDate

 

[danneedham]

bonus!

Just what i was looking for!!!

Thanks again!

Dan