CODEWITHSUNDEEP
t-sql
Eon
Eon

Reputation: 3974

Comparison of two rows in TSQL

Good Day

I have the following result set returned: 

financeYearEnd          FromDate    ToDate      ClientPortfolioCode
2013-12-31 00:00:00.000 2014-01-01  2014-01-31  C1
2013-12-31 00:00:00.000 2014-01-01  2014-01-31  C2
2012-12-31 00:00:00.000 2013-12-01  2013-12-31  C1
2012-12-31 00:00:00.000 2013-12-01  2013-12-31  C2

What I need to do is the following:

I need to compare the financeYearEnd of all the C1 Fields (there will always only be two), and see if they are different to each other

 2013-12-31 00:00:00.000    2014-01-01  2014-01-31  C1
 2012-12-31 00:00:00.000    2013-12-01  2013-12-31  C1

As seen, the financeYearEnd does differ, so I need to store that result as a row in a temporary Table.

This needs to be done for all distinct ClientPortfolioCodes(Which will always appear in groups of two)

How can this be achieved?

I have tried select distinct .. - didn't work. It returned all my rows

EDIT - 

WITH cteCompareTopTwoYears
AS (
    SELECT TOP (
            SELECT COUNT(*) * 2
            FROM #ClientPortFolios
            ) FinancialYearEnd AS financeYearEnd
        ,FromDate
        ,ToDate
        ,CA.ClientPortfolioCode
    FROM rpt.F3_fn_ClientPortfolios_CapitalAccount_IncludingYTD CA
    WHERE (
            (
                CA.FromDate = (DATEADD(m, - 1, @FromDate))
                AND CA.ToDate = (DATEADD(m, - 1, @ToDate))
                )
            OR (
                CA.FromDate = @FromDate
                AND CA.ToDate = @ToDate
                )
            )
        AND (
            CA.ClientPortFolioCode IN (
                SELECT ClientPortfolioCode
                FROM #ClientPortfolios
                )
            )
    ORDER BY FromDate DESC
    )
SELECT *
FROM cteCompareTopTwoYears c

Upvotes: 0

Views: 44

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460038

Perhaps with the help of ROW_NUMBER, e.g.:

WITH CTE AS
(
    SELECT financeYearEnd, FromDate, ToDate, ClientPortfolioCode,
           rn = row_Number () OVER (Partition By ClientPortfolioCode
                                    Order By financeYearEnd ASC)
    FROM dbo.TableName
)
SELECT financeYearEnd, FromDate, ToDate, ClientPortfolioCode
FROM CTE
WHERE rn > 1

Demo

Upvotes: 1

Related Questions