Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# MS SQL - Help building a Query

Posted on 2008-10-03
Medium Priority
246 Views
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
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

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22638275
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)
0

LVL 8

Expert Comment

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

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

LVL 39

Expert Comment

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

ID: 31502930
Thanks a bunch.
0

## Featured Post

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
###### Suggested Courses
Course of the Month8 days, 2 hours left to enroll

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

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