This part of your code causes a full table scan regardless of the TOP 52 because of the ORDER BY in the ROW_NUMBER(). Since you're doing the ORDER BY on the StockCode table, it also does a shedload of unnecessary reads. Also, there is no guarantee that you'll return 52 rows because there's no guarantee that the Data.Stock table will start with or be maintained in such a fashion as to always have at least 52 rows in it because it's a user table rather than a utility or fix system table.
SELECT TOP 52 ROW_NUMBER() OVER (ORDER BY StockCode) AS Num
This part of your code causes a full table scan because the WHERE clause is non-SARGable as well as there being no indexes on the table.
SELECT SUM(TotalSalePrice) AS SalesForTheWeek
,DatePart(wk, SaleDate) AS WeekNo
WHERE YEAR(SaleDate) = 2016
GROUP BY DatePart(wk, SaleDate)
Also, you should always use the 2 part naming convention both in the FROM clause (which you've dome) and the SELECT clause of a joined select (which you've not done).
Also, what is a "week"? DATEPART(wk) will only return 2 days for the first week of Jan 2016. The only reason why it returns 7 days for the last week of 2016 is because you get lucky and average of once every 7 years.
Last but not least, your sample data model in the PrestigeCars database is devoid of any and all Primary Keys and indexes.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)