Group by month

Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #TestTry this:
SELECT DATEADD(MONTH,mth,'20000101') AS startdate,
DATEADD(MONTH,mth,'20000131') AS enddate,
person, COUNT(*) AS cnt
FROM
(SELECT DATEDIFF(MONTH,'20000101',datecreated) AS mth, person
FROM #Test) AS T
GROUP BY mth, person ;
If you want to include rows in the result for months that have no data
in your table then join the above query with a calendar table or
numbers table to generate the extra months.
David Portas
SQL Server MVP
--|||Thanx for posting DDL and INSERT's:
select
cast (convert (char (6), DateCreated, 112) + '01' as datetime) StartDate
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01')) EndDate
, Person
, count (*)
from
#Test
group by
cast (convert (char (6), DateCreated, 112) + '01' as datetime)
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01'))
, Person
order by
Person
, StartDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #Test|||Try,
-- for each year and month
SELECT
min(cast(convert(varchar(6), Datecreated, 112) + '01' as datetime)) as
Startdate,
dateadd(day, -1, dateadd(month, 1, min(cast(convert(varchar(6),
Datecreated, 112) + '01' as datetime)))) as Enddate,
Person,
count(*) as [Count]
FROM
#Test
group by
convert(varchar(6), Datecreated, 112),
Person
go
AMB
"Lasse Edsvik" wrote:

> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>|||This is pretty much the same as Tom's one, but with less conversions
SELECT
MonthAdded AS StartDate
, DATEADD( d , -1 , DATEADD( m , 1 , MonthAdded ) ) AS EndDate
, Person
, Total AS Count
FROM
(
SELECT
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 ) AS MonthAdded
, Person
, COUNT(*) AS Total
FROM
#Test
GROUP BY
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 )
, Person
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>