Friday, 25 April 2014

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))
Visit Our New Site for HR Interview Questions Answers http://www.interviewboat.com/Company/GetCompany
Sponsored Ads

0 comments:

Post a Comment