get last active record

get_last_active_record.sql

NEW VER utk current 
+++++
updated 2021-04-14 Guna struktur table baru
--
SELECT
	t.*,
	b.TarikhWarta ,
	b.DPT,
	b.Penerangan 
FROM
	( SELECT KumpulanNoKPId, NoKp, Nama, WartaId FROM DPI UNION ALL SELECT KumpulanNoKPId, NoKp, Nama, WartaId FROM DpiSejarah ) AS t
	INNER JOIN warta b ON t.WartaId = b.WartaId
	JOIN (
	SELECT
		t.KumpulanNoKPId,
		MAX ( b.WartaId ) AS WartaId 
	FROM
		( SELECT KumpulanNoKPId, NoKp, Nama, WartaId FROM DPI UNION ALL SELECT KumpulanNoKPId, NoKp, Nama, WartaId FROM DpiSejarah ) AS t
		INNER JOIN warta b ON t.WartaId = b.WartaId 
	WHERE
		TarikhWarta <= '2020-11-15' 
-- 		TarikhWarta <= '2021-01-15' 
		
	GROUP BY
		t.KumpulanNoKPId 
	) y ON y.KumpulanNoKPId = t.KumpulanNoKPId 
	AND y.WartaId = t.WartaId
	
//-----------------------------------------------

SELECT
	t.*,
	b.TarikhWarta ,
	b.DPT,
	b.Penerangan 
FROM
	( SELECT DpiId, NoKp, Nama, WartaId FROM DPI UNION ALL SELECT DpiId, NoKp, Nama, WartaId FROM SejarahDPI ) AS t
	INNER JOIN warta b ON t.WartaId = b.WartaId
	JOIN (
	SELECT
		t.DpiId,
		MAX ( b.WartaId ) AS WartaId 
	FROM
		( SELECT DpiId, NoKp, Nama, WartaId FROM DPI UNION ALL SELECT DpiId, NoKp, Nama, WartaId FROM SejarahDPI ) AS t
		INNER JOIN warta b ON t.WartaId = b.WartaId 
	WHERE
		TarikhWarta <= '2020-11-15' 
-- 		TarikhWarta <= '2021-01-15' 
		
	GROUP BY
		t.DpiId 
	) y ON y.DpiId = t.DpiId 
	AND y.WartaId = t.WartaId
+++++


SELECT
	* 
FROM
	test x
	JOIN ( SELECT p.id_user, MAX ( tahun ) AS tahun FROM test p WHERE p.tahun <= 2019 GROUP BY p.id_user ) y ON y.id_user = x.id_user 
	AND y.tahun = x.tahun 
WHERE
	x.tahun <= 2019
	
	
sample data	
====
/*
 Navicat Premium Data Transfer

 Source Server         : localhost - mssql
 Source Server Type    : SQL Server
 Source Server Version : 15002070
 Source Catalog        : spr
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 15002070
 File Encoding         : 65001

 Date: 23/06/2020 17:12:04
*/


-- ----------------------------
-- Table structure for test
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type IN ('U'))
	DROP TABLE [dbo].[test]
GO

CREATE TABLE [dbo].[test] (
  [tahun] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [id_user] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [data] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [id] uniqueidentifier  NOT NULL
)
GO

ALTER TABLE [dbo].[test] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO [dbo].[test] ([tahun], [id_user], [data], [id]) VALUES (N'2020', N'1', N'user 1', N'47A2732A-2BAE-423D-A680-02F9432CE69A')
GO

INSERT INTO [dbo].[test] ([tahun], [id_user], [data], [id]) VALUES (N'2017', N'2', N'user 2', N'B6D90CE8-9288-4222-B816-16ECFFFFA45A')
GO

INSERT INTO [dbo].[test] ([tahun], [id_user], [data], [id]) VALUES (N'2019', N'1', N'user 1', N'D5541A5B-A3E7-4715-9ED7-F18247275FF3')
GO


-- ----------------------------
-- Primary Key structure for table test
-- ----------------------------
ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK__test__3213E83F28A8F395] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO