SQL Query/View

I have a table with the following fields, I need a view/query where it will  list for every account in the table, rows that start with Descr = ON TEST "THRU" Descr= OFF TEST, It needs to list all the rows in between that date and ignore all others. any help?
thank you

account      date                         descr
8073      10/05/15 17:45:24      MISC
8073      10/05/15 17:45:24      ON TEST
8073      10/05/15 17:51:32      DOOR
8073      10/05/15 17:52:52      CLOSE
8073      10/05/15 17:55:08      OPEN
8073      10/05/15 17:56:56      CLOSE
8073      10/05/15 18:08:16      OPEN
8073      10/05/15 18:10:28      CLOSE
8073      10/05/15 18:10:32      CLOSE
8073      10/05/15 18:15:02      OFF TEST
8073      10/05/15 19:10:32      CLOSE


required result sample for this one account:
8073      10/05/15 17:45:24      ON TEST
8073      10/05/15 17:51:32      DOOR
8073      10/05/15 17:52:52      CLOSE
8073      10/05/15 17:55:08      OPEN
8073      10/05/15 17:56:56      CLOSE
8073      10/05/15 18:08:16      OPEN
8073      10/05/15 18:10:28      CLOSE
8073      10/05/15 18:10:32      CLOSE
8073      10/05/15 18:15:02      OFF TEST
Pete EAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Pete,
Just giving you 2 more cases to know how to perform when this kind of records found:
-- CASE 1 --> No OFF TEST
account      date                         descr
 8073      10/05/15 17:45:24      MISC
 8073      10/05/15 17:45:24      ON TEST
 8073      10/05/15 17:51:32      DOOR
 8073      10/05/15 17:52:52      CLOSE
 8073      10/05/15 17:55:08      OPEN
 8073      10/05/15 17:56:56      CLOSE
 8073      10/05/15 18:08:16      OPEN
 8073      10/05/15 18:10:28      CLOSE

-- CASE 2 --> Two groups of tests
account      date                         descry
8073      10/05/15 17:45:24      MISC
 8073      10/05/15 17:45:24      ON TEST
 8073      10/05/15 17:51:32      DOOR
 8073      10/05/15 17:52:52      CLOSE
 8073      10/05/15 17:55:08      OPEN
 8073      10/05/15 17:56:56      CLOSE
 8073      10/05/15 18:08:16      OPEN
 8073      10/05/15 18:10:28      CLOSE
 8073      10/05/15 18:10:32      CLOSE
 8073      10/05/15 18:15:02      OFF TEST
 8073      10/05/15 19:10:32      CLOSE
 8073      10/05/15 19:45:24      MISC
 8073      10/05/15 19:45:24      ON TEST
 8073      10/05/15 19:51:32      DOOR
 8073      10/05/15 19:52:52      CLOSE
 8073      10/05/15 19:55:08      OPEN
 8073      10/05/15 19:56:56      CLOSE
 8073      10/05/15 20:08:16      OPEN
 8073      10/05/15 20:10:28      CLOSE
1
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can try this one.

;WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY ACCOUNT ORDER BY ACCOUNT,ACCDATE) RN FROM [dbo].[ExpertTables]
)
,CTENEW AS
(
SELECT *
,(SELECT TOP 1 RN FROM CTE C1 WHERE C1.ACCOUNT = C22.ACCOUNT AND Description = 'ON TEST' ORDER BY RN)  SLIMIT
,(SELECT TOP 1 RN FROM CTE C2 WHERE C2.ACCOUNT = C22.ACCOUNT AND Description = 'OFF TEST' ORDER BY RN DESC) ELIMIT
FROM CTE C22 
)

SELECT ACCOUNT,AccDate,Description FROM CTENEW 
WHERE RN BETWEEN SLIMIT AND ELIMIT

Open in new window

0
Pete EAuthor Commented:
Vikas Garg getting this when trying your code.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Pls check this

USE [CDCTest]
GO
/****** Object:  Table [dbo].[ExpertTables]    Script Date: 10/31/2015 9:22:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExpertTables](
	[ACCOUNT] [int] NULL,
	[AccDate] [datetime] NULL,
	[Description] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A52800107AC0 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A5280010C110 AS DateTime), N'ON TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A52800110760 AS DateTime), N'     DOOR')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A52800114DB0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A52800119400 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A5280011DA50 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A528001220A0 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A528001266F0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A5280012AD40 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A5280012F390 AS DateTime), N'OFF TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8073, CAST(0x0000A528001339E0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A52800107AC0 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A5280010C110 AS DateTime), N'ON TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A52800110760 AS DateTime), N'     DOOR')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A52800114DB0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A52800119400 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A5280011DA50 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A528001220A0 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A528001266F0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A5280012AD40 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A5280012F390 AS DateTime), N'OFF TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8074, CAST(0x0000A528001339E0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A52800107AC0 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A5280010C110 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A52800110760 AS DateTime), N'ON TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A52800114DB0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A52800119400 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A5280011DA50 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A528001220A0 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A528001266F0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A5280012AD40 AS DateTime), N'OFF TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A5280012F390 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8088, CAST(0x0000A528001339E0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A52800107AC0 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A5280010C110 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A52800110760 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A52800114DB0 AS DateTime), N'     MISC')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A52800119400 AS DateTime), N'ON TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A5280011DA50 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A528001220A0 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A528001266F0 AS DateTime), N'OFF TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A5280012AD40 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A5280012F390 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8989, CAST(0x0000A528001339E0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A5280010C110 AS DateTime), N'ON TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A52800110760 AS DateTime), N'ON TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A52800114DB0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A52800119400 AS DateTime), N'     OPEN')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A5280011DA50 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A528001220A0 AS DateTime), N'OFF TEST')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A528001266F0 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A5280012AD40 AS DateTime), N'     CLOSE')
GO
INSERT [dbo].[ExpertTables] ([ACCOUNT], [AccDate], [Description]) VALUES (8001, CAST(0x0000A5280012F390 AS DateTime), N'OFF TEST')
GO


;WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY ACCOUNT ORDER BY ACCOUNT,ACCDATE) RN FROM [dbo].[ExpertTables]
)
,CTENEW AS
(
SELECT *
,(SELECT TOP 1 RN FROM CTE C1 WHERE C1.ACCOUNT = C22.ACCOUNT AND Description = 'ON TEST' ORDER BY RN)  SLIMIT
,(SELECT TOP 1 RN FROM CTE C2 WHERE C2.ACCOUNT = C22.ACCOUNT AND Description = 'OFF TEST' ORDER BY RN DESC) ELIMIT
FROM CTE C22 
)

SELECT ACCOUNT,AccDate,Description FROM CTENEW 
WHERE RN BETWEEN SLIMIT AND ELIMIT

                                          

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.