Kennel SWAG, Sql Server

Language: Layout:

CREATE TABLE kennel_history([kh_identity] [int] NOT NULL,
                            [kennel_identity] [int] NOT NULL,
                            [stamp] [datetime] NULL,
                            [userid] [varchar](8) NULL,
                            [impound_no] [varchar](10) NOT NULL,
                            [kennel_no] [varchar](10) NULL,
                            [kennel_stat] [varchar](10) NULL,
                            [hold_notify] [varchar](1) NULL,
                            [outcome_request] [varchar](10) NULL
);

INSERT INTO kennel_history --original
VALUES (9471697,881929,'2019-01-17 08:05:41','CHEITMAN','K18-847522','QCB','UNAVAILABL',NULL,NULL),
(9471254,881929,'2019-01-16 14:07:18','THUTCHIN','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469550,881929,'2019-01-15 07:18:36','BBUSCEMI','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469390,881929,'2019-01-14 16:56:02','LRAYNER','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469302,881929,'2019-01-14 16:02:41','SHUNT','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9467613,881929,'2019-01-13 08:30:46','DEADS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465923,881929,'2019-01-11 10:16:52','DEADS','K18-847522','QRL06','UNAVAILABL','Y','VET CHECK'),
(9465225,881929,'2019-01-10 12:07:17','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465224,881929,'2019-01-10 12:07:07','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9463051,881929,'2019-01-08 06:43:19','CSILVEY','K18-847522','QRL03','AVAILABLE',NULL,NULL),
(9461197,881929,'2019-01-06 09:24:07','APENAZUR','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9460067,881929,'2019-01-05 08:57:46','APENAZUR','K18-847522','QRL07','AVAILABLE',NULL,NULL),
(9459250,881929,'2019-01-04 10:13:45','DEADS','K18-847522','QRL01','AVAILABLE',NULL,NULL),
(9458551,881929,'2019-01-03 12:30:42','ACLARK','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9458499,881929,'2019-01-03 11:51:48','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9458484,881929,'2019-01-03 11:48:32','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9454810,881929,'2018-12-29 12:20:01','ACLARK','K18-847522','DHC04','UNAVAILABL',NULL,NULL),
(9454683,881929,'2018-12-29 11:08:39','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454680,881929,'2018-12-29 11:06:32','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454511,881929,'2018-12-29 09:13:22','BBUSCEMI','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9453649,881929,'2018-12-28 08:46:12','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453648,881929,'2018-12-28 08:46:07','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET CHECK'),
(9453624,881929,'2018-12-28 08:03:19','BBUSCEMI','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453533,881929,'2018-12-27 17:45:22','DEADS','K18-847522','DO03','UNAVAILABL','Y','VET TECH'),
(9453405,881929,'2018-12-27 15:28:02','DEADS','K18-847522','DO02','UNAVAILABL','Y','VET TECH'),
(9452597,881929,'2018-12-26 15:27:48','SSUTTON','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452426,881929,'2018-12-26 13:05:26','THUTCHIN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452121,881929,'2018-12-26 10:18:55','THUTCHIN','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451959,881929,'2018-12-26 08:09:21','BBUSCEMI','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451886,881929,'2018-12-25 14:12:49','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451884,881929,'2018-12-25 14:11:58','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451870,881929,'2018-12-25 13:28:15','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9450863,881929,'2018-12-23 17:24:17','BLAEHLE','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9449482,881929,'2018-12-22 13:12:34','V-KTAYLO','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448808,881929,'2018-12-21 16:23:03','SBUCKMAN','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448111,881929,'2018-12-21 09:10:49','CHEITMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9448069,881929,'2018-12-21 08:36:47','BMERMAN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9447864,881929,'2018-12-20 17:32:53','DEADS','K18-847522','DO07','UNAVAILABL','Y','VET TECH'),
(9446090,881929,'2018-12-19 09:54:33','MGELTZ','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9445884,881929,'2018-12-19 07:25:51','ZKNOX','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9444928,881929,'2018-12-18 08:22:30','EKNEPPER','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9438860,881929,'2018-12-12 09:15:33','CMCCANN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9438820,881929,'2018-12-12 08:21:33','JAUSEC','K18-847522','DO07','UNAVAILABL',NULL,NULL);

INSERT INTO kennel_history --update 1
VALUES (9471697,881930,'2019-01-17 09:05:41','THUTCHIN','K18-847522','QCB','UNAVAILABL','Y','VET CHECK'),
(9471254,881930,'2019-01-15 12:07:18','THUTCHIN','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469550,881930,'2019-01-15 07:18:36','BBUSCEMI','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469390,881930,'2019-01-14 16:56:02','LRAYNER','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469302,881930,'2019-01-14 12:02:41','THUTCHIN','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9467613,881930,'2019-01-12 09:30:46','KMORRIS','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9465923,881930,'2019-01-11 10:16:52','DEADS','K18-847522','QRL06','UNAVAILABL',NULL,NULL),
(9465225,881930,'2019-01-11 02:07:17','KMORRIS','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9465224,881930,'2019-01-10 12:07:07','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9463051,881930,'2019-01-08 06:43:19','KMORRIS','K18-847522','QRL03','AVAILABLE',NULL,NULL)

INSERT INTO kennel_history --update 2
VALUES (9471697,881931,'2019-01-18 06:05:22','BBUSCEMI','K18-847522','QCB','UNAVAILABL','Y','PRIORITY 1'),
(9471254,881931,'2019-01-17 11:30:18','LRAYNER','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469550,881931,'2019-01-17 08:25:33','KMORRIS','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469390,881931,'2019-01-16 18:20:02','KMORRIS','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469302,881931,'2019-01-16 14:01:41','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET TECH'),
(9467613,881931,'2019-01-15 10:20:46','DEADS','K18-847522','QRL03','UNAVAILABL','Y','VET TECH'),
(9465923,881931,'2019-01-15 06:11:52','BMERMAN','K18-847522','QRL06','UNAVAILABL',NULL,NULL),
(9465225,881931,'2019-01-15 06:09:17','CHEITMAN','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9465224,881931,'2019-01-15 05:36:07','CHEITMAN','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9463051,881931,'2019-01-14 023:56:19','CHEITMAN','K18-847522','QRL03','AVAILABLE','Y','VET TECH')

CREATE FUNCTION StatusAtTime (@RequestedTime DATETIME)
RETURNS TABLE
AS
RETURN
(
	WITH statusBuilder AS
	(
		SELECT
			kh1.*,
			lag(kh2.[outcome_request]) OVER (PARTITION BY kh2.[kennel_identity] ORDER BY kh2.[stamp]) AS [lagged_outcome_request]
		FROM dbo.kennel_history kh1
		JOIN dbo.kennel_history kh2
			ON kh2.[stamp] = kh1.[stamp]
			AND kh2.[kennel_identity] = kh1.[kennel_identity]
		WHERE kh1.[stamp] <= @RequestedTime 
	)

SELECT 
		SB1.[kh_identity]
		,SB1.[kennel_identity]
		,SB1.[stamp]
		,SB1.[userid]
		,SB1.[impound_no]
		,SB1.[kennel_no]
		,SB1.[kennel_stat]
		,SB1.[hold_notify]
		,SB1.[outcome_request]
		,SB2.[userid] AS [initial_outcome_request_userid]
		,SB2.[stamp] AS [initial_outcome_request_stamp]
	FROM 
		(
			SELECT TOP 1  WITH ties *
			FROM statusBuilder 
			ORDER BY ROW_NUMBER() OVER (PARTITION BY statusBuilder.kennel_identity ORDER BY statusBuilder.stamp desc) 
		) SB1
	JOIN 
		(
			SELECT TOP 1  WITH ties *
			FROM statusBuilder 
			WHERE ISNULL([outcome_request],'NullOutcome') <> ISNULL([lagged_outcome_request],'NullOutcome')
			ORDER BY ROW_NUMBER() OVER (PARTITION BY statusBuilder.kennel_identity ORDER BY statusBuilder.stamp desc)
		)SB2
	ON SB1.kennel_identity = SB2.kennel_identity
)

SELECT * FROM dbo.StatusAtTime (GETDATE()) ORDER BY kennel_identity

SELECT * FROM dbo.StatusAtTime ('2019-01-15 07:08:00.000') ORDER BY kennel_identity
SELECT * FROM dbo.StatusAtTime ('2019-01-15 07:19:00.000') ORDER BY kennel_identity
SELECT * FROM dbo.StatusAtTime ('2019-01-10 12:06:00.000') ORDER BY kennel_identity
SELECT * FROM dbo.StatusAtTime ('2019-01-10 12:08:00.000') ORDER BY kennel_identity
SELECT * FROM dbo.StatusAtTime ('2019-01-14 16:03:00.000') ORDER BY kennel_identity

View schema
Execution time: 0,19 sec, rows selected: 14, rows affected: 63, absolute service time: 0,42 sec, absolute service time: 0,42 sec