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

grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

group data by weeks

I have data entered into a table using a datetime field. How can I group the data one week at a time and show mulitple weeks at a time?Look at the datepart function.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx|||

What do you mean by showing multiple weeks at a time?

Lets assume you have columns Cost as currency and BoughtOn as date

SELECT COUNT(Cost) as Items, SUM(Cost) As TotalPrice, DATEPART(wk, BoughtOn) As Week FROM yourTable GROUP BY DATEPART(wk, BoughtOn)

Is that what you need?

--
SvenC

|||

I'm trying for something like this:

Name

Week End Date

Product1

Product2

Porduct3

Rep1

9/9/2006

1130

331

Rep1

9/16/2006

130

3021

1452

Rep1

9/23/2006

1351

3513

1542

What you have above is very close SvenC.

|||

Can you show your table definition or definitions from which the above result set should come?

--
SvenC

|||

Here is the sql I am using now:

SELECT dbo.sales_rep.sales_rep_first_name + ' ' + dbo.sales_rep.sales_rep_last_name AS Name, dbo.sale.sale_dts, dbo.sale.sale_type,

SUM(dbo.sale.total_pt_of_sale_amt) AS POS, DATEPART(wk, sale_dts) As Week

FROM dbo.sale INNER JOIN

dbo.sales_rep ON dbo.sale.sales_rep_user_id = dbo.sales_rep.sales_rep_user_id

WHERE (dbo.sale.sale_dts BETWEEN @.start AND @.end) and sale_type in ('qqq', 'ttt', 'fff', '11111)

and dbo.sales_rep.sales_rep_user_id in ('id123','id1234','id2151','id5214')

GROUP BY DATEPART(wk, sale_dts), dbo.sale.sale_dts, dbo.sales_rep.sales_rep_last_name, dbo.sales_rep.sales_rep_first_name, dbo.sale.sale_type, dbo.sale.total_pt_of_sale_amt

The sale table is

sale_id int
sales_rep_user_id varchar(7)
sale_dts datetime
sale_type varchar(10)
total_pt_of_sale_amt

|||And what results do you get with that?

Do you need to concatenate the week datepart with the year datepart to get uniqueness for a given week?|||

I get data like this:

Rep Name

Date

Type

POS

Week

|||So you don't get any data?|||

Sorry, didn't know you wanted to see the data:

Rep Name

Date

Type

POS

Week

RepName1

9/5/2006

Product1

1731

36

RepName1

9/5/2006

Product1

216

36

RepName1

9/5/2006

Prodcut2

240

36

RepName1

9/5/2006

Product1

1960

36

RepName1

9/5/2006

Prodcut2

15000

36

RepName2

9/5/2006

Product1

120

36

RepName2

9/5/2006

Prodcut2

600

36

RepName2

9/6/2006

Product1

800

36

RepName2

9/6/2006

Product1

1680

36

RepName2

9/6/2006

Product1

168

36

RepName2

9/6/2006

Product1

348

36

|||That helps.

Now, with your data, where do you want to go? What should the data look like?|||

I'd like to had the date listed as the Satuday of the week. For example: 9/9/2006.

Something like this:

Rep Name	Date	Product1	Product2	Product3
Rep1	9/9/2006	1302	1731	3020
Rep1	10/14/2006	680	311	3151
Rep1	10/21/2006	21001	15000	36
Rep2	10/21/2006	0	3033	11220
Rep3	9/9/2006	1	1002	12125
Rep3	9/16/2006	3320	2111	3601
Rep3	9/23/2006	12102	2210	1101
Rep3	10/14/2006	2151	3264	36

Where there is one record for each rep per week.

|||So you want to pivot your data in a query? That is to say you want dynamic columns? So you'll have as many product columns as the max(type) per sales rep?

Oh boy...|||Pivot the data, yes. However I'm only looking for four (4) sale types and five (5) sales reps.|||Well, try this.

select repname, weekend, sum(Product1Col), sum(Product2Col), sum(Product3Col), sum(Product4Col)
from (
select [repname], 'weekend' = case datepart("dw",[YourDateField])
when 1 then dateadd("dd",6,[YourDateField])
when 2 then dateadd("dd",5,[YourDateField])
when 3 then dateadd("dd",4,[YourDateField])
when 4 then dateadd("dd",3,[YourDateField])
when 5 then dateadd("dd",2,[YourDateField])
when 6 then dateadd("dd",1,[YourDateField])
when 7 then [YourDateField]
end,
'Product1Col' = case [type]
when 'Product1' then [POS]
else 0
end,
'Product2Col' = case [type]
when 'Product2' then [POS]
else 0
end,
'Product3Col' = case [type]
when 'Product3' then [POS]
else 0
end,
'Product4Col' = case [type]
when 'Product4' then [POS]
else 0
end
from table
where [repname] in ('Rep1','Rep2','Rep3','Rep4','Rep5')
) GROUP BY [repname], [weekend]

Group by Top # entered in as Parameter

Background: I have a report that groups by Item number and gives adds
up total amount for that item number. What I want to do is have the
user enter in a numeric value as a parameter such as 10, 15, 20, etc
that will then only display the TOP 10, 15, 20, etc (what they entered
in the parameter) total amounts on the report. Can anyone help me out,
Im sure this can be done but it gets tricky with the parameters thrown
in the mix. Any suggestions is much appreciated. Thanks!hi brent
you can do this w/o issue by using a stored procedure as the source dataset
(and having your 'TOP' value included as one of the parameters).
next, you are going to need to supply a dataset for the dropdown:
select '10' as topval
union
select '20' as topval
union
select '3....
if you plan on 'rolling your own' ASP.NET interface, you can preload the
values for the dropdown in HTML.
Rob
"Brent" wrote:
> Background: I have a report that groups by Item number and gives adds
> up total amount for that item number. What I want to do is have the
> user enter in a numeric value as a parameter such as 10, 15, 20, etc
> that will then only display the TOP 10, 15, 20, etc (what they entered
> in the parameter) total amounts on the report. Can anyone help me out,
> Im sure this can be done but it gets tricky with the parameters thrown
> in the mix. Any suggestions is much appreciated. Thanks!
>