CODEWITHSUNDEEP
sql
Kevin M
Kevin M

Reputation: 5496

How to write SQL query for the following case.?

I have one Change Report Table which has two columns ChangedTime,FileName enter image description here

Please consider this table has over 1000 records

Here I need to query all the changes based on following factors

i) Interval (i.e-1mins )
ii) No of files

It means when we have given Interval 1 min and No Of files 10. If the the no of changed files more than 10 in any of the 1 minute interval, we need to get all the changed files exists in that 1 minute interval

Example:

  i) Consider we have 15 changes in the interval 11:52 to 11:53
  ii)And consider we have 20 changes in the interval 12:58 to 12:59

Now my expected results would be 35 records.

Thanks in advance.

Upvotes: 0

Views: 145

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You need to aggregate by the interval and then do the count. Assuming that an interval starting at time 0 is ok, the following should work:

declare @interval int = 1;
declare @limit int = 10;

select sum(cnt)
from (select count(*) as cnt
      from t
      group by DATEDIFF(minute, 0, ChangedTime)/@interval
     ) t
where cnt >= @limit;

If you have another time in mind for when intervals should start, then substitute that for 0.

EDIT:

For your particular query:

select sum(ChangedTime)
from (select count(*) as ChangedTime
  from [MyDB].[dbo].[Log_Table.in_PC] 
  group by DATEDIFF(minute, 0, ChangedTime)/@interval
 ) t
where ChangedTime >= @limit;

You can't have a three part alias name on a subquery. t will do.

Upvotes: 1

chetan
chetan

Reputation: 2886

select count(*) from (select a.FileName, 
b.ChangedTime startTime, 
a.ChangedTime endTime, 
DATEDIFF ( minute , a.ChangedTime , b.ChangedTime ) timeInterval
from yourtable a, yourtable b
where a.FileName = b.FileName
and a.ChangedTime > b.ChangedTime
and DATEDIFF ( minute , a.ChangedTime , b.ChangedTime ) = 1) temp
group by temp.FileName

Upvotes: 0

Datux
Datux

Reputation: 1

Something like this should work: You count the number of records using the COUNT() function. Then you limit the selection with the WHERE clause:

SELECT COUNT(FileName) 
FROM "YourTable" 
WHERE ChangedTime >= "StartInteval" 
  AND ChangedTime <= "EndInterval";

Another method that is useful in a where clause is BETWEEN : http://msdn.microsoft.com/en-us/library/ms187922.aspx.

You didn't state which SQL DB you are using so I assume its MSSQL.

Upvotes: 0

Related Questions