Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Get Previous and Next Value Puzzle

In this puzzle you have to Get the previous and next value of the Value column. Please check out the sample input values and sample expected output below.

Sample Input

Source ObjectID Key Value AuditAction AuditDate
Compounds 1 CompoundName Comp A I 2017-10-24 16:50:50.100
Compounds 1 Dosage 125.50 I 2017-10-24 16:50:50.100
Compounds 1 Route IT I 2017-10-24 16:50:50.100
Compounds 1 CompoundName Comp A U 2017-10-24 17:50:50.100
Compounds 1 Dosage 160.50 U 2017-10-24 17:50:50.100
Compounds 1 Route IT U 2017-10-24 17:50:50.100
Compounds 1 CompoundName Comp A U 2017-10-24 18:50:50.100
Compounds 1 Dosage 130.50 U 2017-10-24 18:50:50.100
Compounds 1 Route IV U 2017-10-24 18:50:50.100

Expected Output

Source ObjectID Key From To AuditAction AuditDate
Compounds 1 CompoundName NULL Comp A I 2017-10-24 16:50:50.100
Compounds 1 Dosage NULL 125.50 I 2017-10-24 16:50:50.100
Compounds 1 Route NULL IT I 2017-10-24 16:50:50.100
Compounds 1 Dosage 125.50 160.50 U 2017-10-24 17:50:50.100
Compounds 1 Dosage 160.50 130.50 U 2017-10-24 18:50:50.100
Compounds 1 Route IT IV U 2017-10-24 18:50:50.100

Script

Use below script to create table and insert sample data into it.

--

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VW_AuditHistory](
	[Source] [varchar](9) NOT NULL,
	[ObjectID] [int] NOT NULL,
	[Key] [varchar](12) NOT NULL,
	[Value] [varchar](6) NOT NULL,
	[AuditAction] [varchar](1) NOT NULL,
	[AuditDate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'CompoundName', N'Comp A', N'I', CAST(0x0000A8160115A256 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Dosage', N'125.50', N'I', CAST(0x0000A8160115A256 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Route', N'IT', N'I', CAST(0x0000A8160115A256 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'CompoundName', N'Comp A', N'U', CAST(0x0000A81601261D16 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Dosage', N'160.50', N'U', CAST(0x0000A81601261D16 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Route', N'IT', N'U', CAST(0x0000A81601261D16 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'CompoundName', N'Comp A', N'U', CAST(0x0000A816013697D6 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Dosage', N'130.50', N'U', CAST(0x0000A816013697D6 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Route', N'IV', N'U', CAST(0x0000A816013697D6 AS DateTime))
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--


SELECT * FROM 
(
	select v.Source, v.ObjectID, v.[Key], lag(Value) over(partition by source,[key] order by AuditDate) [From],
	v.Value [To], v.AuditAction, v.AuditDate
	from VW_AuditHistory v
)u WHERE ([From] <> [To] or [From] IS NULL)
order by AuditDate, [Key]
--

Output

--

/*------------------------
SELECT * FROM 
(
	select v.Source, v.ObjectID, v.[Key], lag(Value) over(partition by source,[key] order by AuditDate) [From],
	v.Value [To], v.AuditAction, v.AuditDate
	from VW_AuditHistory v
)u WHERE ([From] <> [To] or [From] IS NULL)
order by AuditDate, [Key]
------------------------*/
Source    ObjectID    Key          From   To     AuditAction AuditDate
--------- ----------- ------------ ------ ------ ----------- -----------------------
Compounds 1           CompoundName NULL   Comp A I           2017-10-24 16:50:50.100
Compounds 1           Dosage       NULL   125.50 I           2017-10-24 16:50:50.100
Compounds 1           Route        NULL   IT     I           2017-10-24 16:50:50.100
Compounds 1           Dosage       125.50 160.50 U           2017-10-24 17:50:50.100
Compounds 1           Dosage       160.50 130.50 U           2017-10-24 18:50:50.100
Compounds 1           Route        IT     IV     U           2017-10-24 18:50:50.100

(6 row(s) affected)



--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com