?
Solved

MS SQL - Help building a Query

Posted on 2008-10-03
5
Medium Priority
?
251 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
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

569 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