Grouping by hour, day, month, etc

I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--