Maybe it can become clearer if you work with 2 CTEs.

WITH Months AS(

SELECTDATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate

FROM (VALUES (6),(5),(4),(3),(2),(1)) x(N)

),

MonthData AS(

SELECT LEFT( DATENAME(MONTH, startdate), 3) AS...

You previously posted a way to use only the month without the year.

Here's another way.

SELECT LEFT( DATENAME(MONTH, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)), 3) month_name,

You could just change the month_name column definition to your original code using datename.

As I told you, you can build a "calendar table" on the fly. This will work for 6 months, but you may need to increase it if the requirement changes....

It means that you ignored the first reply to your post made by me.

While I'm here, do you want to include a validation to include months without data?

You could easily...

born2achieve (1/29/2014)

---

hi Torpkev,

thanks for your reply. actually the avg gives in terms of decimal. i just rounded and shown as result.

But on your query where is the condition to...

I was thinking on how to do it with a CASE statement, but as you said, the code might have been complicated with multiple ignore and all those columns. I'm...

All this reminds me of my weirdest day regarding the weather (all in Mexico so under the 41st parallel:-P). I had to leave Chihuahua at 6am with -4°F(-20°C), arrived to...

Could you post DDL and sample data in a consumable format?

This should be easy but I don't want to spend half an hour formatting to create a test table...

I won't get to the exact data you posted as expected output, but I suppose it's not correct and you just included some numbers.

Here's an option.

select DATEADD(MM, DATEDIFF( MM, 0,...

That's a...

Now you're not including the target data type. Have you opened the link I provided?

And you are missing the SELECT statement.

Tip: If you select a function and press F1, you'll...

The issues you're facing are all covered in this book: Troubleshooting SQL Server: A Guide for the Accidental DBA[/url].

Take a look and make it your faithful companion.

EDIT: And be sure...

Have you tried it?

It might not return the expected results because you're not including your variable in the function.

ScottPletcher (1/28/2014)

---

Don't convert to a datetime, just compare it as characters. Invalid data may still get through, but you can't easily at all correct invalid data on the fly...