February 13, 2013 at 11:57 pm
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
February 14, 2013 at 12:12 am
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
February 14, 2013 at 1:02 am
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
February 14, 2013 at 1:28 am
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