Solved

MS SQL - Help building a Query

Posted on 2008-10-03
5
239 Views
Last Modified: 2010-04-21
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"
0
Comment
Question by:josephdaviskcrm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22638275
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
 
LVL 8

Expert Comment

by:jtdebeer
ID: 22638282
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
 

Author Comment

by:josephdaviskcrm
ID: 22638297
angelIII... this is exactly what I am looking for.  Only I need the month to be abbreviated.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22638314
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
 

Author Closing Comment

by:josephdaviskcrm
ID: 31502930
Thanks a bunch.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question