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

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]

enter image description here

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

sqlsql-serverdata-analysis


Solution 1:[1]

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];

Solution 2:[2]

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 ?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ankit Bajpai
Solution 2

Related Questions