0

I have a query

SELECT ROW_NUMBER() OVER (PARTITION BY [CLIENT NO] ORDER BY Business_Date ASC) AS AGE_IN_DAYS,* 
FROM #TEMP 
WHERE COLLATERAL_VALUE < 0 AND [CLIENT NO] = 'XXXXXX'

which gives Result set As:

GEA_IN_DAYS CLIENT NO   MARKET_VALUE    COLLATERAL_VALUE    Business_Date   PERSHING ID
1           xxxxx       -3102.37        -332884.83          12/13/2016      2VX031579
2           xxxxx       -4963.8         -334467.04          12/14/2016      2VX031579
3           xxxxx       -17952.41       -345507.36          12/15/2016      2VX031579
4           xxxxx       -428.28         -428.28             12/22/2016      2VX031579

I need to update the column AGE_IN_DAYS as per the Business_date. if Business_date is continuous I need to update AGE_IN_DAYS by '1'. in the above example I need to increment first 3 records by '1' and last one I need to count again starting from 1.

can any one please suggest me how can I do this?

Dzmitry Paliakou
  • 1,587
  • 19
  • 27
srikanth
  • 41
  • 1
  • 6
  • 1
    Please tag your DBMS – JohnHC Jan 09 '17 at 15:33
  • May window function make you allow to look previous and next. [compare the current row with next and previous row](http://stackoverflow.com/questions/7974866/how-to-compare-the-current-row-with-next-and-previous-row-in-postgresql) – Aya Aboud Jan 09 '17 at 15:52
  • This sounds like an [islands and gaps](https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/) problem. – JNevill Jan 09 '17 at 17:16
  • what MSSQL version ? – Milan Jan 10 '17 at 00:18
  • it's SQL server 2008 R2 – srikanth Jan 10 '17 at 01:20
  • Hi all thanks for the comments. here is the code that is solved the above issue:DELETE AGE_IN_DAYS WHERE NOT EXISTS (SELECT DISTINCT [CLIENT NO] FROM #MS_CLIENTS WHERE COLLATERAL_VALUE < 0) IF (SELECT COUNT(1) FROM AGE_IN_DAYS AGE INNER JOIN (SELECT * FROM #MS_CLIENTS WHERE COLLATERAL_VALUE < 0) MS ON MS.[CLIENT NO] = AGE.[CLIENT NO] AND MS.Business_Date = AGE.BUSINESS_DATE) = 0 INSERT INTO AGE_IN_DAYS([CLIENT NO],BUSINESS_DATE,[CREATED_DATE]) (SELECT [CLIENT NO],Business_Date,CONVERT(DATE,GETDATE()) FROM #MS_CLIENTS WHERE COLLATERAL_VALUE < 0) – srikanth Jan 13 '17 at 16:57
  • Basically I am inserting and updating data, if there is a gap in the date I am deleting the records from the table – srikanth Jan 13 '17 at 17:00

0 Answers0