DECLARE @FromDate varchar(50)
DECLARE @Todate varchar(50)
SET @FromDate='10-1-2008'
SET @Todate='2-1-2009'
SELECT
CASE MONTH(ValidFrom)
WHEN 1 THEN 'JAN-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 2 THEN 'FEB-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 3 THEN 'MAR-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 4 THEN 'APR-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 5 THEN 'MAY-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 6 THEN 'JUN-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 7 THEN 'JUL-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 8 THEN 'AUG-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 9 THEN 'SEP-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 10 THEN 'OCT-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 11 THEN 'NOV-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 12 THEN 'DEC-'+CAST(year(ValidFrom)AS VARCHAR)
END MonthName,COUNT(*)AS Total,
COUNT( CASE Level WHEN 1 THEN Level END )AS Platinum,
COUNT( CASE Level WHEN 2 THEN Level END )AS Gold,
COUNT( CASE Level WHEN 7 THEN Level END )AS Trail
FROM SubscriberLevel
WHERE
((YEAR(ValidFrom) >= YEAR(@FromDate)
AND Month(ValidFrom) >= Month(@FromDate)
AND (Year(ValidFrom) <= YEAR(@Todate))--AND
-- AND MONTH(ValidFrom) <= 12 OR
OR Month(ValidFrom) <= Month(@Todate)))
GROUP BY YEAR(ValidFrom),MONTH(ValidFrom)
ORDER BY YEAR(ValidFrom),MONTH(ValidFrom)
