MS SQL SERVER COMPLEX INTERVIEW QUERY | CAR
Today I came with complex Sql server Query, You can face this query in interview but not in case of fresher sql server interview, It can be asked to experienced sql server developer. I think If you write down this query, then you must impress your interviewer, and if you impressed your interviewer then you must cracked your interview.
So now lets talk about the query.
Query : You have a table with ID, Fuel, And Date columns.
Fuel column is contain fuel at a particular time when car start traveling. So we need to find out that when the driver fill Petrol in his/her car.
By below image you can understand the query.
Car start driving at 10 Am on 25th April
at 11 Am Petrol was 9 liters
at 12 Am petrol was 8 liters
at 2 Pm (14) petrol was 12 liters...
This means that he/she fill the petrol at 25th April 2014 at 2PM
Next time he fill petrol at 7PM 25th April 2014
and Next time he fill petrol at 11PM 25th April 2014
So we need to find out the above date by query.
Solution of the is (by Deepak):
select c1.fuel,c1.[Date],c.fuel,c.[date]
from car c
join
car c1 on c1.[Date] =(select min([Date]) from car where [Date]>c.[Date] )
where c1.fuel>c.fuel
Second Solution you can try following Long Query (by Vikas):
Select * from
(
SELECT CASE
WHEN [Fuel] < fule1 THEN DATE1
ELSE
NULL
END AS [DATE]
FROM
(
select A.[ID]
,A.[Fuel]
,m.[Fuel] as fule1
,A.[Date]
,M.[Date] AS DATE1
FROM
(SELECT TOP 2000 [ID]
,[Fuel]
,[Date]
FROM [Vikas].[dbo].[Car]
where [ID]%2 = 1
) A
Join (
SELECT TOP 2000 [ID]-1 as id
,[Fuel]
,[Date]
FROM [Vikas].[dbo].[Car]
where [ID]%2 = 0)
M on A.ID = M.id
) R
Union all
SELECT CASE
WHEN [Fuel] < fule1 THEN DATE1
ELSE
NULL
END AS [DATE]
FROM
(
select A.[ID]
,A.[Fuel]
,m.[Fuel] as fule1
,A.[Date]
,M.[Date] AS DATE1
FROM
( SELECT [ID] as [ID]
,[Fuel]
,[Date]
FROM ( SELECT TOP 2000 [ID]-1 AS ID
,[Fuel]
,[Date]
FROM [Vikas].[dbo].[Car]
WHERE [ID] NOT IN (1)) T
where [ID]%2 = 1
) A
Join (
SELECT [ID]-1 as id
,[Fuel]
,[Date]
FROM ( SELECT TOP 2000 [ID]-1 AS ID
,[Fuel]
,[Date]
FROM [Vikas].[dbo].[Car]
WHERE [ID] NOT IN (1)) T
where [ID]%2 = 0)
M on A.ID = M.id
) R
) Z WHERE DATE IS NOT NULL order by [DATE]
OUTPUT :
If you want to try this at your and then run the following script for create table.
CREATE TABLE [dbo].[Car](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Fuel] [int] NULL,
[Date] [datetime] NULL,
CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Car]
ON
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (1, 10, CAST(0x0000A31800A4CB80
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (2, 9, CAST(0x0000A31800B54640
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (3, 8, CAST(0x0000A31800C5C100
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (4, 6, CAST(0x0000A31800D63BC0
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (5, 12, CAST(0x0000A31800E6B680
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (6, 11, CAST(0x0000A31800F73140
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (7, 10, CAST(0x0000A3180107AC00
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (8, 9, CAST(0x0000A318011826C0
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (9, 8, CAST(0x0000A3180128A180
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (10, 10, CAST(0x0000A31801391C40
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (11, 9, CAST(0x0000A31801499700
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (12, 8, CAST(0x0000A318015A11C0
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (13, 7, CAST(0x0000A318016A8C80
AS DateTime))
INSERT [dbo].[Car] ([ID], [Fuel], [Date]) VALUES (14, 15, CAST(0x0000A318017B0740
AS DateTime))
Comments