CODEWITHSUNDEEP
sql-server
Kavitha
Kavitha

Reputation: 1467

How to get the month name in YTD by month in sql server 2008?

I am trying for YTD by Month in sql server. I dont have any records in FEB Month.

select WO,CreatedDate,Item ,(DATENAME(Month,CreatedDate)) as [Month],
    DATEPART(yyyy ,CreatedDate) as [Year]from #temptbale
Group by WO,CreatedDate,Item
Order by Month

Output :

   WO        Date         Item      Month
WO-1009 2014-04-29  Bicycle - Blue  April   
WO-1010 2014-04-29  Bicycle - Blue  April   
WO-1012 2014-01-20  Bicycle - Blue  January 
WO-1013 2014-01-24  Bicycle - Blue  January 
WO-1021 2014-03-12  Bicycle - Red   March   
WO-1022 2014-03-12  Bicycle - Red   March

Desire Result :

WO        Date         Item      Month
WO-1009 2014-04-29  Bicycle - Blue  April   
WO-1010 2014-04-29  Bicycle - Blue  April   
WO-1012 2014-01-20  Bicycle - Blue  January 
WO-1013 2014-01-24  Bicycle - Blue  January 
WO-1021 2014-03-12  Bicycle - Red   March   
WO-1022 2014-03-12  Bicycle - Red   March   
NULL         NULL      NULL           February

Upvotes: 0

Views: 128

Answers (1)

Szymon
Szymon

Reputation: 43023

You can use a table variable with names of months and join your query to this variable

DECLARE @Months TABLE (Month_Value INT, Month_Name NVARCHAR(20))
INSERT @Months VALUES (1, 'January'),(2, 'February'),(3, 'March'),(4, 'April'),(5, 'May'),(6, 'June'),
    (7, 'July'),(8, 'August'),(9, 'September'),(10, 'October'),(11, 'November'),(12, 'December')

;with CTE AS (
    select WO,CreatedDate,Item ,DATENAME(Month,CreatedDate) as [Month],
        DATEPART(yyyy ,CreatedDate) as [Year] from #temptbale
    Group by WO,CreatedDate,Item)

select CTE.WO, CTE.CreatedDate, CTE.Item, coalesce(CTE.[Month], M.Month_Name), CTE.[Year] from CTE
full outer join @Months M ON CTE.[Month] = M.Month_Name
where Month_Value <= DATEPART(month, getdate())
order by coalesce(CTE.[Month], M.Month_Name)

Upvotes: 1

Related Questions