query where there is no match (1 Viewer)

[CoffeeGuru]

Hi guys
I am looking for advice here as I am not sure how to get this scenario to work.
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo
I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.

tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;


my new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;

The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.

So that when I do year on year revenue calculations it works properly.

Any ideas gratefully received.

Martin

 

[namliam]

Best solution is to have a price table with a start and end date, not weeknumbers....
You can then fairly easily get the correct price

You can do this with weeknumbers as well, assuming you concatinate year and weeknumbers

Source.Year & source.Week between Price.StartYear & price.startweek and nz(price.endyear, 9999) & nz(price.endweek,99)

If your weeknumbers are not 2 didgit always, so 05 and not just 5 otherwize this goes bad. use Format(weeknumber,"00") if you have weeknumbers as real numbers...

 

[CoffeeGuru]

Thanks namliam
I will certainly look into this idea.I can only use dates if I do some sly work with the year and weekno which I have had to do in this project anyway in places.
Likewise the concatenation of year and week no, which again I have done in places.
So your advice is have a start and end date for every tblRRP entry which is certainly doable at this stage.

Hmm still have to do some serious thought to this.

Martin.

 

[CoffeeGuru]

OK I have beendoing som emajor work on this now and come up with the following.
I think I need to insert a select query into my main query and this is where my knowledge stops :banghead:

This is quite long so please bear with me...

I have been running queries without taking into consideration the historic prices of products.
Whilst this was ok in the short term, in January we had a price increase and so when I run reports over this year vs last year the results are now skewed as the database only had the current prices

I have now introduced 2 new tables

tblCurrency
CountryCode varchar i.e. GB, IT
Currency varchar i.e. GBP, EUR


tblPriceList
YYYYWW int i.e. 201301
UID varchar i.e. ‘Q99X07114011’
Currency varchar i.e. ‘GBP’
[RRP W/O TAX] float i.e. 31.74

==============================================================

I have a small piece of SQL, thanks to Google, that will pick up the correct price
SELECT TOP 1
[YYYYWW]
,[UID]
,[Currency]
,[RRP W/O TAX]
FROM tblPriceList
WHERE
UID = 'Q99X07114011'
AND [Currency] = 'GBP'
ORDER BY ABS(YYYYWW - 201801)

Source: http://www.sqlservercurry.com/2010/06/find-closest-number-using-sql-server.html
==============================================================

As long as I pass the current rows UID, Currency and concatenated year+week for this I am using
CAST(CAST([cm_data].[year] as varchar) + right('0' + CAST([cm_data].[week] as varchar), 2) AS int) as YYYYWW

==============================================================

So here is my challenge
I think, I need to amend this Query to include the query above so that the correct [RRP W/O TAX] is used

SELECT
tmpStores.Planogram,
tmpStores.Nbr_of_Stores,
Sum(tblProducts.Size) AS [Sales units year to date (cartridges)],
Sum(tblProducts.Size)/Max([DefaultWeek]) AS [Average weekly sales units year to date (cartridges)],
Sum([Volume]*[tblPriceList].[RRP W/O TAX]) AS [Sales revenue year to date],
Sum([Volume]*[tblPriceList].[RRP W/O TAX])/Max([DefaultWeek]) AS [Average weekly sales revenue year to date (cartridges)],

--I have just added the next 3 lines
[CM_DATA].Year,
[CM_DATA].Week,
CAST(Cast([cm_data].[year] as varchar) + right('0' + cast([cm_data].[week] as varchar), 2) AS int) as yyyyww

