Solved

MS SQL - Help building a Query

Posted on 2008-10-03
5
194 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 142

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2005 - reduce database/logfile size on system that is running out of disk space 36 41
SQL 2000:  Shrink LDF file 11 39
Help with SQL joins 9 43
sql query help 2 44
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now