HI,
I need help Sql table like below with different Insert and Delete in a column for a given Account with response column being dates.
I would like to get the result with Insert and Delete in two columns such that insert date and delete dates are closest like output desired section.
Any help is greatly appreciated.
Thanks,
Consumable test data which you should have provided:
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t
(
VisitID varchar(20) NOT NULL
,QueryID varchar(10) NOT NULL
,Response date NOT NULL
PRIMARY KEY (VisitID, Response)
);
INSERT INTO #t
VALUES ('PA10004252470', 'Insert', '20240219')
,('PA10004252970', 'Delete', '20240212')
,('PA10004252970', 'Delete', '20240215')
,('PA10004252970', 'Insert', '20240211')
,('PA10004252970', 'Insert', '20240214');
GO
This works with the given data:
WITH Grps
AS
(
SELECT VisitID, QueryID, Response
,(ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY Response) - 1) / 2 AS Grp
FROM #t
)
SELECT VisitID
,MIN(CASE WHEN QueryID = 'Insert' THEN Response END) AS [Insert]
,MAX(CASE WHEN QueryID = 'Delete' THEN Response END) AS [Delete]
FROM Grps
GROUP BY VisitID, Grp
ORDER BY [Insert];