FROM tblCurrencies
INNER JOIN (((tblStoreMaster
INNER JOIN ((tblLive
INNER JOIN (tmpStores
INNER JOIN ([Current_User]
INNER JOIN CM_DATA
ON ([Current_User].Year = CM_DATA.Year)
AND ([Current_User].Chain = CM_DATA.Chain)
AND ([Current_User].CountryCode = CM_DATA.Country))
ON (tmpStores.Chain = CM_DATA.Chain)
AND (tmpStores.CountryCode = CM_DATA.Country))
ON (tblLive.Live = [Current_User].Live)
AND (tblLive.UID = CM_DATA.UID)
AND (tblLive.Chain = CM_DATA.Chain)
AND (tblLive.CountryCode = CM_DATA.Country))
INNER JOIN tblProducts
ON (CM_DATA.Country = tblProducts.[COUNTRY CODE])
AND (CM_DATA.UID = tblProducts.SKU))
ON (tblStoreMaster.Planogram = tmpStores.Planogram)
AND (tblStoreMaster.StoreNo = CM_DATA.StoreNo)
AND (tblStoreMaster.Chain = CM_DATA.Chain)
AND (tblStoreMaster.CountryCode = CM_DATA.Country))
INNER JOIN tmpDefaultPeriod_0
ON CM_DATA.Year = tmpDefaultPeriod_0.DefaultYear)
INNER JOIN tblPriceList ON CM_DATA.UID = tblPriceList.UID)
ON ([Current_User].CountryCode = tblCurrencies.CountryCode)
AND (tblCurrencies.Currency = tblPriceList.Currency)

WHERE
(((CM_DATA.Week)<=[tmpDefaultPeriod_0].[DefaultWeek])
AND ((tmpStores.Planogram)<>'Z'))

GROUP BY
tmpStores.Planogram,
tmpStores.Nbr_of_Stores,
CM_DATA.Country,
CM_DATA.Chain,
CM_DATA.Year,
tmpDefaultPeriod_0.DefaultWeek,
[CM_DATA].Year,
[CM_DATA].Week,
tblPricelist.[RRP W/O TAX]

ORDER BY
tmpStores.Planogram;

==============================================================

It may be that I need to create another temporary table which is not a problem I now have scores of these J


Martin

 

[namliam]

Do you have a price for each available week number? or do you have one price at a given week and not have any prices untill it changes?

Assuming the easier (for SQL) solution, you have a price for every weeknumber

I think, you want to instead of:
INNER JOIN tblPriceList ON CM_DATA.UID = tblPriceList.UID

use:

INNER JOIN tblPriceList ON CM_DATA.UID = tblPriceList.UID
                  AND CM_DATA.[Year] * 100 + cm_data.[Week) = tblPricelist.YYYYWW

If you have only one price in like 201401 (week 1 year 2014) which is valid untill such time it changes, i.e. in 201426...
You would be best served if your table holds a StartingYYYYWW and EndingYYYYWW
Then in your SQL you can use:

INNER JOIN tblPriceList ON CM_DATA.UID = tblPriceList.UID
                  AND CM_DATA.[Year] * 100 + cm_data.[Week] Between tblPricelist.StartingYYYYWW and tblPricelist.EndingYYYYWW

Assuming your EndingYYYYWW will be something like 999988 as default value untill such time it will change where it will get the proper weeknumber where it is supposed to be last valid. I.e. Initial record:
201401 999988
Then in 201426 a new price, thus change above record to:
201401 201425
And create a new record
201426 999988

I hope you can see what I mean and work this out the way you prefer.

 

[CoffeeGuru]

Hi namliam

I have the latter ie "one price at a given week and not have any prices until it changes"

I was grasping at straws when I only used a change date, but it looks like I may need to use the two dates as in your wonderful example.
Many thanks for the time you put in to read and understand my ramblings.

Martin

 

[namliam]

Well you can work with only one weeknumber but things get A LOT more complicated and involved as you need to dynamicaly determine which ONE record is the proper price. it is doable and not THAT hard once you have it in place.... Working with 2 weeks though is a lot more easy and faster too when using queries and all that. which is where things can matter once your database and thus its history and recordcount start going up.
The 2 week solution will work just fine, the 1 weeknumber solution will degrade in performance relatively fast and before you know it will cause considerable slow down in your database.

I hope you can work out what I described above, if you run into any trouble post back and we will see what we can do to resolve them issues.