Find duplicate row and differentiate with date

Question

Hi any one hep me? I have table in sql like: RateDate Currency MinLos MaxLOS CutOff Availability 2018-06-01 INR 12 15 0 5 2018-06-02 INR 12 15 0 5 2018-06-03 INR 3 13 2 4 2018-06-04 INR 5 12 4 20 2018-06-05 INR 5 12 4 20 2018-06-06 INR 8 1 5 2 my out put should be like this RateDate Currency MinLos MaxLOS CutOff Availability 2018-06-01 - 2018-06-02 INR 12 15 0 5 2018-06-03 - 2018-06-03 INR 3 13 2 4 2018-06-04 - 2018-06-05 INR 5 12 4 20 2018-06-06 - 2018-06-06 INR 8 1 5 2 Please help me ASAP.

Manish Kumar · Answer

Sorry i did not see your output clearly. Try below query. declare @tbl table (ratedate date,currency varchar(3), minlos int,maxlos int,cutoff int) insert into @tbl select '2018-06-01','INR',12,15,0 union all select '2018-06-02','INR',12,15,0 union all select '2018-06-03','INR',3,13,2 union all select '2018-06-04','INR',5,12,4 union all select '2018-06-05','INR',5,12,4 union all select '2018-06-06','INR',8,1,5 ;with cte as ( select ROW_NUMBER() over (partition by MinLos,MaxLOS,CutOff order by RateDate asc) as rwno,RateDate,MinLos,MaxLOS,CutOff from @tbl ) select * into #temp from cte order by RateDate select cast(a.ratedate as varchar)+'-'+cast(isnull(tbl.ratedate,a.ratedate) as varchar) as ratedate, a.minlos,a.maxlos,a.cutoff from #temp a left join (select * from #temp b where b.rwno=2 ) tbl on a.minlos=tbl.minlos and a.maxlos=tbl.maxlos and a.cutoff=tbl.cutoff where a.rwno=1

Manish Kumar · Answer

;with cte as ( select ROW_NUMBER() over (partition by MinLos,MaxLOS,CutOff,Availability order by RateDate asc) as rwno,RateDateMinLos,MaxLOS,CutOff,Availability from table_name ) select * from cte where rwno=1

Karan Singh · Answer

hi sir, thanks for your response , i'm applying your query on my actual table it show like this not the way which i want but i want my table out put will be on this format : RateDate Currency MinLos MaxLOS CutOff Availability 2018-06-01 - 2018-06-02 INR 12 15 0 5 2018-06-03 - 2018-06-03 INR 3 13 2 4 2018-06-04 - 2018-06-05 INR 5 12 4 20 2018-06-06 - 2018-06-06 INR 8 1 5 2

Karan Singh · Answer

Thanks Sir, its working .

Find duplicate row and differentiate with date

Answers (4)