How can I compare two rows values within the same table based on condition?-CodePudding

Need to compare 1st row values with second rows values based on multiple columns condition based

this is my query :

SELECT [MM_FWReport].[TYPE] AS [TYPE],
       [MM_FWReport].[BUY_AMOUNT] AS [AMOUNT],
       [MM_FWReport].[MM_SZSWIFTL] AS [MM_SZSWIFTL],
       [MM_FWReport].[MM_SZCPAIR] AS [CURRENCY_PAIR],
       [MM_FWReport].[Value_Date] AS [Value_Date],
       [MM_FWReport].[A_Rate] AS [A_Rate],
       [MM_FWReport].[CLIENT_NAME] AS [CLIENT_NAME],
       [MM_FWReport].[TSTATUS] AS [TSTATUS]
  FROM [dbo].[MM_FWReport] [MM_FWReport]
 ORDER BY [MM_FWReport].[MM_SZCPAIR]

now need to create new filed status that will be if 1 row LONG amount 25m is equal to 2nd row SHOT values 25M then status is 'NET' here both CURRENCY_PAIR should be same and Value date should b equal, if not then status will be not matched

CodePudding user response：

It seems you need a simple LEAD() or LAG() function -

SELECT [MM_FWReport].[TYPE] AS [TYPE],
       [MM_FWReport].[BUY_AMOUNT] AS [AMOUNT],
       [MM_FWReport].[MM_SZSWIFTL] AS [MM_SZSWIFTL],
       [MM_FWReport].[MM_SZCPAIR] AS [CURRENCY_PAIR],
       [MM_FWReport].[Value_Date] AS [Value_Date],
       [MM_FWReport].[A_Rate] AS [A_Rate],
       [MM_FWReport].[CLIENT_NAME] AS [CLIENT_NAME],
       [MM_FWReport].[TSTATUS] AS [TSTATUS].
       CASE WHEN [MM_FWReport].[TYPE] = 'LONG'
                 AND LEAD([MM_FWReport].[TYPE]) OVER(PATITION BY [MM_FWReport].[MM_SZCPAIR], [MM_FWReport].[Value_Date] ORDER BY [MM_FWReport].[Value_Date]) = 'SHORT'
                 THEN 'NET'
            ELSE 'NOT MATCHED'
       END STATUS
  FROM [dbo].[MM_FWReport] [MM_FWReport]
 ORDER BY [MM_FWReport].[MM_SZCPAIR];

CodePudding user response：

In major sql engines, the functions for your purpose comes as:

LAG() -- get values from previous rows for comparison with current row.

LEAD() -- get values from forward/forthcoming rows for comparison with current row.

On MSSQL, you can do this:

SELECT 
  [MM_FWReport].[TYPE] AS [TYPE]
  ,[MM_FWReport].[BUY_AMOUNT] AS [AMOUNT]
  ,[MM_FWReport].[MM_SZSWIFTL] AS [MM_SZSWIFTL]
  ,[MM_FWReport].[MM_SZCPAIR] AS [CURRENCY_PAIR]
  ,[MM_FWReport].[Value_Date] AS [Value_Date]
  ,[MM_FWReport].[A_Rate] AS [A_Rate]
  ,[MM_FWReport].[CLIENT_NAME] AS [CLIENT_NAME]
  ,[MM_FWReport].[TSTATUS] AS [TSTATUS]
  ,CASE 
    WHEN [MM_FWReport].[TYPE] = 'LONG'
      AND [MM_FWReport] = 25000000
      AND LAG([MM_FWReport].[TYPE]) OVER (PATITION BY [MM_FWReport].[MM_SZCPAIR], [MM_FWReport].[Value_Date] ORDER BY [MM_FWReport].[Value_Date])
        = 'SHORT'
      THEN 'NET'
    ELSE 'NOT MATCHED'
  END AS STATUS
FROM [dbo].[MM_FWReport] [MM_FWReport]
ORDER BY [MM_FWReport].[MM_SZCPAIR];

Have a look at Microsoft Documentation for SQL Server HERE

Was the answer helpful? Consider marking the answer tick and upvoting. Thanks