# Column calculations and inserting into table

Hello,

I have the following format:
Date,Signal,SystemIdent,Delta

There can be multiple entries on the same Date with different values of Signal and Signal Ident.
I need to calculate a new column called Equity.

Equity column is derived from the following calculation.
If Signal from currentdate-2 days >0 and delta>0 then Equity=Equity (yesterday) + Delta

If signal from currentdate-2 days<0 and delta>0 then Equity=Equity(yesterday) - Delta
If signal from currentdate-2 days<0 and delta<0 then Equity=Equity(yesterday) + abs(Delta)

The Equity column must then be inserted into a new table.
Can somebody give me a example code how to do this in sql server 2008?

regards,
Dennis
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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?
0
Commented:
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?
0
Commented:
Another thing that would help is to know what types are those columns and the new one, Equity
0
Author Commented:
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
0
Commented:
It helps but you didn't answered my question. Check my 2 posts above.
0
Commented:
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?
0
Author Commented:
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?
0
Commented:
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?
0
Author Commented:
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.
0
Commented:
what is

Equity=Equity (yesterday)

the equity from yesterday?  It looks recursive how do you get the first equity?
0
Commented:
Another question:

What happens when signal from currentdate-2 days = 0
0
Commented:
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?
0
Commented:
What happens when delta = o

Please make sure you give all the info when asking questions.
0
Author Commented:
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
0
Commented:
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
``````
0
Commented:
If you get over existing rows in equityTable then you need to filter them out in the select statement.
0
Author Commented:
Hi,

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

Regards,
Dennis
ErrorEquity.jpg
0
Data EngineerCommented:
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.
0
Data EngineerCommented:
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
0
Commented:
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
``````
0
Data EngineerCommented:
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
0
Author Commented:
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')
``````
0
Data EngineerCommented:
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
0
Commented:
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.
0
Author Commented:
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 :

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23892138.html
0
Commented:
I responded to that issue as well so check it and try it.
0
Data EngineerCommented:
Schuttend - Did you try executing the queries provided here? If you get any error message, can you post here?
0
Author Commented:
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
``````
equity.jpg
0
Data EngineerCommented:
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
0
Author Commented:
Please find attached the debug results of the query...

Regards,
Dennis
debug.txt
0
Author Commented:
Also result from SELECT * from Data for comparison
datatbl.txt
0
Commented:
So, right now what's the problem? As I see the query works.
0
Author Commented:
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.
0
Data EngineerCommented:
yes, i understand whats wrong here. I will look into this when i get some time.
0
Commented:
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.
0
Data EngineerCommented:
>>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
0
Author Commented:
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
0
Author Commented:
Some errors in there, but we are getting there...
Any more ideas??
ident.jpg
0
Data EngineerCommented:
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
0
Data EngineerCommented:
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

0
Author Commented:
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
0
Data EngineerCommented:
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)
0
Author Commented:
This code is not giving errors! However , no records are returned...
norows.jpg
0
Commented:
Take out the where clause.
0
Author Commented:
I now took out the where clause, still no records are returned....
where.jpg
0
Author Commented:
Anybody has more suggestions??
0
Commented:
Run only the select part without the Update.
0
Commented:
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.
0
Author Commented:
Hi,

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

snap1.jpg
snap2.jpg
0
Commented:
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.
0
Commented:
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
...
``````
0
Author Commented:
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)
``````
snap3.jpg
0
Commented:
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
``````
0
Commented:
The update with a select as it was built makes no sense as you can't do that. What were you doing with that?
0
Author Commented:
As you can see Zberteoc, your code is giving errors.
snap5.jpg
0
Author Commented:
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
``````
snap6.jpg
0
Data EngineerCommented:
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
0
Author Commented:
Hi Sharath_123

You code is also not remembering the pervious Equity value. See attached result.
snap7.jpg
0
Author Commented:
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
``````
0
Data EngineerCommented:
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
0
Author Commented:
This is giving error:
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "eqtYesterday.Equity" could not be bound.
0
Data EngineerCommented:
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

0
Author Commented:
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
0
Data EngineerCommented:
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
0
Author Commented:
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'from'.
0
Data EngineerCommented:
:) 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
0
Author Commented:
Msg 207, Level 16, State 1, Line 33
Invalid column name 'SystemIdent'.
0
Author Commented:
I am trying to figure out the error above, but I don't get it (yet)
snap8.jpg
0
Author Commented:
May it be important how which keys are define in table
-Data
-Equitytable ??

At this moment there are no keys defined....
0
Commented:
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?
0
Commented:
Sorry, I meant in that table.
0
Author Commented:
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
``````
debug1.jpg
debug1.txt
0
Data EngineerCommented:
Did you get the correct result now? check your query result.
0
Author Commented:
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
0
Author Commented:
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
0
Data EngineerCommented:
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
0
Author Commented:
debug2.txt
0
Author Commented:
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
0
Data EngineerCommented:
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
0
Author Commented:
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
``````
0
Data EngineerCommented:
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
0
Author Commented:
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
``````
order.txt
order.jpg
0
Author Commented:
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
``````
solution.jpg
0
Commented:
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
``````
0
Data EngineerCommented:
did you check my query?
0
Author Commented:
Hi Sharath,

Yes I did so. But did you check my latest messages about the date-2 problems in combination with the NULL values?
0
Author Commented:
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
0
Commented:
What exactly is not completely right?
0
Author Commented:
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
``````
2daysago.jpg
0
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
``````
0
Author Commented:
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.

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
0
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

0
Author Commented:
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
``````
calculation.jpg
0
sure, can update the DATA table, just change tmp_equity_table in the update statement to DATA ie:

update data set equity = es.equity
from data
inner join equity_set es on es.col1 = data.col1
OPTION (MAXRECURSION 0);

with regard running more than one, then I am not sure - I can run it several times in succession without error - cannot run it concurrently though (it is "single stream")

But not sure what you are saying about the calcs... Maybe some examples of what you are expecting ?  Looking at the very top,  your calc requirements are :

If Signal from currentdate-2 days >0 and delta>0 then Equity=Equity (yesterday) + Delta
If signal from currentdate-2 days<0 and delta>0 then Equity=Equity(yesterday) - Delta
If signal from currentdate-2 days<0 and delta<0 then Equity=Equity(yesterday) + abs(Delta)

seems to be missing : Signal from currentdate-2 days >0 and delta<0  , or I have added, that condition into the formula. Is that what is wrong ?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.