Bulk Bulk Bulk Empty Empty
Looks like homework. What have you got so far? If you post a query that shows the order counts per customer/year and standard cost, that's a good start for the update.
sales order header has a FK to dimdate, which has the date information I assume
Hint you need to join SalesOrderDetail to SalesOrderHeader to get the date
and dimdate, I think
Can someone provide me the right Query if you guys could help me out..
vijay4, let's walk thru the whole thing and break it into steps. Don't try to write the whole thing at once. You need to build up the final query. Plus, this allows each step to be run separately to verify it's working correctly before going on to the next step.
The whole thing:
"Reduce Product’s ListPrice by 20% if it did not receive any order for the last two years and the reduced price would be still above StandardCost."
The first part analysis:
So, you need to possibly update only those products which haven't been ordered in the last two years. Determining that must come first -- the StandardCost part has to wait, because you can't determine cost until you've determined the ProductId!
Ok then, what minimum info do we need to determine product ids not ordered in the last two years? Well, we'll need the productid and the orderdate. Which means we need the Header and Detail tables only (for now; yes, we'll need other table(s) later). All right, let's join those two first:
SELECT sod.ProductId, soh.DateOrdered
FROM SalesOrderDetail sod
INNER JOIN SalesOrderHeader soh ON soh.SalesOrderId = sod.SalesOrderId
Hmm, but that will give us all order dates, not just 2 year old orders. If we think about it, what we really need is the last order date for each product id, and then we can test that to see if it's 2 years old.
So, vijay4, what SQL code/function would you use to get the last order date for each productid?
1 Like
a MAX Function ?
Thanks a Ton Scott.
Kindly provide me your help in the next part as well please.