AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of rwallacej
rwallacej
 asked on

SQL query to count records before they change

Hi

I have a problem with SQL query.   I want to display the first record and the number of times a value is duplicated before the next uniqu record. An example of input data

date field   value         other fields.....
---------------------------------------
01:00       1234        
01:01       1234
01:02       1234
01:03       654
01:04        321
01:05        321
01:06        1234

The results I would like to display:

Date field                           Value        Count of records
(first time value occurs)
----------------------------------------
01:00                                 1234              3
01:03                                 654              1
01:04                                 321              2
01:06                                 1234            1

Not that I don't want a count of e.g. 1234 over all record set but counts as the values change

Thank-you in advance for help in SQL.
Microsoft SQL Server 2008
Avatar of undefined
Last Comment
rwallacej
8/22/2022 - Mon
rowansmith
select min(DateField), value, count(value) as theCount
from Tablename
Group By Value
order by DateField, Value, theCount
rwallacej
ASKER
hi, thank-you but I have a problme with this

select min([TIMESTAMP]), [CLR-PI353005], count([CLR-PI353005]) as theCount
from dbo.HistorianData
Group By [CLR-PI353005]
order by [TIMESTAMP], [CLR-PI353005], theCount

the error being
Column "dbo.HistorianData.TIMESTAMP" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
rwallacej
ASKER
changing the SQL slightly gives results
select min([TIMESTAMP]) as TheTime, [CLR-PI353005], count([CLR-PI353005]) as theCount
from dbo.HistorianData
Group By [CLR-PI353005]
order by TheTime, [CLR-PI353005], theCount

but not correct ones

e.g. results are

TheTime      CLR-PI353005      theCount
2008-10-12 08:00:00.000      81.06003571      2
2008-10-12 08:01:00.000      81.22103119      1
2008-10-12 08:02:00.000      79.52970123      1
2008-10-12 08:03:00.000      81.67597961      1
2008-10-12 08:04:00.000      80.71354675      2
2008-10-12 08:05:00.000      79.81403351      1
2008-10-12 08:06:00.000      81.71259308      1
2008-10-12 08:07:00.000      80.30219269      104
2008-10-12 08:08:00.000      80.12368774      1


which can't be right as the data is minutely

thanks in advance for more help
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
rwallacej
ASKER
querying
select COUNT(*) from HistorianData where
[CLR-PI353005] = 80.30219269

gives 104 if this helps in diagnosis

sorry if perhaps I didn't explain right in first instance the requirement
rwallacej
ASKER
Progress perhaps?
I have added an ID field with 1....X

ID field     date field   CLR-PI353005      other fields.....
---------------------------------------
1                 01:00       1234        
2                01:01       1234
3                 01:02       1234
4                 01:03       654
5                 01:04        321
6                 01:05        321
7                 01:06        1234


The following now selects the first instance of value i.e.

rows 1,5,7

but I don't have the counter yet.....

SELECT [InputID], [timestamp], [CLR-PI353005]
FROM [HistorianData] e
WHERE EXISTS (SELECT * FROM [HistorianData]
        WHERE e.InputID = [HistorianData].InputID - 1
        AND e.[CLR-PI353005] = [HistorianData].[CLR-PI353005]
        )

rowansmith
Sorry I have been away from the computer.

I am at abit of a loss to understand what you what to achieve.

My first post (excluding the error that you picked up).

What I have so far is that you want:

The first DateTime that a value occurs and the number of additional times that that value occurs, in which case:

select MIN(DateField) as theDate, [CLR-PI353005], COUNT([CLR-PI353005]) as theCount
from #HistorianData
group by [CLR-PI353005]
order by theDate

Will do this for you.  So I must be misunderstanding something.

please see my code below:




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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sharath S
This script will work for you. 

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

Open in new window

Sharath S
infact you don't want group id in the final select. removed that in the select clause.
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

Open in new window

rwallacej
ASKER
hi
thank-you for feedback, I have problem running it though (tried item 1)
the column name is InputID, not ID, so I renamed the items and got this error.

Msg 213, Level 16, State 1, Line 12
Column name or number of supplied values does not match table definition.

Could it be because I have "other fields" next to ,[CLR-PI353005], ?

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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
rwallacej
ASKER
rowansmith,
thank-you for help again. This is almost the solution except I want

Date field                           [CLR-PI353005]  Count of records
(first time value occurs)
----------------------------------------
01:00                                 1234              3
01:03                                 654              1
01:04                                 321              2
01:06                                 1234            1

Note here at line 1 the count of records is "3", not "4".
"4" is the count of 1234 over whole dataset, "3" is the number of times 1234 appears BEFORE changing to another value (654).
654 is displayed once (1), then it changes to 321 which is displayed twice (2), then 1234 appears (again) and is displayed once (1)

I hope I am making sense
Sharath S
yes, that is because you may have other columns in your table definition. try this.

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

Open in new window

Sharath S
i got the same ouput as you want.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwallacej
ASKER
thank-you, one (what I am sure is a minor thing todo) is to make the
[CLR-PI353005] configurable to any value
I thought this was as simple as
declare @tag varchar
set @tag = '[CLR-PI353005]'

and using @tag where [CLR-PI353005] was but I haven't got this right, I get error
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '@tag'.
thank-you
Sharath S

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

Open in new window

rwallacej
ASKER
oops I haven't explained right, where I put "configurable to any value" I meant to say "configurable to any field" e.g. user could put a different field like
CLR-PI353005, CLR-PI353006 to stored procedure
for there are many fields
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sharath S
Here you go.
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

Open in new window

Sharath S
or create an SP like this and pass column name,

exec sp_Count 'CLR-PI353005' 

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

Open in new window

ASKER CERTIFIED SOLUTION
Sharath S
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
vinurajr
After seeing all the code I thought to write my own... Please try and tell me is it usefull.....
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)

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rowansmith
How are you going with this?
Sharath S
rwallacej - vinurajr solution will not give you your expected result. check that.
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
*/

Open in new window

rwallacej
ASKER
hi Sharath_123,
apologies for acceptance of solution that does not work, I took it that it did - can a correction be made to the points?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sharath S
You can ask moderator to re-open the question again and assign the points properly.
Its not about points but do you really think that the solution (Post# 23896843) will work for you?
rwallacej
ASKER
I had thought it would work - I should have tested it thoroughly though, so I have made a mistake, if you wish the points reassigned I will open questions.
Sharath S
sure, test it and re-open the question so that you can assign the points properly.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwallacej
ASKER
thank-you kindly for your help,
regards,
rwallacej
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent