DECLARE @Extract_Date varchar(11) = '31 JUL 2015'
SELECT
Display_Month
, claim_month
, claim_year
, SUM(Claimed_Amount) AS Claimed
FROM tb_Claim_Detail
cross apply (select
case when month(Claim_Date) < 7
then month(Claim_Date) + 12
else month(Claim_Date)
end as claim_month
, year(Claim_Date) as claim_year
, LEFT(DATENAME(MONTH, Claim_Date), 3)
+ ' '
+ DATENAME(YEAR, Claim_Date) AS Display_Month
) ca
WHERE DATEDIFF(M, Claim_Date, @Extract_Date) BETWEEN 0 AND 24
GROUP BY Display_Month, claim_month, claim_year
order by claim_month, claim_year
I am not satisfied that the way you are selecting the date range is correct and I really don't like using a string 'dd mmm yyyy' as a means to declare a date.DECLARE @Extract_Date datetime = '20150801'
SELECT
Display_Month, claim_month, claim_year
FROM tb_Claim_Detail
cross apply (select
case when month(Claim_Date) < MONTH(@Extract_Date)
then month(Claim_Date) + 12
else month(Claim_Date)
end as claim_month
, year(Claim_Date) as claim_year
, LEFT(DATENAME(MONTH, Claim_Date), 3) + ' ' + DATENAME(YEAR, Claim_Date) AS Display_Month
) ca
WHERE ( Claim_Date >= dateadd(m,-24,@Extract_Date) AND Claim_Date < @Extract_Date )
GROUP BY Display_Month, claim_month, claim_year
order by claim_month, claim_year
I could give more precise answer if there were more facts, such as your current query