Group by Month

26 Jan 2006 9:36 PM

Tod

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

26 Jan 2006 9:44 PM

Anith Sen

See 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

26 Jan 2006 10:02 PM

William Stacey [MVP]

group by year(date_value), month(date_value)

--
William Stacey [MVP]

Show quote

26 Jan 2006 10:13 PM

William Stacey [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]

Show quote

"William Stacey [MVP]" <william.sta***@gmail.com> wrote in message
news:OPHi1QsIGHA.3936@TK2MSFTNGP12.phx.gbl...
| group by year(date_value), month(date_value)
|
|
| --
| William Stacey [MVP]
|
| "Tod" <todt***@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
||
|
|

26 Jan 2006 10:03 PM

Mark Williams

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) )

--

Show quote

"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
>
>

26 Jan 2006 10:10 PM

Hugo Kornelis

On 26 Jan 2006 13:36:15 -0800, Tod wrote:

Show quote

>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