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 datecurrency varchar(3) minlos intmaxlos intcutoff int) insert into @tbl select '2018-06-01''INR'12150 union all select '2018-06-02''INR'12150 union all select '2018-06-03''INR'3132 union all select '2018-06-04''INR'5124 union all select '2018-06-05''INR'5124 union all select '2018-06-06''INR'815 ;with cte as ( select ROW_NUMBER() over (partition by MinLosMaxLOSCutOff order by RateDate asc) as rwnoRateDateMinLosMaxLOSCutOff from @tbl ) select * into #temp from cte order by RateDate select cast(a.ratedate as varchar)+'-'+cast(isnull(tbl.ratedatea.ratedate) as varchar) as ratedate a.minlosa.maxlosa.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 MinLosMaxLOSCutOffAvailability order by RateDate asc) as rwnoRateDateMinLosMaxLOSCutOffAvailability 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 .