Group by Month

Pardon my persistant newbieness:
I have something like this:
SELECT
SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
ELSE 0 END) AS 'Closed'
FROM
tablename
WHERE
customer = 'customer_name'
and date_value >= '01/01/2005' and date_value < '01/01/2006'
Now I want to group by month and show the month in "mmmm yyyy" format.
Any help?
todSee DATENAME function in SQL Server Books Online. Depending on your client
side display, it might also be better to change the format on the front
end/report.
Anith|||group by year(date_value), month(date_value)
William Stacey [MVP]
"Tod" <todtown@.swbell.net> wrote in message
news:1138311375.334942.46790@.o13g2000cwo.googlegroups.com...
| Pardon my persistant newbieness:
|
| I have something like this:
|
| SELECT
| SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
| SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
| ELSE 0 END) AS 'Closed'
| FROM
| tablename
| WHERE
| customer = 'customer_name'
| and date_value >= '01/01/2005' and date_value < '01/01/2006'
|
| Now I want to group by month and show the month in "mmmm yyyy" format.
|
| Any help?
|
| tod
||||Try
SELECT DATENAME(mm, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) ) +
' ' + DATENAME(yyyy, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) ),
SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
ELSE 0 END) AS 'Closed'
FROM
tablename
WHERE
customer = 'customer_name'
and date_value >= '01/01/2005' and date_value < '01/01/2006'
GROUP BY
DATENAME(mm, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) ) +
' ' + DATENAME(yyyy, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) )
"Tod" wrote:

> Pardon my persistant newbieness:
> I have something like this:
> SELECT
> SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
> SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
> ELSE 0 END) AS 'Closed'
> FROM
> tablename
> WHERE
> customer = 'customer_name'
> and date_value >= '01/01/2005' and date_value < '01/01/2006'
> Now I want to group by month and show the month in "mmmm yyyy" format.
> Any help?
> tod
>|||On 26 Jan 2006 13:36:15 -0800, Tod wrote:

>Pardon my persistant newbieness:
>I have something like this:
>SELECT
> SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
> SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
>ELSE 0 END) AS 'Closed'
>FROM
> tablename
>WHERE
> customer = 'customer_name'
> and date_value >= '01/01/2005' and date_value < '01/01/2006'
>Now I want to group by month and show the month in "mmmm yyyy" format.
>Any help?
>tod
Hi Tod,
SELECT
DATEADD(month, DATEDIFF(month, 0, date_value), 0) AS Month,
SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS
'Open',
SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name'
THEN 1 ELSE 0 END) AS 'Closed'
FROM
tablename
WHERE
customer = 'customer_name'
and date_value >= '20050101' and date_value < '20060101'
GROUP BY
DATEDIFF(month, 0, date_value)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||oops. Try this and add your other stuff.
select DATENAME(mm, min(Date)) + ' ' +datename(yyyy, min(Date))
from @.t
group by year(Date), month(Date)
William Stacey [MVP]
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OPHi1QsIGHA.3936@.TK2MSFTNGP12.phx.gbl...
| group by year(date_value), month(date_value)
|
|
| --
| William Stacey [MVP]
|
| "Tod" <todtown@.swbell.net> wrote in message
| news:1138311375.334942.46790@.o13g2000cwo.googlegroups.com...
|| Pardon my persistant newbieness:
||
|| I have something like this:
||
|| SELECT
|| SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
|| SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
|| ELSE 0 END) AS 'Closed'
|| FROM
|| tablename
|| WHERE
|| customer = 'customer_name'
|| and date_value >= '01/01/2005' and date_value < '01/01/2006'
||
|| Now I want to group by month and show the month in "mmmm yyyy" format.
||
|| Any help?
||
|| tod
||
|
|