MS SQL - Help building a Query

I have the following query...

SELECT Year(TransDate) AS TransDateYear, Month(TransDate) AS TransDateMonth, SUM(Amount)
  FROM BudgetTransact
  WHERE TransDate BETWEEN '1-1-2006' AND '1-1-2009'
    AND Category IN ('Gasoline')
  GROUP BY Year(TransDate), Month(TransDate)
ORDER BY Year(TransDate), Month(TransDate)

I need the date to be returned in 1 column in the format of " Jan'07 " instead of two different columns.  Currently TransDateYear is returned as "2007" and TransDateMonth is returned as "1"
josephdaviskcrmAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT DateName(month, TransDate) + '''' + CAST(Year(TransDate) AS VARCHAR(4)) AS TransDateMonth, SUM(Amount)
  FROM BudgetTransact
  WHERE TransDate BETWEEN '1-1-2006' AND '1-1-2009'
    AND Category IN ('Gasoline')
  GROUP BY Year(TransDate), Month(TransDate), DateName(month, TransDate) + '''' + CAST(Year(TransDate) AS VARCHAR(4)) 
ORDER BY Year(TransDate), Month(TransDate)

Open in new window

0
 
jtdebeerCommented:
You can create a second table with 3 Col
Month, Year, DateString

Then add sub Select query to your Group By
Select DateString From MyDatesTable
where (Month = Month(TransDate)) AND (Year = Year(TransDate))

Just an idea......
Make sure to populate the table far in advance
0
 
josephdaviskcrmAuthor Commented:
angelIII... this is exactly what I am looking for.  Only I need the month to be abbreviated.
0
 
BrandonGalderisiCommented:
no points please, but try this:

SELECT left(DateName(month, TransDate),3) + '''' + CAST(Year(TransDate) AS VARCHAR(4)) AS TransDateMonth, SUM(Amount)
  FROM BudgetTransact
  WHERE TransDate BETWEEN '1-1-2006' AND '1-1-2009'
    AND Category IN ('Gasoline')
  GROUP BY Year(TransDate), Month(TransDate), left(DateName(month, TransDate),3) + '''' + CAST(Year(TransDate) AS VARCHAR(4))
ORDER BY Year(TransDate), Month(TransDate)

0
 
josephdaviskcrmAuthor Commented:
Thanks a bunch.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.