December 30, 2013 at 6:36 am
Hi,
I have a table where I want to return rows based on two scenarios given below :
CREATE TABLE #NewTempTable
(
StartTime DATETIME,
EndTime DATETIME,
ServerName VARCHAR(10),
Databasename VARCHAR(10),
STATUS VARCHAR(10)
)
INSERT INTO #NewTempTable ( StartTime , EndTime,ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 15:00:48.847' , '2013-12-23 15:00:48.852' , 'Server A' , 'DB A' , 'Error' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 13:00:48.847' ,'2013-12-23 13:00:48.853', 'Server A' , 'DB A' , 'SUCCESS' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 12:00:38.447' ,'2013-12-23 12:00:38.451', 'Server A' , 'DB C' , 'SKIPPED' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 16:00:28.247' ,'2013-12-23 16:00:28.249', 'Server A' , 'DB A' , 'SUCCESS' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 18:00:48.847' ,'2013-12-23 18:00:48.849', 'Server A' , 'DB B' , 'SUCCESS' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-24 18:00:48.847' ,'2013-12-24 18:00:48.849', 'Server A' , 'DB B' , 'SUCCESS' )
INSERT INTO #NewTempTable ( StartTime , EndTime,ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 12:00:48.847' , '2013-12-23 12:00:48.852' , 'Server B' , 'DB BX' , 'Missed' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 11:00:48.847' ,'2013-12-23 11:00:48.853', 'Server B' , 'DB CC' , 'SUCCESS' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 10:00:38.447' ,'2013-12-23 10:00:38.451', 'Server B' , 'DB CX' , 'SKIPPED' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-23 13:00:28.247' ,'2013-12-23 13:00:28.249', 'Server B' , 'DB AX' , 'SUCCESS' )
INSERT INTO #NewTempTable ( StartTime , EndTime, ServerName , Databasename , STATUS )
VALUES ( '2013-12-24 15:00:48.847' ,'2013-12-24 15:00:48.849', 'Server B' , 'DB BX' , 'SUCCESS' )
DECLARE @StartTime DATETIME ='12/23/2013'
DECLARE @EndTime DATETIME ='12/25/2013'
SELECT * FROM #NewTempTable WHERE StartTime >=@StartTime AND EndTime <= @EndTime
DROP TABLE #NewTempTable
--SCENARIO 1
--For a particular SERVER during the StartTime and EndTime parameter value, if status is 'Error' regardless of the MAX(EndTime) timestamp ,I want to return that row for Server A
StartTime EndTime ServerNameDatabasename
2013-12-23 15:00:48.847 2013-12-23 15:00:48.853 Server A DB A
--SCENARIO 2
--For a particular SERVER during the StartTime and EndTime parameter value, if there is no 'Error' status, then I want to return the row where EndTime is MAX for that parameter selection
StartTime EndTime ServerName Databasename STATUS
2013-12-24 15:00:48.847 2013-12-24 15:00:48.850 Server B DB BX SUCCESS
Thanks,
PSB
December 30, 2013 at 7:10 am
DROP TABLE #NewTempTable
CREATE TABLE #NewTempTable
(
StartTime DATETIME,
EndTime DATETIME,
ServerName VARCHAR(10),
Databasename VARCHAR(10),
STATUS VARCHAR(10)
)
INSERT INTO #NewTempTable (StartTime, EndTime, ServerName, Databasename, [STATUS]) VALUES
( '2013-12-23 15:00:48.847' , '2013-12-23 15:00:48.852' , 'Server A' , 'DB A' , 'Error' ),
( '2013-12-23 13:00:48.847' ,'2013-12-23 13:00:48.853', 'Server A' , 'DB A' , 'SUCCESS' ),
( '2013-12-23 12:00:38.447' ,'2013-12-23 12:00:38.451', 'Server A' , 'DB C' , 'SKIPPED' ),
( '2013-12-23 16:00:28.247' ,'2013-12-23 16:00:28.249', 'Server A' , 'DB A' , 'SUCCESS' ),
( '2013-12-23 18:00:48.847' ,'2013-12-23 18:00:48.849', 'Server A' , 'DB B' , 'SUCCESS' ),
( '2013-12-24 18:00:48.847' ,'2013-12-24 18:00:48.849', 'Server A' , 'DB B' , 'SUCCESS' ),
( '2013-12-23 12:00:48.847' ,'2013-12-23 12:00:48.852', 'Server B' , 'DB BX' , 'Missed' ),
( '2013-12-23 11:00:48.847' ,'2013-12-23 11:00:48.853', 'Server B' , 'DB CC' , 'SUCCESS' ),
( '2013-12-23 10:00:38.447' ,'2013-12-23 10:00:38.451', 'Server B' , 'DB CX' , 'SKIPPED' ),
( '2013-12-23 13:00:28.247' ,'2013-12-23 13:00:28.249', 'Server B' , 'DB AX' , 'SUCCESS' ),
( '2013-12-24 15:00:48.847' ,'2013-12-24 15:00:48.849', 'Server B' , 'DB BX' , 'SUCCESS' )
DECLARE @StartTime DATETIME ='12/23/2013'
DECLARE @EndTime DATETIME ='12/25/2013'
--SCENARIO 1
SELECT StartTime, EndTime, ServerName, Databasename
FROM #NewTempTable
WHERE StartTime >= @StartTime
AND EndTime <= @EndTime
AND [STATUS] = 'Error'
--SCENARIO 2
SELECT *
FROM (
SELECT StartTime, EndTime, ServerName, Databasename, [STATUS],
rn = ROW_NUMBER() OVER(PARTITION BY t.ServerName ORDER BY t.EndTime DESC)
FROM #NewTempTable t
WHERE t.StartTime >= @StartTime
AND t.EndTime <= @EndTime
AND NOT EXISTS (
SELECT 1
FROM #NewTempTable i
WHERE i.ServerName = t.ServerName
AND i.StartTime >= @StartTime
AND i.EndTime <= @EndTime
AND i.[STATUS] = 'Error'
)
) d
WHERE rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 30, 2013 at 7:25 am
It works for Scenario 2. In scenario 1, if there are two 'Error' status, I need to display the one with the MAX(EndTime)
INSERT INTO #NewTempTable (StartTime, EndTime, ServerName, Databasename, [STATUS]) VALUES
( '2013-12-23 15:00:48.847' , '2013-12-23 15:00:48.852' , 'Server A' , 'DB A' , 'Error' ),
( '2013-12-23 13:00:48.847' ,'2013-12-23 13:00:48.853', 'Server A' , 'DB A' , 'SUCCESS' ),
( '2013-12-23 12:00:38.447' ,'2013-12-23 12:00:38.451', 'Server A' , 'DB C' , 'SKIPPED' ),
( '2013-12-23 16:00:28.247' ,'2013-12-23 16:00:28.249', 'Server A' , 'DB A' , 'SUCCESS' ),
( '2013-12-23 18:00:48.847' ,'2013-12-23 18:00:48.849', 'Server A' , 'DB B' , 'SUCCESS' ),
( '2013-12-24 18:00:48.847' ,'2013-12-24 18:00:48.849', 'Server A' , 'DB B' , 'ERROR' )
December 30, 2013 at 7:27 am
Sure, pretty straightforward - use ROW_NUMBER() as for Scenario 2. Can you give it a shot?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 30, 2013 at 7:31 am
Got it. Thanks 🙂
December 30, 2013 at 7:38 am
PSB (12/30/2013)
Got it. Thanks 🙂
Top job! Anytime 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply