Here is the problem. I have two tables

Transaction_Table

Valuation_Table (The table contains the valuation for all dates and all trades)

I am trying to calculate a valuation movement of the trades based on when they were purchased and sold in relation to the financial year which is say 1/01/2020 - 31/12/2020 The rules are as follows:

1. Trade is purchased before the beginning of financial year and not sold before the end --> Movement calculated as 1/01/2020-31/12/2020
2. Trade is purchased and sold during the financial year --> Movement calculated as purchase date - sell date
3. Trade was purchased during the financial year, but not sold --> purchase date - 31/12/20
4. Trade is purchased before the financial year and sold during the finncial year --> 1/01/2020 - sell date

I have added valuation start and end dates to transaction table with CASE in the select statement like so.

    Select *
    Case
    When t.purchase date < 1/01/2020 and (t.sell date is not null or t.sell date > 31/12/2020) THEN 01/01/2020
    When t.purchase date < 1/01/2020 and t.sell date < 31/12/2020 THEN t.sell date
    When t.purchase date > 1/01/2020 and (t.sell date is not null or t.sell date > 31/12/2020) THEN 31/12/2020
    When t.purchase date > 1/01/2020 and (t.sell date is not null or t.sell date < 31/12/2020) THEN t.sell date
    END as [Start Date]
    
    
    Case
    When t.purchase date < 1/01/2020 and (t.sell date is not null or t.sell date > 31/12/2020) THEN 31/12/2020
    When t.purchase date < 1/01/2020 and t.sell date < 31/12/2020 THEN t.sell date
    When t.purchase date > 1/01/2020 and (t.sell date is not null or t.sell date > 31/12/2020) THEN 31/12/2020
    When t.purchase date > 1/01/2020 and (t.sell date is not null or t.sell date < 31/12/2020) THEN t.sell date
    END as [End Date]

FROM Transaction_table t

This creates the following table

What I am struggling with is now is to add the valuations from the valuation table based on the start and end date created in the select statement. The problem seems to be that those columns only came in the existence through the select statement and don't exist in the original transaction table.

The End result should look like this

CodePudding user response：

If you need to use the results of calculated columns, just use derived table sub-queries (of which a CTE as I have used is one form).

Its interesting that your query as posted is full of syntax errors though. Have you actually got it working?

And as philipxy notes, your date format is ambiguous and could lead to issues. But I'll leave that as an issue for you to resolve.

I would also recommend not using spaces in your columns names, because you end up having to escape them all.

with cte1 as (
    Select *

        , Case
        When t.[purchase date] < '1/01/2020' and (t.[sell date] is not null or t.[sell date] > '31/12/2020') THEN '01/01/2020'
        When t.[purchase date] < '1/01/2020' and t.[sell date] < '31/12/2020' THEN t.[sell date]
        When t.[purchase date] > '1/01/2020' and (t.[sell date] is not null or t.[sell date] > '31/12/2020') THEN '31/12/2020'
        When t.[purchase date] > '1/01/2020' and (t.[sell date] is not null or t.[sell date] < '31/12/2020') THEN t.[sell date]
        END as [Start Date]
    
        , Case
        When t.[purchase date] < '1/01/2020' and (t.[sell date] is not null or t.[sell date] > '31/12/2020') THEN '31/12/2020'
        When t.[purchase date] < '1/01/2020' and t.[sell date] < '31/12/2020' THEN t.[sell date]
        When t.[purchase date] > '1/01/2020' and (t.[sell date] is not null or t.[sell date] > '31/12/2020') THEN '31/12/2020'
        When t.[purchase date] > '1/01/2020' and (t.[sell date] is not null or t.[sell date] < '31/12/2020') THEN t.[sell date]
        END as [End Date]

    FROM Transaction_table t
), cte2 as (
    select *
        , (select Valuation from Valuation_Table V where V.[Trade Id] = T.[Trade Id] and V.[Valuation Date] = T.[Start Date]) [Valuation Start]
        , (select Valuation from Valuation_Table V where V.[Trade Id] = T.[Trade Id] and V.[Valuation Date] = T.[End Date]) [Valuation End]
    from cte2 T
)
select *, [Valuation End]- [Valuation Start] Movement
from cte2;