Column calculations and inserting into table

I think you're saying you'd add an Equity column to the existing table -- right?

And that it needs to be calculated every time a record is added to the table -- right?

And that, in order to figure it out, the calculation needs to look back to pre-existing records in the table -- right?

If so, we're looking at a TRIGGER AFTER INSERT, not a calculated field.  And ... we really need a primary key on the table.  If it doesn't have one, can you add an Identity column?

Can you give some data samples. I don't really know hoe to interpret:

Signal from currentdate-2 days

is that comparing the value form Sign column from current date with the one from 2 days ago? If yes how would I know which Signal from 2 days ago if you say there can be more than 1 per same date?

Another thing that would help is to know what types are those columns and the new one, Equity

Hi.
Types columns:
Date date
Signal, number
Equity, number
Systemident, number

The procedure needs to grab a date interval from the table. Next calculate the Equity field and insert into a NEW table.
As there are multiple entries for the same date, different systems are identified with the Systemident field.
For example:
01/01/2000,5,1
01/01/2000,4,2
01/01/2000,-3,3

In the example above you see the same date,with different signal field values. Systemident (the last row) can be taken as key value , because this is really a unique number.
Does this help?

To get all data from system 1:
Select Date,Signal where systemident=1

It helps but you didn't answered my question. Check my 2 posts above.

On what basis can we match the signal to the signal from 2 days ago?  Will systemIdent be the same?  Or ... what would be the matching criterion?

Maybe this is better:

Signal from currentdate-2 days WHERE systemident=x

Currentdate-2 days example:
currentdate : 1-10-2008
currentdate-2 : 1-8-2008

But it's better to refer to it like : currentrecord-2, because currentdate-2 might not exist.
The matching criterion for currentrecord-2 will be [systemident].

Does this answer the question?

But you said before that the systemident column is a unique value, like a key, so you can't have same systemident in 2 different rows. Should I understand that is unique pe day but id can repeat in different days?

Correct, it's unique per day...

Background info:
I have 500.000 different systems. Which generate a signal on a daily basis. Therefore I have multiple entries for the same date, which can be identified with the systemident. Each system will only generate 1 unique signal for a specific day.

what is

Equity=Equity (yesterday)

the equity from yesterday?  It looks recursive how do you get the first equity?

Another question:

What happens when signal from currentdate-2 days = 0

And where do you look for the Equity (yesterday) if you're suppose to enter the values in a different table? In the destination table?

What happens when delta = o

Please make sure you give all the info when asking questions.

I'm sorry, you are completely right.

- When Delta=0 then Equity currentrecord= Equity previous record (no change)
- You look indeed for Equity in the destination table. The Equity will also be identified with [Systemident] and Datevalue
- When signal from currentrecord-2 =0 then also Equity currentrecord=previous record (no change)
- Equity (yesterday) is indeed from yesterday or another defintion currentrecord-1.
 - At the start of the selected date interval all Equities for all [systemsident] are 0. So equities are made or lost during the processing of dates

First try only the select and then if everything OK uncomment the insert lines and run the query again:

--insert into equityTable
--	(Date,Equity,SystemIdent)
select 
	Date,
	case  
		when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
		when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
		when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
		else isnull(eqt.Equity,0)
	end	as Equity,
	SystemIdent
from
	YourTable curr
	left join YourTable twoDaysAgo
		on twoDaysAgo.SystemIdent=curr.SystemIdent
		and twoDaysAgo.Date=curr.Date-2
	left join equityTable eqtYesterday
		on eqtYesterday.Date=curr.date-1
		and eqtYesterday.SystemIdent=curr.SystemIdent

Select allOpen in new window

If you get over existing rows in equityTable then you need to filter them out in the select statement.

Hi,

I am getting errors. To be more precise I have attached a screenshot. Do you have a solution?

Regards,
Dennis
ErrorEquity.jpg

Zbertoec has mentioned eqt.equity where eqt is not at all an alias for any of the table which he used. You will get the second part even though you run the below query. I updated that query to remove the first and third errors.

--insert into equityTable
--      (Date,Equity,SystemIdent)
select
      Curr.Date,
      case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            else isnull(eqt.Equity,0)
      end      as Equity,
      curr.SystemIdent
from
      YourTable curr
      left join YourTable twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and twoDaysAgo.Date=curr.Date-2
      left join equityTable eqtYesterday
            on eqtYesterday.Date=curr.date-1
            and eqtYesterday.SystemIdent=curr.SystemIdent

I just modified his query. I am still trying to understand your requirement clearly.

check this:

--insert into equityTable
--      (Date,Equity,SystemIdent)
select
      Curr.Date,
      case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            else curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
      end      as Equity,
      curr.SystemIdent
from
      YourTable curr
      left join YourTable twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and twoDaysAgo.Date=curr.Date-2
      left join equityTable eqtYesterday
            on eqtYesterday.Date=curr.date-1
            and eqtYesterday.SystemIdent=curr.SystemIdent

There:

--insert into equityTable
--      (Date,Equity,SystemIdent)
select 
        curr.Date,
        case  
                when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
                else isnull(eqtYesterday.Equity,0)
        end     as Equity,
        curr.SystemIdent
from
        YourTable curr
        left join YourTable twoDaysAgo
                on twoDaysAgo.SystemIdent=curr.SystemIdent
                and twoDaysAgo.Date=curr.Date-2
        left join equityTable eqtYesterday
                on eqtYesterday.Date=curr.date-1
                and eqtYesterday.SystemIdent=curr.SystemIdent

Select allOpen in new window

select
      Curr.Date,
      case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
      end      as Equity,
      curr.SystemIdent
from
      YourTable curr
      left join YourTable twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and twoDaysAgo.Date=curr.Date-2
      left join equityTable eqtYesterday
            on eqtYesterday.Date=curr.date-1
            and eqtYesterday.SystemIdent=curr.SystemIdent

Still there is a problem. That inserted in Table [Data] comes from a bulk insert procedure. All fields are varchar as I did not now to define the correct fields like a Date format for the Date field when importing from a file.

Currently I get the error:
Msg 8117, Level 16, State 1, Line 4
Operand data type varchar is invalid for subtract operator.

This sounds logical, so I guess I need to convert the varchars before I can substract.
Or the attached code needs to be changed so all fields can be something else then varchar.

USE [Monday]
GO
/****** Object:  StoredProcedure [dbo].[Bulk_Insert]    Script Date: 11/09/2008 17:10:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Bulk_Insert] as BULK insert Data From 'c:\structure\test.txt' with (Fieldterminator =',', ROWTERMINATOR = '\n')

Select allOpen in new window

check this:

select
      Curr.Date,
      case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
      end      as Equity,
      curr.SystemIdent
from
      YourTable curr
      left join YourTable twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
      left join equityTable eqtYesterday
            on convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
            and eqtYesterday.SystemIdent=curr.SystemIdent

And why not change the structure in the Data table for the date columns from varchar to datetime? This way you don't need to do any convert later.

I already tried this, but it's giving an error about the date not being in the correct format.
That's why i am trying now to input first in a temp table and then do the conversion in sql server it self.
This works for most columns, but not for all, please take a look at :

https://www.experts-exchange.com/questions/23892138/Cast-problem-string-into-integer.html

I responded to that issue as well so check it and try it.

Schuttend - Did you try executing the queries provided here? If you get any error message, can you post here?

Yes, I tried multiple ones. I am getting there.
I changed my input field [date] to varchar(50) and the rest to float.
Next I copy all data from within sql server to a new table which all fields the correct format:
date,float,float,float etc

Next I executed the code below. I am getting output now, but some fields are NULL and this is not possible. Equity should slowly increase.

select 
      Curr.Date,
      case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
      end      as Equity,
      curr.SystemIdent
from
      data curr
      left join data twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
      left join equityTable eqtYesterday
            on convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
            and eqtYesterday.SystemIdent=curr.SystemIdent 

Select allOpen in new window

equity.jpg

Run this query and post the result. so that we can debug whats the problem in Equity.
select
      Curr.Date,
      case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
      end      as Equity,
      curr.SystemIdent,
      eqtYesterday.Equity
      ,curr.Delta
      ,twoDaysAgo.Signal
      ,curr.Signal
from
      data curr
      left join data twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
      left join equityTable eqtYesterday
            on convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
            and eqtYesterday.SystemIdent=curr.SystemIdent  

Please find attached the debug results of the query...

Regards,
Dennis
debug.txt

Also result from SELECT * from Data for comparison
datatbl.txt

So, right now what's the problem? As I see the query works.

Correct, the query works, however this cannot be the correct result.
During time Equity cannot jump to NULL. You can see Equity as a stockprice. This my rise and fall according to the daily delta. But it can never be 0.
It looks like if yesterday equity is not 'remembered' for some reason.

yes, i understand whats wrong here. I will look into this when i get some time.

That is because of your recursive approach and because of the left joins. However you have to use left joins otherwise the unmatching rows will be filtered out. You need to figure out exactly what you need.

>>It looks like if yesterday equity is not 'remembered' for some reason.
 Yes, that is SELECT query only and we are not storing the value of Equity no where.
From the diagram, I understood that the Date, Signal and SystemIndent columns are NULLable. Then what is your primary key? (I assume here as Date and SystemInent as Primary key).

Drop your existing EquityTable and create again. While creating EquityTable, default the Equity column to 0.00
INSERT INTO EquityTable(Date,SystemIndent) SELECT Date,SystemIndent FROM Data

Update EquityTable for Equity column as below
UPDATE EquityTable SET Equity = (case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
           when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
         end )
  FROM EquityTable A
  JOIN Data Curr
    ON Curr.Date = B.Date
   AND Curr.SystemIdent = B.SystemIdent
  LEFT JOIN data twoDaysAgo
    ON twoDaysAgo.SystemIdent=curr.SystemIdent
   AND convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
  LEFT JOIN equityTable eqtYesterday
    ON convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
   AND eqtYesterday.SystemIdent=curr.SystemIdent  

I am not sure whether it will UPDATE each row seperatly. If you didnt get what you want, then you have to use cursor (scanning row by row and then update the Equity).
SELECT * FROM EquityTable  

To be honest I have not set a primary key yet. I am open to any suggestions.
The one thing which is 100% sure:
Date has never a NULL value
Systemident has never a NULL value.

I know that currently it's allowed, but this will never happen while inserting data.

The code is giving an error. Please see attached pic.


errorfor.jpg

Some errors in there, but we are getting there...
Any more ideas??
ident.jpg

Run the below query.
-- Update EquityTable for Equity column as below. This is a comment dude!!!
UPDATE EquityTable SET Equity = (case  
          when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
          when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
        end )
 FROM EquityTable A
 JOIN Data Curr
   ON Curr.Date = B.Date
  AND Curr.SystemIdent = B.SystemIdent
 LEFT JOIN data twoDaysAgo
   ON twoDaysAgo.SystemIdent=curr.SystemIdent
  AND convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
 LEFT JOIN equityTable eqtYesterday
   ON convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
  AND eqtYesterday.SystemIdent=curr.SystemIdent  

Check this:
UPDATE EquityTable SET Equity = (case  
          when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
          when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
        end )
 FROM EquityTable A
 JOIN Data Curr
   ON Curr.Date = A.Date
  AND Curr.SystemIdent = A.SystemIdent
 LEFT JOIN data twoDaysAgo
   ON twoDaysAgo.SystemIdent=curr.SystemIdent
  AND convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
 LEFT JOIN equityTable eqtYesterday
   ON convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
  AND eqtYesterday.SystemIdent=curr.SystemIdent  
 

Sorry about the comment ;) I was too fast with copy past ...

Still a  error with the last code.
Msg 8154, Level 16, State 1, Line 6
The table 'EquityTable' is ambiguous.
ambigious.jpg

I didnt understand about the ambiguous error. just modified the code. check this now.
UPDATE EquityTable SET Equity =
(SELECT case  
         when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
         when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
         when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
         when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
       end AS Equity
FROM Data Curr
LEFT JOIN data twoDaysAgo
  ON twoDaysAgo.SystemIdent=curr.SystemIdent
 AND convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
LEFT JOIN equityTable eqtYesterday
  ON convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
 AND eqtYesterday.SystemIdent=curr.SystemIdent  
WHERE EquityTable.Date = Curr.Date
AND EquityTable.SystemIdent = Curr.SystemIdent)

This code is not giving errors! However , no records are returned...
norows.jpg

Take out the where clause.

I now took out the where clause, still no records are returned....
where.jpg

Anybody has more suggestions??

Run only the select part without the Update.

You said UPDATE EquityTable ... but there is no EquityTable in the FROM clause. You need to have a joint between EquityTable and the rest of the Tables in the FROM clause in order the update to work.

For now run only the SELECT statement ( delete the brackets) to see if you get any record. If you do next step is to join them with EquityTable so that the matching rows will be updated.

Hi,

I did so, but with some errors. Please see enclosed pic...


snap1.jpg
snap2.jpg

Why did you uncommented the WHERE clause? Of course there is error because you have referenced the EquityTable in the WHERE clause but there is no such table in the FROM clause. Comment back the WHERE clause.

The first time was correct. If you want to avoid the NULL values you need to wrap the whole case statement within ISNULL(). Get rid of that AS Equity after the END in CASE statement.

SELECT 
	ISNULL(
		CASE
			...
		END,0)
FROM
	...

Select allOpen in new window

As you can see clearly now, Equity value is not remembered. Therefore value equity will be the delta value each time. And even for some reason also NULL.

--UPDATE EquityTable SET Equity = 
(SELECT case  
        when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
        when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
        when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
        when curr.Signal-isnull(twoDaysAgo.Signal,0) = 0 or curr.delta = 0 then isnull(eqtYesterday.Equity,0)
      end AS Equity,curr.Delta
FROM Data Curr
LEFT JOIN data twoDaysAgo
 ON twoDaysAgo.SystemIdent=curr.SystemIdent
AND convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
LEFT JOIN equityTable eqtYesterday
 ON convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
AND eqtYesterday.SystemIdent=curr.SystemIdent  )
--WHERE EquityTable.Date = Curr.Date
--AND EquityTable.SystemIdent = Curr.SystemIdent) 

Select allOpen in new window

snap3.jpg

I think what you need is below, but I already posted it like 20 posts ago minus the ISNULL around the CASE. Have you tried it?

insert into equityTable
      (Date,Equity,SystemIdent)
select 
        Date,
        ISNULL(case  
                when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
                else isnull(eqt.Equity,0)
        end,0)     as Equity,
        SystemIdent
from
        YourTable curr
        left join YourTable twoDaysAgo
                on twoDaysAgo.SystemIdent=curr.SystemIdent
                and twoDaysAgo.Date=curr.Date-2
        left join equityTable eqtYesterday
                on eqtYesterday.Date=curr.date-1
                and eqtYesterday.SystemIdent=curr.SystemIdent

Select allOpen in new window

The update with a select as it was built makes no sense as you can't do that. What were you doing with that?

As you can see Zberteoc, your code is giving errors.
snap5.jpg

A better version, with less errors, but still not working...

insert into equityTable
      (Date,Equity,SystemIdent)
select 
       curr.Date ,
        ISNULL(case  
                when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
                else isnull(eqt.Equity,0)
        end,0)     as Equity,
        curr.SystemIdent
from
        data curr 
        left join data twoDaysAgo
                on twoDaysAgo.SystemIdent=curr.SystemIdent
                and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
        left join equityTable eqtYesterday on      
                eqtYesterday.SystemIdent=curr.SystemIdent
                and  convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1

Select allOpen in new window

snap6.jpg

insert into equityTable
      (Date,Equity,SystemIdent)
select
       curr.Date ,
        ISNULL(case  
                when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
                else isnull(eqtYesterday.Equity,0)
        end,0)     as Equity,
        curr.SystemIdent
from
        data curr
        left join data twoDaysAgo
                on twoDaysAgo.SystemIdent=curr.SystemIdent
                and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
        left join equityTable eqtYesterday on      
                eqtYesterday.SystemIdent=curr.SystemIdent
                and  convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1

Hi Sharath_123


You code is also not remembering the pervious Equity value. See attached result.
snap7.jpg

Hi Zberteoc.
I rebuild your code into a working one. But also your code does not 'remember' the previous Equity value. So new Equity value insert is alway just : Delta.

insert into equityTable
    (Date,Equity,SystemIdent)
select 
       curr.Date ,
        ISNULL(case  
                when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
                else isnull(Equity,0)
        end,0) ,
        curr.SystemIdent
from
        data curr 
        left join data twoDaysAgo
                on twoDaysAgo.SystemIdent=curr.SystemIdent
                and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
        left join equityTable eqtYesterday on      
                eqtYesterday.SystemIdent=curr.SystemIdent
                and  convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1

Select allOpen in new window

check this:
With CTETab(Date,Equity,SystemIndent) AS(
select
       curr.Date ,
        ISNULL(case  
               when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
               when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
               when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
               else isnull(eqtYesterday.Equity,0)
       end,0) as Equity,
        curr.SystemIdent
from
        data curr
        left join data twoDaysAgo
                on twoDaysAgo.SystemIdent=curr.SystemIdent
                and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
)

SELECT curr.Date ,
        ISNULL(case  
               when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
               when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
               when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
               else isnull(eqtYesterday.Equity,0)
       end,0) as Equity,
        curr.SystemIdent
  FROM data curr
  LEFT JOIN CTETab eqtYesterday
    on eqtYesterday.SystemIdent=curr.SystemIdent
   and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1

This is giving error:
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "eqtYesterday.Equity" could not be bound.

What about this?
With CTETab(Date,Equity,SystemIndent) AS(
select
       curr.Date ,
       ISNULL(case  
              when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
              when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
              when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
              else 0
      end,0) as Equity,
       curr.SystemIdent
from
       data curr
        left join data twoDaysAgo
               on twoDaysAgo.SystemIdent=curr.SystemIdent
               and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
)

SELECT curr.Date ,
       ISNULL(case  
              when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
              when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) - curr.Delta
              when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
              else isnull(eqtYesterday.Equity,0)
      end,0) as Equity,
       curr.SystemIdent
 FROM data curr
 LEFT JOIN CTETab eqtYesterday
    on eqtYesterday.SystemIdent=curr.SystemIdent
  and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1
 

Even more errors:
Msg 207, Level 16, State 1, Line 28
Invalid column name 'SystemIdent'.
Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "twoDaysAgo.Signal" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "twoDaysAgo.Signal" could not be bound.
Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "twoDaysAgo.Signal" could not be bound

execute this:
With CTETab(Date,Equity,SystemIndent,CaseChoice) AS(
select
       curr.Date ,
      ISNULL(case  
             when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
             when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
             when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
             else 0
     end,0) as Equity,
      curr.SystemIdent,
  ISNULL(case  
             when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
             when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
             when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
             else 4
     end,0) as CaseChoice,
from
      data curr
        left join data twoDaysAgo
              on twoDaysAgo.SystemIdent=curr.SystemIdent
              and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
)
SELECT curr.Date ,
      ISNULL(case  
             when eqtYesterday.CaseChoice = 1 then isnull(eqtYesterday.Equity,0) + curr.Delta
             when eqtYesterday.CaseChoice = 2 then isnull(eqtYesterday.Equity,0) - curr.Delta
             when eqtYesterday.CaseChoice = 3 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
             else isnull(eqtYesterday.Equity,0)
     end,0) as Equity,
      curr.SystemIdent
FROM data curr
LEFT JOIN CTETab eqtYesterday
    on eqtYesterday.SystemIdent=curr.SystemIdent
 and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1  

Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'from'.

:) extra comma is there. run this:

With CTETab(Date,Equity,SystemIndent,CaseChoice) AS(
select
       curr.Date ,
     ISNULL(case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
            else 0
    end,0) as Equity,
     curr.SystemIdent,
 ISNULL(case  
            when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
            when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
            else 4
    end,0) as CaseChoice
from
     data curr
        left join data twoDaysAgo
             on twoDaysAgo.SystemIdent=curr.SystemIdent
             and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
)
SELECT curr.Date ,
     ISNULL(case  
            when eqtYesterday.CaseChoice = 1 then isnull(eqtYesterday.Equity,0) + curr.Delta
            when eqtYesterday.CaseChoice = 2 then isnull(eqtYesterday.Equity,0) - curr.Delta
            when eqtYesterday.CaseChoice = 3 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
            else isnull(eqtYesterday.Equity,0)
    end,0) as Equity,
     curr.SystemIdent
FROM data curr
LEFT JOIN CTETab eqtYesterday
    on eqtYesterday.SystemIdent=curr.SystemIdent
and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1  

Msg 207, Level 16, State 1, Line 33
Invalid column name 'SystemIdent'.

I am trying to figure out the error above, but I don't get it (yet)
snap8.jpg

May it be important how which keys are define in table
-Data
-Equitytable ??

At this moment there are no keys defined....

Well, it is clear, the SystemIdent column doesnt exist in the table aliased as eqtYesterday, which is actually the CTETab table. Do you have that column in that database?

Sorry, I meant in that table.

Got the code working. There was a space or ident somewhere.
Still the Equity drops to zero along the way, which is not correct. And cannot happen.

As you can see I added some code for debugging. Please see attached files.

With CTETab(Date,Equity,SystemIndent,CaseChoice) AS(
select 
       curr.Date ,
    ISNULL(case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
           else 0
   end,0) as Equity,
    curr.SystemIdent,
ISNULL(case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
           else 4
   end,0) as CaseChoice
from
    data curr 
        left join data twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2 
) 
SELECT curr.Date ,
    ISNULL(case  
           when eqtYesterday.CaseChoice = 1 then isnull(eqtYesterday.Equity,0) + curr.Delta
           when eqtYesterday.CaseChoice = 2 then isnull(eqtYesterday.Equity,0) - curr.Delta
           when eqtYesterday.CaseChoice = 3 then isnull(eqtYesterday.Equity,0) + abs(curr.Delta)
           else isnull(eqtYesterday.Equity,0)
   end,0) as Equity,
    curr.SystemIdent,curr.delta,curr.signal
FROM data curr
LEFT JOIN CTETab eqtYesterday 
  on eqtYesterday.SystemIndent=curr.SystemIdent
and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1   

Select allOpen in new window

debug1.jpg
debug1.txt

Did you get the correct result now? check your query result.

Sharath did you look at my attached results from my latest post? Result are NOT correct. There are still null values in there which cannot happen

Zberteoc:
Well, it is clear, the SystemIdent table doesnt exist in the table aliased as eqtYesterday, which is actually the CTETab table. Do you have that table in that database?

Please see attached the available tables and their formats. As far as I know it's correct



object.jpg

Just execute this and let me know the result:
Select
       curr.Date ,
    ISNULL(case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
           else 0
   end,0) as Equity,
    curr.SystemIdent,
ISNULL(case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
           else 4
   end,0) as CaseChoice
from
    data curr
        left join data twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2  

Please find attached...
debug2.txt

With dellta value included...As you can see, only delta values are in the equity columns. - or + value may change, but it looks as the twodaysago equity is always 0
debug3.txt

Execute this:
With CTETab(Date,Equity,SystemIndent,CaseChoice) AS(
select
       curr.Date ,
    ISNULL(case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
           else 0
   end,0) as Equity,
    curr.SystemIdent,
ISNULL(case  
           when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
           when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
           else 4
   end,0) as CaseChoice
from
    data curr
        left join data twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
)

SELECT curr.Date ,
       case  
           when curr.CaseChoice = 1 then eqtYesterday.Equity + curr.Equity
           when curr.CaseChoice = 2 then eqtYesterday.Equity - curr.Equity
           when curr.CaseChoice = 3 then eqtYesterday.Equity + abs(curr.Equity)
           else eqtYesterday.Equity
       end as Equity,
    curr.SystemIdent,curr.delta,curr.signal
FROM CTETab curr
LEFT JOIN CTETab eqtYesterday
  on eqtYesterday.SystemIndent=curr.SystemIdent
and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1  

Msg 8159, Level 16, State 1, Line 1
'CTETab' has fewer columns than were specified in the column list.

With CTETab(Date,Equity,SystemIdent,CaseChoice,signal,delta) AS(
select 
       curr.Date ,
   ISNULL(case  
          when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
          else 0
  end,0) as Equity,
   curr.SystemIdent,
ISNULL(case  
          when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
          else 4
  end,0) as CaseChoice
from
   data curr 
        left join data twoDaysAgo
           on twoDaysAgo.SystemIdent=curr.SystemIdent
           and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2 
) 
 
 
SELECT curr.Date ,
      case  
          when curr.CaseChoice = 1 then eqtYesterday.Equity + curr.Equity
          when curr.CaseChoice = 2 then eqtYesterday.Equity - curr.Equity
          when curr.CaseChoice = 3 then eqtYesterday.Equity + abs(curr.Equity)
          else eqtYesterday.Equity
      end as Equity,
   curr.SystemIdent,curr.delta,curr.signal
FROM CTETab curr
LEFT JOIN CTETab eqtYesterday 
  on eqtYesterday.SystemIdent=curr.SystemIdent
and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1 

Select allOpen in new window

With CTETab(Date,Equity,SystemIdent,CaseChoice) AS(
select
       curr.Date ,
   ISNULL(case  
          when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then curr.Delta
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then abs(curr.Delta)
          else 0
  end,0) as Equity,
   curr.SystemIdent,
ISNULL(case  
          when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then 1
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then 2
          when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then 3
          else 4
  end,0) as CaseChoice
from
   data curr
        left join data twoDaysAgo
           on twoDaysAgo.SystemIdent=curr.SystemIdent
           and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
)
 
 SELECT curr.Date ,
      case  
          when curr.CaseChoice = 1 then eqtYesterday.Equity + curr.Equity
          when curr.CaseChoice = 2 then eqtYesterday.Equity - curr.Equity
          when curr.CaseChoice = 3 then eqtYesterday.Equity + abs(curr.Equity)
          else eqtYesterday.Equity
      end as Equity,
   curr.SystemIdent
FROM CTETab curr
LEFT JOIN CTETab eqtYesterday
  on eqtYesterday.SystemIdent=curr.SystemIdent
and convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1

Let me first apologize for my action. I made a mistake in the signal calculation part, therefore NULL or 0 values were generated. Please see correct code below.Instead of 3 possibilities there are now 4.

This code is generating output, but yesterday equity is still not stored for some reason.

insert into equityTable
     (Date,Equity,SystemIdent)
select 
       curr.Date ,
       ISNULL(case  
               when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
               when curr.Signal-isnull(twoDaysAgo.Signal,0) > 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) + curr.Delta
               when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta < 0 then isnull(eqtYesterday.Equity,0) +abs(curr.Delta)
                when curr.Signal-isnull(twoDaysAgo.Signal,0) < 0 and curr.delta > 0 then isnull(eqtYesterday.Equity,0) -abs(curr.Delta)
               else isnull(eqtYesterday.Equity,0)
       end,0)     as Equity,
       curr.SystemIdent
from
       data curr 
        left join data twoDaysAgo
               on twoDaysAgo.SystemIdent=curr.SystemIdent
               and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2
       left join equityTable eqtYesterday on      
               eqtYesterday.SystemIdent=curr.SystemIdent
               and  convert(datetime,eqtYesterday.Date)=convert(datetime,curr.date)-1

Select allOpen in new window

order.txt
order.jpg

Hi,

I might have found a solution. It's about referring to date-1 or date-2. The problem with this during weekends there are no dates to query, therefore the resul will be a NULL value. Please see attached pic. The same problem will rise with not 'remembering' the Equity value. That might be the reason.

I think a solution might be is to refer to indexkey-1 or indexkey-2. What is your opion about this?
And what would be the code for this?

select 
      Curr.Date,curr.[Close],twodaysago.[Close] as TwoDaysago_CloseValue,
      
      curr.SystemIdent,curr.Signal,curr.delta
from
      data curr
      left join data twoDaysAgo
            on twoDaysAgo.SystemIdent=curr.SystemIdent
            and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)-2

Select allOpen in new window

solution.jpg

If i get it right what you could do is to check what is the current day, if Monday go 3 days back if Tuesday go 4 days back, to Friday in both these cases. I am not sure if this is what you want but might be a direction.

select 
	Curr.Date,curr.[Close],
	twodaysago.[Close] as TwoDaysago_CloseValue,
	curr.SystemIdent,curr.Signal,curr.delta
from
	data curr
	left join data twoDaysAgo
		on twoDaysAgo.SystemIdent=curr.SystemIdent
		and convert(datetime,twoDaysAgo.Date)=convert(datetime,curr.Date)- 
			case 
				when datename(dw,convert(datetime,curr.Date))='Monday' then 3
				when datename(dw,convert(datetime,curr.Date))='Tuesday' then 4
				else 2
			end

Select allOpen in new window

did you check my query?

Hi Sharath,

Yes I did so. But did you check my latest messages about the date-2 problems in combination with the NULL values?

hi Zberteoc,

As can see it's not completely right. I was more think about adding a integer value to the imported file, which can be used a key index. In that way it;s quite easy to refer to currentrecord-2 instead of date-2.
What do you think?
date.jpg

What exactly is not completely right?

Hi,

I rebuild the table data and added a indexnumber to it. So instead of date-2 I have now keyindex-2. Much easier to handle. What would now be the next step with calculating the Equity as required?

select 
	Curr.Date,curr.[Close],
	twodaysago.[Close] as TwoDaysago_CloseValue,
	curr.SystemIdent,curr.Signal,curr.delta
from
	data curr
	left join data twoDaysAgo
		on twoDaysAgo.SystemIdent=curr.SystemIdent
		and twodaysago.Col1=curr.Col1-2 

Select allOpen in new window

2daysago.jpg

inspired by zberteoc entry 22900741

realised that we need a recursive query.

Using tmp_equity_table (as an example - you need to give equity a "real" home) to first create entries, then secondly to update those entries with a CTE query.

Now, couply of things, equity will need a real home, so we either give tmp_equity_table a more meaning name, or, we use that as a basis to update the real equity table. Either way there are pros and cons. If it is to be the "real" home, then we should not recreate each time (obviously) and we need to only insert those entries that do not already exist rather than doing a select into...

-- create indexes on DATA just as a once-off should be there...
create index idx_data_col1 on data (col1)
create index idx_data_date on data (systemident,date)
 
-- we will create an equity table with the neccessary columns (like two days ago signal) to make calcs work better...
if object_id('tmp_equity_table','U') is not NULL drop table tmp_equity_table
go
 
-- we will populate that temp table
select currentday.col1, currentday.systemident, convert(varchar,currentday.date) as date, currentday.signal, (select top 1 signal from data twodaysago where twodaysago.systemident = currentday.systemident and twodaysago.col1 <= currentday.col1 - 2 order by twodaysago.col1 desc) as twodaysago_signal, delta, convert(decimal(36,18),0.0000) as equity
into tmp_equity_table
from data currentday 
go
 
-- and index it for the recursive query to work a whole lot better
create index idx_tmp_equity_table_col1 on tmp_equity_table (col1)
create index idx_tmp_equity_table_systemident on tmp_equity_table (systemident,col1)
go
 
-- now run a recursive query over the tmp table picking up "yesterday's" equity
with Equity_Set (col1, systemident, date, equity)
AS
(
select 0 as col1, 0 as systemident,convert(varchar,'19000101') as date,convert(decimal(18,6),0.000000) as equity
union all
select currentday.col1, currentday.systemident, currentday.date, 
Case when e.systemident = currentday.systemident then
convert(decimal(18,6),(case   
           when currentday.Signal-isnull(twodaysago_signal,0) > 0 and currentday.delta > 0 then isnull(e.Equity,0) + currentday.Delta
           when currentday.Signal-isnull(twodaysago_signal,0) > 0 and currentday.delta < 0 then isnull(e.Equity,0) - currentday.Delta
           when currentday.Signal-isnull(twodaysago_signal,0) < 0 and currentday.delta < 0 then isnull(e.Equity,0) + abs(currentday.Delta)
           when currentday.Signal-isnull(twodaysago_signal,0) < 0 and currentday.delta > 0 then isnull(e.Equity,0) - abs(currentday.Delta)
       else isnull(e.Equity,0)
      end))
else 0 end as equity
from tmp_equity_table currentday
inner join equity_set e on e.col1 + 1 = currentday.col1
)
 
update tmp_equity_table set equity = es.equity
from tmp_equity_table
inner join equity_set es on es.col1 = tmp_equity_table.col1
OPTION (MAXRECURSION 0);
 
-- now check results
select * from tmp_equity_table order by col1

Select allOpen in new window

Let me give some background info. I have huge amount of data in SQL server. User select time interval to process. This interval is copied into table [data] and needs to be added with column [Equity]. Thereafter this 'new' table needs to be queried based on criteria like:
select date,signal,signalident where equity>x
Later on I need also additional calculations made like [equity] and also added to this table for executing queries.

About your code. It's working, almost.
What I don't understand if the fact that there already is equity generated while the twodaysago signal=NULL. This is not possible. The first 2 days equity can only be 0 or NULL. Please see attached pic...

equitycalc.jpg

yes, we can agree that the first one must be zero - there is no "twodaysago" and there certainly is no "yesterday" so must end up zero (in so much as cannot link to "yesterday" if nothing else).

So, lets look at the second instance, when there is a "yesterday".

The first part of the calculation is saying currentday.signal - isnull(twodaysago_signal,0) so when it is NULL we are effectively saying "just currentday.signal" which is 0.289338077265122

and then we are checking currentday.delta which is 67.06

so 0.298 is > 0 and 67.06 is > 0 and yesterdays equity is 0.00 so todays equity becomes 67.06.

maybe the check of twodaysagosignal should not be an isnull as such, but should be dependant on "is not NULL" ie:

when twodaysago_signal is not NULL and currentday.Signal-isnull(twodaysago_signal,0) > 0 and currentday.delta > 0 then isnull(e.Equity,0) + currentday.Delta

Indeed the NULL values are gone, however the equity values are incorrect now as you can see in attached pic.

And some other small issues:
- I need the Equity field into the Data table, would this be possible?
- If the code is executed more then once then errors are generated, because:
 the existing tmp_equity_table is NOT dropped for some reason
query is trying to create indexes, but on the second run they already exists, thus errors are generated

-- create indexes on DATA just as a once-off should be there...
--create index idx_data_col1 on data (col1)
--create index idx_data_date on data (systemident,date)
 
-- we will create an equity table with the neccessary columns (like two days ago signal) to make calcs work better...
if object_id('tmp_equity_table','U') is not NULL drop table tmp_equity_table
go
 
-- we will populate that temp table
select currentday.col1, currentday.systemident, convert(varchar,currentday.date) as date, currentday.signal, (select top 1 signal from data twodaysago where twodaysago.systemident = currentday.systemident and twodaysago.col1 <= currentday.col1 - 2 order by twodaysago.col1 desc) as twodaysago_signal, delta, convert(decimal(36,18),0.0000) as equity
into tmp_equity_table
from data currentday 
go
 
-- and index it for the recursive query to work a whole lot better
create index idx_tmp_equity_table_col1 on tmp_equity_table (col1)
create index idx_tmp_equity_table_systemident on tmp_equity_table (systemident,col1)
go
 
-- now run a recursive query over the tmp table picking up "yesterday's" equity
with Equity_Set (col1, systemident, date, equity)
AS
(
select 0 as col1, 0 as systemident,convert(varchar,'19000101') as date,convert(decimal(18,6),0.000000) as equity
union all
select currentday.col1, currentday.systemident, currentday.date, 
Case when e.systemident = currentday.systemident then
convert(decimal(18,6),(case  
 when twodaysago_signal is not NULL and currentday.Signal-isnull(twodaysago_signal,0) > 0 and currentday.delta > 0 then isnull(e.Equity,0) + currentday.Delta
 when twodaysago_signal is not NULL and currentday.Signal-isnull(twodaysago_signal,0) > 0 and currentday.delta < 0 then isnull(e.Equity,0) - abs(currentday.Delta)
 when twodaysago_signal is not NULL and currentday.Signal-isnull(twodaysago_signal,0) < 0 and currentday.delta < 0 then isnull(e.Equity,0) + abs(currentday.Delta)   
 when twodaysago_signal is not NULL and currentday.Signal-isnull(twodaysago_signal,0) < 0 and currentday.delta > 0 then isnull(e.Equity,0) - abs(currentday.Delta  )
               else isnull(e.Equity,0)
      end))
else 0 end as equity
from tmp_equity_table currentday
inner join equity_set e on e.col1 + 1 = currentday.col1
)
 
update tmp_equity_table set equity = es.equity
from tmp_equity_table
inner join equity_set es on es.col1 = tmp_equity_table.col1
OPTION (MAXRECURSION 0);
 
-- now check results
select * from tmp_equity_table order by col1

Select allOpen in new window

calculation.jpg

Thanks!