| Author | Message | ||
Nakhral Junior Artist Username: Nakhral Post Number: 804 Registered: 11-2012 Posted From: 73.50.134.101 Rating: N/A |
Select Vbeln, min(Begda) Begda, Max(Endda) Endda, PernrZa,PernrZb from ( SELECT Vbeln, Begda ,Endda, PernrZa,PernrZb ,row_number() Over(Order by Begda)- row_number() Over(Partition by Vbeln Order by Endda) tempdata FROM my_table ) t Group by Vbeln,PernrZa,PernrZb,tempdata | ||
Cheenu Junior Artist Username: Cheenu Post Number: 73 Registered: 05-2016 Posted From: 174.228.3.10 Rating: N/A |
DR annai chepinattu ranking function use cheyyi order by lo dates and any other column as per your need ivvali. This is a common requirement google chesthe chala sql codes dorukuthai | ||
Driverramudu Megastar Username: Driverramudu Post Number: 21814 Registered: 02-2009 Posted From: 166.170.15.64 Rating: N/A |
Use dense rank for function and using CTE Write query its very simple Driving is my PASSION. Ball or Bimmer does not matter. | ||
Shikar Junior Artist Username: Shikar Post Number: 362 Registered: 03-2010 Posted From: 68.197.154.245 Rating: N/A |
I need to condense the date range and build a contiguous date range CREATE TABLE #Data ( Vbeln NVARCHAR(12) , Begda NVARCHAR(10) -- but can be converted to datetime2 , Endda NVARCHAR(10) -- but can be converted to datetime2 , PernrZa NVARCHAR(10) , PernrZb NVARCHAR(10) ) INSERT INTO #Data (Vbeln, Begda, Endda, PernrZa, PernrZb) VALUES ('3000080085','19000101','20160411','1111111','1111111') ,('3000080085','20160412','20160418','1521708','1895971') ,('3000080085','20160419','20160516','1521708','1895971') ,('3000080085','20160517','20160519','1521708','1895971') ,('3000080085','20160520','20160523','1521708','1895971') ,('3000080085','20160524','20160606','1521708','2304882') ,('3000080085','20160607','20160628','1521708','1895971') ,('3000080085','20160629','20160826','1521708','2304882') ,('3000080085','20160827','20160909','1521708','2304882') ,('3000080085','20160910','20161011','1579311','2304882') ,('3000080085','20161012','20161201','1579311','2889814') ,('3000080085','20161202','20161225','1579311','2889814') ,('3000080085','20161226','99991231','1111111','1111111') ,('2000094798','19000101','20121029','1315393','1315393') ,('2000094798','20121030','20121224','1315393','1315393') ,('2000094798','20121225','20130331','1315393','1315393') ,('2000094798','20130401','20131003','1315393','1315393') ,('2000094798','20131004','20140429','1315393','1315393') ,('2000094798','20140430','20150326','1315393','1315393') ,('2000094798','20150327','20160826','1315393','1315393') ,('2000094798','20160827','20160909','1315393','1315393') ,('2000094798','20160910','20161201','1315393','1315393') ,('2000094798','20161202','99991231','1315393','1315393') I want the output as below: '3000080085','19000101','20160412','1111111','1111111' '3000080085','20160412','20160524','1521708','1895971' '3000080085','20160524','20160607','1521708','2304882' '3000080085','20160607','20160629','1521708','1895971' '3000080085','20160629','20160910','1521708','2304882' '3000080085','20160910','20161012','1579311','2304882' '3000080085','20161012','20161226','1579311','2889814' '3000080085','20161226','99991231','1111111','1111111' '2000094798','19000101','99991231','1315393','1315393' |