report to check the result for 10 consucutive minutes
-
Frank Kalis gave me some code off-line that might help. I am rather thick-skulled, so he had to give two versions - the use of modulo made more sense to me.
If this helps, thank Frank:
--Farrell, you can either use '19000101' or 0.
CREATE TABLE #time( [ID] INT IDENTITY,
dt DATETIME,
ReasonID INT)
INSERT INTO #time
SELECT '20050427 10:50:00', NULL -- 1
UNION ALL
SELECT '20050427 10:51:00', NULL -- 2
UNION ALL
SELECT '20050427 10:52:00', NULL -- 3
UNION ALL
SELECT '20050427 10:53:00', 20206 -- 4
UNION ALL
SELECT '20050427 10:54:00', 20206 -- 5
UNION ALL
SELECT '20050427 10:55:00', NULL -- 6
UNION ALL
SELECT '20050427 10:56:00', 20212 -- 7
SELECT CONVERT( varchar(1), [ID]) AS [ID],
DATEADD( minute, -DATEPART( minute, dt) % 5, dt) AS DateByFiveSeconds,
-DATEPART( minute, dt) % 5 AS Modulo,
dt
FROM #time
DROP TABLE #time
-- Both will produce the same result. The former is interpreted by SQL Server as a date by converting the string to a DATETIME.
-- The latter is converted from an INT to DATETIME. In both cases it's the server's base date.
-- Basically it's the same trick you can use to set the time in a DATETIME column to midnight.
-- This technique works with all the allowed parameters for DATEADD and DATEDIFF.
-- Here it strips off the seconds and milliseconds.
/*
SELECT DATEADD( minute, DATEDIFF( minute, 0, dt) / 5 * 5, 0),
COUNT(*) - SUM( CASE WHEN ReasonID IS NULL THEN 0 ELSE 1 END) AS Running,
COUNT(*) - SUM( CASE WHEN ReasonID IS NOT NULL THEN 0 ELSE 1 END) AS Down
FROM #time
GROUP BY DATEADD( minute, DATEDIFF( minute, 0, dt) / 5 * 5, 0)
*/
I wasn't born stupid - I had to study.
-
Is this solution working for you or I still need to knock it out?
-
I am still working with the above hint.Not successful so far.
-
Id
----
7513
7514
7515
7516
7517
7518
7519
7520
7521
7522
7523
7524
7525
7526
7527
7528
7529
7530
7531
7532
7533
7534
7535
7536
7537
7538
7539
----
7568
7569
7570
7571
7572
7573
7574
7575
7576
7577
7578
7579
7580
7581
7582
7583
7584
7585
7586
7587
7588
7589
7590
7591
7592
7593
7594
7595
7596
----
7688
----
7695
7696
7697
7698
-----
7700
-----
IF I have these Ids with me and the data between the two dashed lines is a series of contiguius data.
how can I get the following result. Starting and ending ids of the series.
7539-7513
7596-7568
7688-7688
7698-7695
7700-7700
Thanks.
-
-- create table ids( n int)
-- insert into ids (n) values (7535)
-- insert into ids (n) values (7536)
-- insert into ids (n) values (7537)
-- insert into ids (n) values (7538)
-- insert into ids (n) values (7539)
-- insert into ids (n) values (7568)
-- insert into ids (n) values (7569)
-- insert into ids (n) values (7570)
-- insert into ids (n) values (7571)
-- insert into ids (n) values (7572)
-- insert into ids (n) values (7573)
-- insert into ids (n) values (7574)
-- insert into ids (n) values (7575)
-- insert into ids (n) values (7576)
-- insert into ids (n) values (7577)
-- insert into ids (n) values (7578)
-- insert into ids (n) values (7579)
-- insert into ids (n) values (7580)
-- insert into ids (n) values (7581)
-- insert into ids (n) values (7582)
-- insert into ids (n) values (7583)
-- insert into ids (n) values (7584)
-- insert into ids (n) values (7585)
-- insert into ids (n) values (7586)
-- insert into ids (n) values (7587)
-- insert into ids (n) values (7588)
-- insert into ids (n) values (7589)
-- insert into ids (n) values (7590)
-- insert into ids (n) values (7591)
-- insert into ids (n) values (7592)
-- insert into ids (n) values (7593)
-- insert into ids (n) values (7594)
-- insert into ids (n) values (7595)
-- insert into ids (n) values (7596)
-- insert into ids (n) values (7688)
-- insert into ids (n) values (7695)
-- insert into ids (n) values (7696)
-- insert into ids (n) values (7697)
-- insert into ids (n) values (7698)
-- insert into ids (n) values (7700)
select start
,(select min(i3.n)
from ids i3
where i3.n >= n1.start
and not exists (select* from ids i4 where i4.n = i3.n +1 )) as [end]
from
(
select n as start
from ids i1
where
not exists (select *
from ids i2
where i2.n = i1.n -1)
) n1
hth
* Noel -
Looking at the required result
number name result start_date
89277 xxxx no 8/11/05 5:12 AM
89287 xxxx no 8/11/05 5:22 AM
86405 xxxx yes 8/9/05 5:01 AM
90000 xxxx no 9/9/04 5:15 AM
Should the last record be there? The date range for that set of 10 is 9/9/04 to 9/15/04... I do see that the times are consecutive though.
-
num name
7534 tt
7535 tt
7536 tt
7537 nn
7538 nn
7539 tt
From the above data set how can get the following result
start end name
7534 7536 tt
7537 7538 nn
7539 7539 tt
How can I get the above resukt.
Thanks.
-
sahana smthing similar :
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=191316#bm191447
Kindest Regards,Vasc
-
I'm still looking at it and I still don't get it... can you share the idea behind this technic?
-
I m using pair of consecutive rows from a self join
row1 will pair with row2
and I extract the rows where is a variance in group value
for example from 50 to 100 or 50 to null
and by picking the 2 rows that do the variance I obtain in 1 case the min from a group and afther that the max for a group (in the two derived tables)
after this I join the tables and I eliminate the unwanted rows MIN> MAX and by selecting the pairs with the same MAX that has the MAX(MIN) : )) otherwise it means that the pair is not good
: )
Kindest Regards,Vasc
-
Looks like I didn't go to school long enough
.Thanx for the tip.
-
Still I could not make the solution with ur tip. Can anyone give the straight solution to my problem.
Thanks.
-
Sahana can you post your table + sample data (declare @t table....
insert so I just copy in queryAnalixzer : )
and the result that you want
Kindest Regards,Vasc
-
create table #ids( n int,name varchar(20))
insert into #ids (n,name) values (7534,'tt')
insert into #ids (n,name) values (7535,'tt')
insert into #ids (n,name) values (7536,'tt')
insert into #ids (n,name) values (7537,'nn')
insert into #ids (n,name) values (7538,'nn')
insert into #ids (n,name) values (7539,'tt')
Start end COunt name
7534 7536 3 tt
7537 7538 2 nn
7539 7539 1 tt
-
create table #ids( n int,name varchar(20))
insert into #ids (n,name) values (7534,'tt')
insert into #ids (n,name) values (7535,'tt')
insert into #ids (n,name) values (7536,'tt')
insert into #ids (n,name) values (7537,'nn')
insert into #ids (n,name) values (7538,'nn')
insert into #ids (n,name) values (7539,'tt')
/*
Start end COunt name
7534 7536 3 tt
7537 7538 2 nn
7539 7539 1 tt
*/
SELECT X.name,MAX(Y.n),X.n,X.n-MAX(Y.n)+1 as Count
FROM
(SELECT a.n,a.name
FROM #ids a LEFT OUTER JOIN #ids b
ON a.name=b.name AND a.n=b.n-1
WHERE b.name IS NULL
) X
INNER JOIN
(SELECT a.n,a.name
FROM #ids a LEFT OUTER JOIN #ids b
ON a.name=b.name AND a.n=b.n+1
WHERE b.name IS NULL
) Y
ON X.name=Y.name
WHERE Y.n<=X.n
GROUP BY X.name,X.n
DROP TABLE #ids
Kindest Regards,Vasc
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply