Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL - Help building a Query

Posted on 2008-10-03
5
Medium Priority
?
249 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

885 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