sql server tricky interview questions and answers part 29 | sql server

TRICKY QUESTIONS
--Script

Create Table Part2Tbl
(
ItemCode varchar(10),
Amount int ,
StartDt Date ,
EndDt Date
)

Insert Into Part2Tbl
Select '100',165,'2016-10-04','2016-10-31'
union
Select '100',146,'2016-11-01','2016-12-26'
Union
Select '100',162,'2016-12-27','2017-01-09'
union
Select '100',162,'2017-01-10','2017-01-23'
Union
Select '100',162,'2017-01-24','2017-02-20'
union
Select '100',165,'2017-02-21','2017-02-06'
Union
Select '100',165,'2017-03-07','2017-03-20'
union
Select '100',146,'2017-03-21','2017-04-03'

[VinayKumarPalgurgaon]

It is so simple....only single line of query to achive this....without loosing the performance.

select itemcode, amount, min(startdt), max(enddt) from part2tbl group by amount, itemcode

[ShamimShaikhAM]

It would be really helpful if you can put these questions in description.

[stepannazarov]

I think using rank() function to achieve the same result would be less complicated.... just do partition by amount in order you need and then group by rank and amount and have min and max in dates. And you need to change 6 feb to 6 march in data btw.

[subhashkomy]

I think this is more simple way using cursor and you can use for any data set not only for you specified. Here you go:
This is your Data
IF OBJECT_ID('TempDB..#SALES') IS NOT NULL DROP TABLE #SALES
IF OBJECT_ID('TempDB..#CUR') IS NOT NULL DROP TABLE #CUR
SELECT *, ROW_NUMBER() OVER(ORDER BY ItemCode) AS SR INTO #SALES
FROM
(
SELECT 100 AS ItemCode, 165 AS Amount, CAST ('4-Oct-16' AS DATE) AS StartDt, CAST('31-Oct-16' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 146 AS Amount, CAST ('1-Nov-16' AS DATE) AS StartDt, CAST('26-Dec-16' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 162 AS Amount, CAST ('27-Dec-16' AS DATE) AS StartDt, CAST('9-Jan-17' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 162 AS Amount, CAST ('10-Jan-17' AS DATE) AS StartDt, CAST('23-Jan-17' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 162 AS Amount, CAST ('24-Jan-17' AS DATE) AS StartDt, CAST('20-Feb-17' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 165 AS Amount, CAST ('21-Feb-17' AS DATE) AS StartDt, CAST('6-Feb-17' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 165 AS Amount, CAST ('7-Mar-17' AS DATE) AS StartDt, CAST('20-Mar-17' AS DATE) AS EndDt
UNION ALL
SELECT 100 AS ItemCode, 146 AS Amount, CAST ('21-Mar-17' AS DATE) AS StartDt, CAST('3-Apr-17' AS DATE) AS EndDt
) X
SELECT * FROM #SALES


This Is QUERY:

SELECT * INTO #CUR FROM #SALES WHERE 1=2

DECLARE
DECLARE MyCur CURSOR FOR SELECT * FROM #SALES ORDER BY SR
OPEN MyCur
FETCH NEXT FROM MyCur INTO @ItemCode, @Amount, @StartDt, @EndDt, @SR
INSERT INTO #CUR (ItemCode, Amount, StartDt, EndDt, SR)
BEGIN
FETCH NEXT FROM MyCur INTO @ItemCode, @Amount, @StartDt, @EndDt, @SR
BEGIN
INSERT INTO #CUR (ItemCode, Amount, StartDt, EndDt, SR)
END;
END
CLOSE MyCur
DEALLOCATE MyCur
SELECT ItemCode, Amount, CONVERT(VARCHAR(12), StartDt, 6) AS StartDt, CONVERT(VARCHAR(12), EndDt, 6) AS EndDt FROM #CUR

[pravaspatra]

Sir, In this example in the SQL table there is only one item code 100
If we have more than one item code. Then what changes we need to do to get similar results like this ?

[munnaharun]

Hi sir,

can we do this approach ..?


select amount, min(stdate), max(enddate) from group by amount

[Style_with_mee]

Hi,
Your videos are really helpful but I have one suggestion that if u r not comfortable with English so I think u should speak Hindi and I think u can make us understand with more clarity... Don't feel bad or don't take it wrong

[vijay.s-llyq]

with cte as
(select *, row_number() over(partition by year(enddt), amount order by startdt)rm from Part2Tbl)

select amount, year(enddt),min(startdt),max(enddt)from cte
group by amount, year(enddt)

[sravankumar]

This is really tricky question 😕. Am not able to understand the logic

[ravindrasingh]

Awesome question..and nice way to explain. .

[pratibha]

Sel
ItemCode
, amount
, Min(StartDt)
, Max(EndDt)
from table
group by 1, 2 ;

Abv code should do the job.

[KumarCode]

Starting ke 3rd line where 1!=1
Kya kar raha hai please explain that

[subhashkomy]

Arrey bhai Q itani mahenat ki jab ki ek hi statement se aa Jaata ye.
SELECT
PRODCODE, AMOUNT, MIN(STARTDT) FromDate, MAX(ENDDT) ToDate
FROM #TABLE
GROUP BY PRODCODE, AMOUNT

[g.suresh]

Hello Sir, Please share the Query I was unable to find. Thank You

[mokshaGyanRam]

I suggest more work on video cant understand it

[vaibhavsingh]

It seems to easy but it's not...
Thanks for sharing

[ssunitech]

I would really appreciate your affect..
Keep it up...