Most recent date 1

[Cpreston]

Hi

I am trying to get out of a table of cost price changes the last date a cost was changed for products.

I am trying just on one code at the moment to simplify things. My code is as below but I am getting the error An expression of non-boolean type specified in a context where a condition is expected, near ')'

I tried to change the date format but still get the same issue. Any ideas please. Thanks

SELECT  Format([ChangeDateTime],'dd/MM/yyyy') AS Date, ProductID, AverageCostPriceWithAdditional, LastCostPriceWithAdditional
FROM ProductPriceHistory
Group by ProductPriceHistory.ProductID, ProductPriceHistory.AverageCostPriceWithAdditional,ProductPriceHistory.LastCostPriceWithAdditional,ProductPriceHistory.ChangeDateTime
HAVING ProductID = 47561 AND MAX(ProductPriceHistory.ChangeDateTime)

 

[Cpreston]

Hi

I managed to get the one row back using the code below. However, now I want to get it by all codes that have a lastcostpricewithaddiotnal listed, but using this code without the WHERE ProductID = 47561); I am only getting one row back which is the very last date a change as been made. How would I get it to list all the different codes but the latest change. Thanks

SELECT ProductID, AverageCostPriceWithAdditional, LastCostPriceWithAdditional, ChangeDateTime
FROM ProductPriceHistory
WHERE ChangeDateTime=(
SELECT MAX(ChangeDateTime) FROM ProductPriceHistory WHERE ProductID = 47561);

 

[Andrzejek]

What's your Primary Key field in your [tt]ProductPriceHistory[/tt] table?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

It as these keys listed under the columns

Productid PK, FK
Branchid, PK, FK
ChangeDateTime PK

Thanks

 

[Cpreston]

Go this from the database also

pk_Name
PK_ProductPriceHistory

columns
ProductID, BranchID, ChangeDateTime

 

[Andrzejek]

Just a guess here....

SELECT *
FROM ProductPriceHistory
WHERE Productid + Branchid IN (
  SELECT MyID FROM (
    SELECT Productid + Branchid As MyID, MAX(ChangeDateTime)
    FROM ProductPriceHistory
    GROUP BY Productid + Branchid ))

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

HI

Gave your Guess a go but I am getting the below error, and I don't quite get the MyID part of it, thanks

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

 

[Andrzejek]

If you just do:

SELECT Productid + Branchid As MyID, MAX(ChangeDateTime)
FROM ProductPriceHistory
GROUP BY Productid + Branchid

Do you get the outcome of two columns?
Concatenated Productid and Branchid fields (As MyID) and the correct Max date of ChangeDateTime for the concatenated values?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

HI

That brought back

44053 rows affected)

which appear to be unique records looking at the MYID

 

[Andrzejek]

Are those the records (based on Productid + Branchid) that you want to have as the outcome of your request from ProductPriceHistory table?

(You may compare this output to the one as you got for ProductID = 47561)


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

It brings back which is the latest record (see below), so that would work I believe, how do we get the fields in?

47561 2019-11-19 12:41:43.580

 

[Andrzejek]

So, if we know now that this works OK:

[blue]SELECT Productid + Branchid As MyID, MAX(ChangeDateTime)
FROM ProductPriceHistory
GROUP BY Productid + Branchid [/blue]

We can get just the first column (MyID) from it:

SELECT MyID FROM (
[blue]SELECT Productid + Branchid As MyID, MAX(ChangeDateTime)
FROM ProductPriceHistory
GROUP BY Productid + Branchid [/blue])

And if that works OK, we can treat it as a PK field to use in IN part of your Select (as in my post of 2 Dec 19 18:38)


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

I have the code as below but it is giving me Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.

I have tried all sorts to rectify it but cannot solve it.

SELECT *
FROM ProductPriceHistory
WHERE Productid + Branchid IN (
  SELECT MyID FROM (
SELECT Productid + Branchid As MyID, MAX(ChangeDateTime)
FROM ProductPriceHistory
GROUP BY Productid + Branchid)

 

[Andrzejek]

Every "(" should have matching ")", so you would need 2 "))" at the end.

That's why I tried to do it step-by-step in my last posts.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Thanks for all your reply's and patience. The ball park on this as now changed and I have created another query which if I can get the calculation to work will be wht is required.

I can create a new Thread if you prefer but will ask the question anyway.

I need to calculate SUM (AverageCostPriceWithAdditional * Thickness/1000 * Width/1000 * TallyLengths) ) as Calucaltion

The tallycosts field is a varchar field and I am getting Error converting data type varchar to numeric. if I onclude it in the formula. I believe I should use case but how and where I am not sure. I have tried a few things but no luck.

 

[Andrzejek]

I would start a new thread, but...
[tt]
SUM (AverageCostPriceWithAdditional * Thickness/1000 * Width/1000 * TallyLengths) as Calucaltion[/tt]

I do not see any [tt]tallycosts[/tt] field in the expression above...


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

It was TallyLengths I meant sorry. I found a value in the filed that could not convert and should not have been in the table anyway, got rid of that and the calculation worked.

Thanks for all your help

 

[Andrzejek]

If TallyLengths should only contain numbers, I would strongly suggest to change it from varchar to numeric format.


---- Andy

There is a great need for a sarcasm font.