CREATE TABLE #HistorianData(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DateField] [datetime] NOT NULL,
[CLR-PI353005] [int] NOT NULL
)
INSERT INTO #HistorianData (DateField, [CLR-PI353005])
VALUES('15-MAR-2009 01:00:00',1234),
('15-MAR-2009 01:01:00',1234),
('15-MAR-2009 01:02:00',1234),
('15-MAR-2009 01:03:00',654),
('15-MAR-2009 01:04:00',321),
('15-MAR-2009 01:05:00',321),
('15-MAR-2009 01:06:00',1234)
select MIN(DateField) as theDate, [CLR-PI353005], COUNT([CLR-PI353005]) as theCount
from #HistorianData
group by [CLR-PI353005]
order by theDate
DROP TABLE #HistorianData
Produces:
=========
theDate CLR-PI353005 theCount
2009-03-15 01:00:00.000 1234 4
2009-03-15 01:03:00.000 654 1
2009-03-15 01:04:00.000 321 2
declare @var1 int,@var2 int,@GroupId int,@Pre int
set @GroupId = 1
create table #temp(ID int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(ID) from HistorianData
select @var2 = MAX(ID) from HistorianData
insert #temp select ID,[timestamp],[CLR-PI353005] ,@GroupId from HistorianData where ID = @var1
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
if (select [CLR-PI353005] from HistorianData where ID = @Pre) = (select [CLR-PI353005] from HistorianData where ID = @var1)
insert #temp select *,@GroupId from HistorianData where ID = @var1
else
begin
set @GroupId = @GroupId + 1
insert #temp select *,@GroupId from HistorianData where ID = @var1
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select GroupId,[CLR-PI353005],MIN(timestamp),COUNT(*) from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
declare @var1 int,@var2 int,@GroupId int,@Pre int
set @GroupId = 1
create table #temp(ID int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(ID) from HistorianData
select @var2 = MAX(ID) from HistorianData
insert #temp select ID,[timestamp],[CLR-PI353005] ,@GroupId from HistorianData where ID = @var1
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
if (select [CLR-PI353005] from HistorianData where ID = @Pre) = (select [CLR-PI353005] from HistorianData where ID = @var1)
insert #temp select *,@GroupId from HistorianData where ID = @var1
else
begin
set @GroupId = @GroupId + 1
insert #temp select *,@GroupId from HistorianData where ID = @var1
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select MIN([timestamp]) as DateField,[CLR-PI353005],COUNT(*) from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
declare @var1 int,@var2 int,@GroupId int,@Pre int
set @GroupId = 1
create table #temp(inputid int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(inputid) from HistorianData
select @var2 = MAX(inputid) from HistorianData
insert #temp select inputid,[timestamp],[CLR-PI353005] ,@GroupId from HistorianData where inputid = @var1
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
if (select [CLR-PI353005] from HistorianData where inputid = @Pre) = (select [CLR-PI353005] from HistorianData where inputid = @var1)
insert #temp select *,@GroupId from HistorianData where inputid = @var1
else
begin
set @GroupId = @GroupId + 1
insert #temp select *,@GroupId from HistorianData where inputid = @var1
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select GroupId,[CLR-PI353005],MIN(timestamp),COUNT(*) from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
declare @var1 int,@var2 int,@GroupId int,@Pre int
set @GroupId = 1
create table #temp(inputid int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(inputid) from HistorianData
select @var2 = MAX(inputid) from HistorianData
insert #temp select inputid,[timestamp],[CLR-PI353005] ,@GroupId from HistorianData where inputid = @var1
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
if (select [CLR-PI353005] from HistorianData where inputid = @Pre) = (select [CLR-PI353005] from HistorianData where inputid = @var1)
insert #temp select inputid,[timestamp],[CLR-PI353005],@GroupId from HistorianData where inputid = @var1
else
begin
set @GroupId = @GroupId + 1
insert #temp select inputid,[timestamp],[CLR-PI353005],@GroupId from HistorianData where inputid = @var1
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select GroupId,[CLR-PI353005],MIN(timestamp),COUNT(*) from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
declare @var1 int,@var2 int,@GroupId int,@Pre int
set @GroupId = 1
create table #temp(inputid int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(inputid) from HistorianData
select @var2 = MAX(inputid) from HistorianData
insert #temp select inputid,[timestamp],[CLR-PI353005] ,@GroupId from HistorianData where inputid = @var1
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
if (select [CLR-PI353005] from HistorianData where inputid = @Pre) = (select [CLR-PI353005] from HistorianData where inputid = @var1)
insert #temp select inputid,[timestamp],[CLR-PI353005],@GroupId from HistorianData where inputid = @var1
else
begin
set @GroupId = @GroupId + 1
insert #temp select inputid,[timestamp],[CLR-PI353005],@GroupId from HistorianData where inputid = @var1
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select convert(varchar(8),MIN(timestamp),8) as Date_Field,[CLR-PI353005],COUNT(*) as Rec_Count from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
/*
Date_Field CLR-PI353005 Rec_Count
01:00:00 1234 3
01:03:00 654 1
01:04:00 321 2
01:06:00 1234 1
*/
declare @var1 int,@var2 int,@GroupId int,@Pre int
declare @SQL nvarchar(1000),@Column nvarchar(500),@Res1 int,@Res2 int
set @GroupId = 1
set @Column = 'CLR-PI353005' --change this to what ever column name you want.
set @Column = '['+@Column+']'
create table #temp(inputid int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(inputid) from HistorianData
select @var2 = MAX(inputid) from HistorianData
set @SQL = 'insert #temp select inputid,[timestamp],' + @Column + ',' + convert(varchar(10),@GroupId) +
' from HistorianData where inputid = ' + convert(varchar(10),@var1)
exec(@SQL)
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
set @SQL = N'select @Result = ' + @Column + ' from HistorianData where InputID = ' + convert(varchar(10),@Pre)
exec sp_executesql @SQL,N'@Result int output',@Result = @Res1 output
set @SQL = N'select @Result = ' + @Column + ' from HistorianData where InputID = ' + convert(varchar(10),@var1)
exec sp_executesql @SQL,N'@Result int output',@Result = @Res2 output
if (@Res1 = @Res2)
begin
set @SQL = 'insert #temp select inputid,[timestamp],' + @Column + ',' + convert(varchar(10),@GroupId) +
' from HistorianData where inputid = ' + convert(varchar(10),@var1)
exec(@SQL)
end
else
begin
set @GroupId = @GroupId + 1
set @SQL = 'insert #temp select inputid,[timestamp],' + @Column + ',' + convert(varchar(10),@GroupId) +
' from HistorianData where inputid = ' + convert(varchar(10),@var1)
exec(@SQL)
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select convert(varchar(8),MIN(timestamp),8) as Date_Field,[CLR-PI353005],COUNT(*) as Rec_Count from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
create procedure sp_Count
@Column nvarchar(50) as
declare @var1 int,@var2 int,@GroupId int,@Pre int
declare @SQL nvarchar(1000),@Res1 int,@Res2 int
set @GroupId = 1
set @Column = '['+@Column+']'
create table #temp(inputid int,[timestamp] datetime,[CLR-PI353005] int,GroupId int)
select @var1 = MIN(inputid) from HistorianData
select @var2 = MAX(inputid) from HistorianData
set @SQL = 'insert #temp select inputid,[timestamp],' + @Column + ',' + convert(varchar(10),@GroupId) +
' from HistorianData where inputid = ' + convert(varchar(10),@var1)
exec(@SQL)
set @Pre = @var1
set @var1 = @var1 + 1
while @var1 <= @var2
begin
set @SQL = N'select @Result = ' + @Column + ' from HistorianData where InputID = ' + convert(varchar(10),@Pre)
exec sp_executesql @SQL,N'@Result int output',@Result = @Res1 output
set @SQL = N'select @Result = ' + @Column + ' from HistorianData where InputID = ' + convert(varchar(10),@var1)
exec sp_executesql @SQL,N'@Result int output',@Result = @Res2 output
if (@Res1 = @Res2)
begin
set @SQL = 'insert #temp select inputid,[timestamp],' + @Column + ',' + convert(varchar(10),@GroupId) +
' from HistorianData where inputid = ' + convert(varchar(10),@var1)
exec(@SQL)
end
else
begin
set @GroupId = @GroupId + 1
set @SQL = 'insert #temp select inputid,[timestamp],' + @Column + ',' + convert(varchar(10),@GroupId) +
' from HistorianData where inputid = ' + convert(varchar(10),@var1)
exec(@SQL)
end
set @Pre = @var1
set @var1 = @var1 + 1
end
select convert(varchar(8),MIN(timestamp),8) as Date_Field,[CLR-PI353005],COUNT(*) as Rec_Count from #temp group by GroupId,[CLR-PI353005] order by GroupId
drop table #temp
declare @table table(dat datetime,clr int)
insert into @table
select '2009-03-16 17:49:49.160','1234'
union all
select '2009-03-16 17:49:49.160','1235'
union all
select '2009-03-16 17:50:49.160','1235'
union all
select '2009-03-16 17:51:49.160','1234'
union all
select '2009-03-16 17:53:49.160','1232'
union all
select '2009-03-16 17:49:49.160','1232'
union all
select '2009-03-16 17:51:49.160','1231'
union all
select '2009-03-16 17:50:49.160','1230'
select min(dat),clr,count(clr) from @table
group by clr
order by clr,min(dat)
declare @table table(dat datetime,clr int)
insert into @table
select '2009-03-16 17:49:49.160','1234'
union all
select '2009-03-16 17:49:49.160','1235'
union all
select '2009-03-16 17:50:49.160','1235'
union all
select '2009-03-16 17:51:49.160','1234'
union all
select '2009-03-16 17:53:49.160','1232'
union all
select '2009-03-16 17:49:49.160','1232'
union all
select '2009-03-16 17:51:49.160','1231'
union all
select '2009-03-16 17:50:49.160','1230'
select min(dat),clr,count(clr) from @table
group by clr
order by clr,min(dat)
/*
(No column name) clr (No column name)
2009-03-16 17:50:49.160 1230 1
2009-03-16 17:51:49.160 1231 1
2009-03-16 17:49:49.160 1232 2
2009-03-16 17:49:49.160 1234 2
2009-03-16 17:49:49.160 1235 2
*/
from Tablename
Group By Value
order by DateField, Value, theCount