USE [master]
GO

/****** Object:  Database [ConsecutiveReadTest]    Script Date: 1/26/2022 1:59:09 PM ******/
CREATE DATABASE [ConsecutiveReadTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ConsecutiveReadTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ConsecutiveReadTest.mdf' , SIZE = 10 , MAXSIZE = 50, FILEGROWTH = 5 )
 LOG ON 
( NAME = N'ConsecutiveReadTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ConsecutiveReadTest_log.ldf' , SIZE = 10 , MAXSIZE = 50 , FILEGROWTH = 5 )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ConsecutiveReadTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [ConsecutiveReadTest] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET ARITHABORT OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [ConsecutiveReadTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [ConsecutiveReadTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET  DISABLE_BROKER 
GO

ALTER DATABASE [ConsecutiveReadTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [ConsecutiveReadTest] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET RECOVERY FULL 
GO

ALTER DATABASE [ConsecutiveReadTest] SET  MULTI_USER 
GO

ALTER DATABASE [ConsecutiveReadTest] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [ConsecutiveReadTest] SET DB_CHAINING OFF 
GO

ALTER DATABASE [ConsecutiveReadTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO

ALTER DATABASE [ConsecutiveReadTest] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO

ALTER DATABASE [ConsecutiveReadTest] SET DELAYED_DURABILITY = DISABLED 
GO

ALTER DATABASE [ConsecutiveReadTest] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO

ALTER DATABASE [ConsecutiveReadTest] SET QUERY_STORE = OFF
GO

ALTER DATABASE [ConsecutiveReadTest] SET  READ_WRITE 
GO


USE [ConsecutiveReadTest]
GO

/****** Object:  Table [OWCE].[GasModuleBatteryHistory]    Script Date: 1/26/2022 2:01:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableA](
	[UpdateTime] [datetime] NOT NULL,
	[CurrentVoltage] [numeric](7, 5) NULL,
	[ModuleId] [bigint] NOT NULL,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
	[UpdateTime] ASC,
	[ModuleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


USE [ConsecutiveReadTest]
GO

/****** Object:  Table [OWCE].[ZigbeeResponse]    Script Date: 1/26/2022 2:05:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableB](
	[InterrogationDate] [datetime] NOT NULL,
	[SerialNumber] [numeric](20, 0) NOT NULL,
	[Response] [tinyint] NOT NULL,
 CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED 
(
	[InterrogationDate] ASC,
	[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into TableA
(ModuleId, UpdateTime, CurrentVoltage)
values
(67115833,	'2019-09-08 09:41:00.000',	2.79996),
(67115833,	'2019-10-11 08:16:32.000',	2.79996),
(67115833,	'2020-01-06 09:23:10.000',	2.79996),
(67115833,	'2020-08-14 09:07:31.000',	2.79996),
(67115833,	'2020-10-03 10:33:36.000',	2.79996),
(67115833,	'2021-01-06 08:33:20.000',	2.79996),
(67115833,	'2021-04-15 10:12:29.000',	2.79996),
(67115833,	'2021-07-04 09:28:46.000',	2.79996),
(67115833,	'2021-10-28 08:50:50.000',	2.79996),
(67122344,	'2019-09-06 09:06:41.000',	3.10762),
(67122344,	'2019-10-03 09:38:16.000',	3.11402),
(67122344,	'2020-01-03 10:40:46.000',	3.08197),
(67122344,	'2020-08-12 07:14:59.000',	3.10121),
(67122344,	'2020-10-06 09:52:31.000',	3.12684),
(67122344,	'2021-01-02 09:12:02.000',	3.09159),
(67122344,	'2021-04-05 08:37:45.000',	3.11082),
(67122344,	'2021-07-05 10:31:08.000',	3.08839),
(67122344,	'2021-10-26 10:12:32.000',	3.11402),
(67328997,	'2019-09-06 07:25:09.000',	3.07237),
(67328997,	'2019-10-03 08:50:37.000',	3.06916),
(67328997,	'2020-01-03 08:09:49.000',	3.04994),
(67328997,	'2020-08-12 08:53:00.000',	3.07237),
(67328997,	'2020-10-05 07:50:59.000',	3.07557),
(67328997,	'2021-01-03 09:37:25.000',	3.05634),
(67328997,	'2021-04-04 09:01:03.000',	3.05634),
(67328997,	'2021-07-02 07:32:12.000',	3.06916),
(67328997,	'2021-10-26 10:01:43.000',	3.06916),
(67328997,	'2021-11-03 09:37:25.000',	3.05634),
(67328997,	'2021-12-04 09:01:03.000',	3.05634);
go
insert into TableB
(InterrogationDate, SerialNumber, Response)
values
('2018-02-01 00:00:00.000',	67115991,	1),
('2018-02-02 00:00:00.000',	67115991,	1),
('2018-02-03 00:00:00.000',	67115991,	1),
('2018-03-13 00:00:00.000',	67115991,	0),
('2018-03-14 00:00:00.000',	67115991,	1),
('2018-03-15 00:00:00.000',	67115991,	1),
('2018-03-16 00:00:00.000',	67115991,	0),
('2018-03-17 00:00:00.000',	67115991,	0),
('2018-03-18 00:00:00.000',	67115991,	1),
('2018-03-19 00:00:00.000',	67115991,	1),
('2018-03-20 00:00:00.000',	67115991,	1),
('2018-03-21 00:00:00.000',	67115991,	0),
('2018-03-22 00:00:00.000',	67115991,	0),
('2018-03-23 00:00:00.000',	67115991,	1),
('2018-03-24 00:00:00.000',	67115991,	0);
go

create procedure TableACounts as
begin
	select *,
	sum(case when LastVoltage = CurrentVoltage then 1 else 0 end) over(partition by CurrentVoltage order by UpdateTime) as "SameConsecutiveReadCount"
	from
	(
		select moduleid, UpdateTime, currentvoltage,
	
		Lag(CurrentVoltage) over (partition by moduleid order by UpdateTime) as LastVoltage
		from dbo.TableA h
	) x
	where moduleid in (67115833, 67122344, 67328997)
	order by ModuleId, UpdateTime
end
go

create procedure TableBCounts as
begin
	select *,
	sum(case when Response = LastResponse then 1 else 0 end) over(partition by Response order by InterrogationDate) as "SameConsecutiveResponseCount"
	from
	(
		select InterrogationDate, SerialNumber, Response,
		Lag(Response) over (partition by SerialNumber order by InterrogationDate) as LastResponse
		from dbo.TableB zr
	) zr2
	where SerialNumber in (67115991,67123511,67124224)
	order by SerialNumber, InterrogationDate
end
go
exec TableACounts
go
exec TableBCounts
go