doubt in writing a CTE query

  • 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?

  • 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.

  • 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
  • Please provide table structure and sample data for this....

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

  • 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]

  • 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.

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

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