none
Need Help on the query using top and order by caluse RRS feed

  • Question

  • PrdID Prd Name Purchase Date cost

    1 I Pad 3.0 2012-03-19 00:00:00.000 190
    1 I Pad 3.0 2012-03-28 00:00:00.000 200
    2 I Pad 2.0 2012-01-26 00:00:00.000 150
    2 I Pad 2.0 2012-03-19 00:00:00.000 160
    3 I Phone 4.0 2012-01-24 00:00:00.000 400
    3 I Phone 4.0 2012-03-28 00:00:00.000 420
    4 I PHONE 4S 16GB 2012-01-24 00:00:00.000 450
    4 I PHONE 4S 16GB 2012-01-26 00:00:00.000 460
    5 Nokia N95 2012-03-29 00:00:00.000 100
    6 Nokia Asha 303 2012-03-29 00:00:00.000 60

    Hello Readers. I am really stuck finding out the last three purchase  for each product depending up their purchase date. What basically i want to do is I need to calculate the avg cost for each product in their last 3 purchases in a subquery which i've found the most difficult thing ever seen up till now in my tsql life.

    I am desperately looking forward to your guidance. 

    Thanks In Advance.


    Hamad Salahuddin Elite Technologies,Abbottabad Pakistan www.et.com.pk

    Thursday, March 29, 2012 5:10 AM

Answers

All replies

  • Thursday, March 29, 2012 5:14 AM
    Moderator
  • WITH myCTE AS (
                      SELECT ROW_NUMBER() OVER(PARTITION BY PrdName ORDER BY PopurchaseDate DESC) AS 
                             RowNum
                             -- You can even use RANK() and DENSE_RANK() functions as applicable
                            ,p.PrdID
                            ,s.PrdName
                            ,a.PopurchaseDate
                            ,cost
                      FROM   MyTable
                  )
    SELECT *
          ,AVG(cost) OVER(PARTITION BY PrdName) AS Average
    FROM   myCTE
    WHERE  RowNum BETWEEN 1 AND 3

    Check this Link for the Usage of OVER clause and all aggregating functions used with the Over clause @  http://msdn.microsoft.com/en-us/library/ms189461.aspx

    This is the repeat of the answer on your previous thread. Do not repeat threads.



    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Thursday, March 29, 2012 7:52 AM
  • Hi Hamad,

    The below is the required query for your requirement:

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

    SELECT PID, [Prd Name],[Purchase Date],  Cost, AVG(cost) OVER(PARTITION BY [Prd Name]) AS [AverageCost]
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY PID ORDER BY [Purchase Date] DESC) AS PurchasedDate,*
    FROM TEST
    ) as ordlist
    Group by [Prd Name],[PID],[Purchase Date],  Cost, PurchasedDate
    Having ordlist.PurchasedDate <= 3

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

    output:

    PID Prd Name Purchase Date Cost AverageCost
    2 I Pad 2.0 1/26/2012 150 150
    2 I Pad 2.0 1/26/2012 150 150
    1 I Pad 3.0 3/19/2012 120 152
    1 I Pad 3.0 3/20/2012 146 152
    1 I Pad 3.0 3/28/2012 190 152
    3 I Phone 4.0 1/24/2012 400 410
    3 I Phone 4.0 3/28/2012 420 410
    4 I PHONE 4S 1/26/2012 460 331.6666
    4 I PHONE 4S 1/28/2012 465 331.6666
    4 I PHONE 4S 3/31/2012 70 331.6666
    6 Nokia Asha 3/30/2012 60 60
    5 Nokia N95 3/29/2012 100 100

    Kindly mark as answer, if this helps !!


    =================================== Rahul Vairagi ========================================== My Blogs: www.sqlserver2005forum.blogspot.com

    Thursday, March 29, 2012 9:31 AM