Last three records of evently data for comparsion purpose

Sameer · August 14, 2019, 3:35pm

Hi experts !
I have a below sql table query runs fine to bring the last available record of each facility, factilty2 & factilty3 from any date passed.
For comparison purpose need to modify the query every time retrieve the last 3 records of each facility from any date passed. If possible every three records of each facility come in one row?

USE [TESTDB]
GO
--DROP TABLE TEST_TABLE
CREATE TABLE [dbo].[TEST_TABLE](
	[EVENT_GROUP_ID] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[EVENT_TYPE] [varchar](14) NULL,
	[PERIOD] [varchar](20) NULL,
	[ITEM_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[VAL1] [numeric](28, 12) NULL,
	[VAL2] [numeric](28, 12) NULL,
	[VAL3] [numeric](28, 12) NULL
)

GO
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/30/2018',	'12/30/2018',1	)	
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/10/2019',	'1/10/2019',	12	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/31/2019',	'1/31/2019',	33	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/10/2019',	'2/10/2019',	43	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/12/2019',	'2/12/2019',	45	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/16/2019',	'2/16/2019',	49	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'2/17/2019',	'2/17/2019',	50	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'2/19/2019',	'2/19/2019',	52	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'2/21/2019',	'2/21/2019',	54	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'2/23/2019',	'2/23/2019',	56	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'2/24/2019',	'2/24/2019',	57	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'2/25/2019',	'2/25/2019',	58	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY2',	'3/30/2019',	'3/30/2019',	91	)

INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'2/21/2019',	'2/21/2019',	54	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'2/23/2019',	'2/23/2019',	56	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'2/24/2019',	'2/24/2019',	57	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'5/25/2019',	'5/25/2019',	58	)
INSERT [dbo].[TEST_TABLE] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'8/13/2019',	'8/13/2019',	91	)

--SELECT * FROM TEST_TABLE
Declare @Enterdate Datetime
Set @Enterdate =CONVERT(Varchar(10),'8/13/2019',102);
SELECT 
       MAX(CONVERT(Varchar(10),otr.START_DATETIME,102)+' '+ CONVERT(Varchar(20),otr.START_DATETIME,108))startdate
       ,max(otr.ITEM_NAME) name,
       SUM(otr.VAL1)data
      FROM TEST_TABLE otr   
            WHERE CONVERT(Varchar(10),otr.START_DATETIME,102)<= @Enterdate 
            AND (CONVERT(Varchar(10),otr.START_DATETIME,102) +' '+ CONVERT(Varchar(20),otr.START_DATETIME,108))=                         
(SELECT max(CONVERT(Varchar(10),inr.START_DATETIME,102)+' '+CONVERT(Varchar(20),inr.START_DATETIME,108))                          
      FROM TEST_TABLE inr                         
      WHERE inr.ITEM_NAME=otr.ITEM_NAME 
                             AND CONVERT(Varchar(10),inr.START_DATETIME,102)<= @Enterdate                      
 )   GROUP BY otr.ITEM_NAME  ,otr.ITEM_ID  ORDER BY otr.ITEM_NAME

JamesK · August 14, 2019, 6:12pm

I didn't follow all the logic you are trying to implement in the query you posted. In particular, all the conversions of datetime to strings and back seem unnecessary.

I would propose something like this:

;WITH cte AS
(
	SELECT
		*,
		N = ROW_NUMBER() OVER (PARTITION BY ITEM_NAME ORDER BY START_DATETIME DESC)
		--N = DENSE_RANK() OVER (PARTITION BY ITEM_NAME ORDER BY START_DATETIME DESC)
	FROM
		[dbo].[TEST_TABLE]
)
SELECT startdate = [START_DATETIME], 
	name = ITEM_NAME,
	data = Val1
FROM
	cte
WHERE
	--N=1;
	N <= 3;

If you uncomment the second to last line and remove the last line, it should give you the result you are currently getting.

If you want to include ties use the DENSE_RANK function that I commented out.

harishgg1 · August 14, 2019, 6:21pm

Hi

Looking at this
At the top of my head
Lead Lag comes to mind

I am not looking into it closely.

Please let me know if lead Lag works

Sameer · August 15, 2019, 1:19pm

Jamesk Many thanks for your purpose Cte method is much faster then which i posted.

will you please help to transpose rows to columns as below screenshot required results in table2

JamesK · August 15, 2019, 2:52pm

You can pivot the data - see the example below

;WITH cte AS
(
	SELECT
		*,
		N = ROW_NUMBER() OVER (PARTITION BY ITEM_NAME ORDER BY START_DATETIME DESC)
		--N = DENSE_RANK() OVER (PARTITION BY ITEM_NAME ORDER BY START_DATETIME DESC)
	FROM
		[dbo].[TEST_TABLE]
)
SELECT
	startdate = MAX(CASE WHEN N=1 THEN c.START_DATETIME END),
	name = ITEM_NAME,
	data = MAX( CASE WHEN N=1 THEN c.val1 END),
	startdate1 = MAX(CASE WHEN N=2 THEN c.START_DATETIME END),
	data1 = MAX( CASE WHEN N=2 THEN c.val1 END),
	startdate1 = MAX(CASE WHEN N=3 THEN c.START_DATETIME END),
	data1 = MAX( CASE WHEN N=3 THEN c.val1 END)
FROM
	cte c
GROUP BY 
	ITEM_NAME;

Sameer · August 16, 2019, 4:34am

JamesK wonderful and bundle of thanks !