SQL Query

Nearly there with my query now.

All i need to be able to do is show results only from the current month and back the last Twelve Months, also need this to be dynamic so when the month changes the query follows.

The code is below

SELECT     TOP (100) PERCENT DATENAME(month, createdon) + DATENAME(year, createdon) AS Month, projcode, result, COUNT(*) AS Expr2
FROM         dbo.dmsproj
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY YEAR(createdon), MONTH(createdon)

Thanks

John
pepps11976Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
SELECT     TOP (100) PERCENT DATENAME(month, createdon) + DATENAME(year, createdon) AS Month, projcode, result, COUNT(*) AS Expr2
FROM         dbo.dmsproj
where  createdon >= DATEADD(MONTH, -12, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY YEAR(createdon), MONTH(createdon)

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT     TOP (100) PERCENT DATENAME(month, createdon) + DATENAME(year, createdon) AS Month, projcode, result, COUNT(*) AS Expr2
FROM         dbo.dmsproj
where  DATENAME(month, createdon) = DATENAME(month, GETDATE()) and DATENAME(year, createdon) = DATENAME(year, GETDATE())
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY YEAR(createdon), MONTH(createdon)
0
 
mayank_joshiCommented:
SELECT     TOP (100) PERCENT DATENAME(month, createdon) + DATENAME(year, createdon) AS Month, projcode, result, COUNT(*) AS Expr2
FROM         dbo.dmsproj
where  DATENAME(month, createdon) between
 DATENAME(month, GETDATE()) and DATENAME(month, dateadd(month,-12,GETDATE()))
and  DATENAME(year, createdon) between DATENAME(year, GETDATE())
and DATENAME(year, dateadd(month,-12,GETDATE()))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY YEAR(createdon), MONTH(createdon)
	 

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
pepps11976Author Commented:
Hi Tried the query but it returns nothing
0
 
mayank_joshiCommented:
sorry you should use DATEPART:-

SELECT     TOP (100) PERCENT DATENAME(month, createdon) + DATENAME(year, createdon) AS Month, projcode, result, COUNT(*) AS Expr2
FROM         dbo.dmsproj
where  DATEPART(month, createdon) between
 DATEPART(month, GETDATE()) and DATEPART(month, dateadd(month,-12,GETDATE()))
and  DATEPART(year, createdon) between DATEPART(year, GETDATE())
and DATEPART(year, dateadd(month,-12,GETDATE()))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY YEAR(createdon), MONTH(createdon)
	 

Open in new window

0
 
pepps11976Author Commented:
Tried again but the query is still returning nothing
0
 
pepps11976Author Commented:
Anyone???
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.