asked on

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

Alpesh Patel

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)

mayank_joshi

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

pepps11976

ASKER

Hi Tried the query but it returns nothing

mayank_joshi

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

pepps11976

ASKER

Tried again but the query is still returning nothing

pepps11976

ASKER

Anyone???

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial