doubt in writing a CTE query

Question

doubt in writing a CTE query

t.mounika01

Mr or Mrs. 500

Points: 585
More actions
September 5, 2014 at 10:29 am

#310699

In adventureworks database,If I need to find out the product that has highest sales for each month in year 2001..
I have written the query like this:
With CTE as
(
Select ProductID,
OrderQty,
MONTH(OrderDate) as nameoftheMonth,
SUM(OrderQty) OVER (Partition by productID) as TotalOrderQty,
ROW_NUMBER() OVER (Partition By MONTH(OrderDate) Order by OrderQty desc) as Rows
From Sales.SalesOrderDetail A
Inner join Sales.SalesOrderHeader B ON A.SalesOrderID = B.SalesOrderID
Where YEAR(OrderDate)= 2001
)
Select * from CTE
Where Rows = 1
Can anyone please tell me if it is correct?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

t.mounika01 (9/5/2014)
In adventureworks database,If I need to find out the product that has highest sales for each month in year 2001..
I have written the query like this:
With CTE as
(
Select ProductID,
OrderQty,
MONTH(OrderDate) as nameoftheMonth,
SUM(OrderQty) OVER (Partition by productID) as TotalOrderQty,
ROW_NUMBER() OVER (Partition By MONTH(OrderDate) Order by OrderQty desc) as Rows
From Sales.SalesOrderDetail A
Inner join Sales.SalesOrderHeader B ON A.SalesOrderID = B.SalesOrderID
Where YEAR(OrderDate)= 2001
)
Select * from CTE
Where Rows = 1
Can anyone please tell me if it is correct?

Without having access to the sample database I can't tell, but my gut says it may not do what you want. Instead of asking if it is right, why not just run it and see if it returns the results you expect. if it doesn't, then it is wrong and you need to go back and fix it.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

I'm sure that it won't return what you expect.

You're ordering by the individual sales order detail and not the total of the month.

You shouldn't be using functions on the columns in the where clause. That eliminates SARGability on your query (google it). You should use the correct interval. E.g.

Where OrderDate >= '20010101'

AND OrderDate < '20020101'

And don't use keywords as column (or table) alias. In this case Rows.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Please provide table structure and sample data for this....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

LutzM SSC Guru Points: 107049 More actions · Answer 4

Depending on the expected result it can either be wrong or right:

SUM(OrderQty) OVER (Partition by productID) as TotalOrderQty,

will return the Quantity for the year 2001 per productID.

But if you're looking for the monthly quantity you might want to use

SUM(OrderQty) OVER (Partition by MONTH(OrderDate),productID) as TotalOrderQty,

Furthermore,

ROW_NUMBER() OVER (Partition By MONTH(OrderDate) Order by OrderQty desc) as Rows

will sort the [Rows] based on the highest single OrderQty in a given month, regardless of the product or any cumulated values.

I'm not sure if this is what you're looking for...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

kapil_kk (9/7/2014)
Please provide table structure and sample data for this....

You'll find everything you need right here: adventureworks database

That's why I did ask for anything even though I don't have the sample database on my laptop.