Top 10 products by Month

  • Hello

    Can anyone help me with this below.

    Jan2010 2011 2012

    course namecourse namecourse name

    course namecourse namecourse name

    course namecourse namecourse name

    course namecourse namecourse name

    course namecourse namecourse name

    Feb2010 2011 2012

    course namecourse namecourse name

    course namecourse namecourse name

    course namecourse namecourse name

    course namecourse namecourse name

    course namecourse namecourse name

  • If you want the Top 10 Items per month then a Ranking function (RANK(), ROW_NUMBER()) is going to be the best way to move.

    However without sample DDL and Input data as well as out put data its difficult to advise on the best solution.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • I got what I wanted but now I also want it by year.

    Here is the code below

    ;WITH MonthCTE(m) AS

    (SELECT 1 m

    UNION ALL

    SELECT m+1

    FROM monthCTE

    WHERE m < 12

    )

    SELECT m[Month]

    ,t.*

    FROM MonthCTE

    CROSS Apply

    (

    SELECT TOP 20

    YEAR(ah.enrllmnt_prcssd_dt) [Year]

    ,p.code

    ,p.full_name

    ,CAST(SUM(CASE

    WHEN e.payment_type = 1 THEN e.cash / 100

    ELSE (1.00 * IsNull(e.installment, 0)

    / 100.00 * IsNull(e.payment_months, 0)) + (1.00 *

    IsNull(e.deposit,

    0) / 100.00)

    END) AS decimal) AS Gev

    FROM enrollment AS e

    INNER JOIN action_history AS ah

    ON ah.action_history_id = e.action_history_id

    INNER JOIN product AS p

    ON p.product_id = e.product_id

    WHERE (

    month(ah.enrllmnt_prcssd_dt) BETWEEN 1 AND 12

    )

    AND (ah.enrllmnt_prcssd_dt >= '2010-01-01' )

    AND (ah.enrllmnt_prcssd_dt < '2011-01-01' )--AND (p.code IN (@Prod_code) )

    AND (e.status = 2 )

    AND (e.brand_id IN (2) )

    GROUP BY

    YEAR(ah.enrllmnt_prcssd_dt)

    ,--month(ah.enrllmnt_prcssd_dt),

    p.code

    ,p.full_name

    ORDER BY

    --YEAR(ah.enrllmnt_prcssd_dt),

    --month(ah.enrllmnt_prcssd_dt),

    Gev

    DESC

    ) t

  • Please post table definitions, sample data and expected outcome based on the sample data as per the second link in my signature.

    With that, we will be able to help you better with your query.

Viewing 4 posts - 1 through 3 (of 3 total)

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