Solved

# Column calculations and inserting into table

Posted on 2008-11-06
633 Views
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
0
Question by:Schuttend

LVL 32

Expert Comment

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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

Author Comment

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

LVL 26

Expert Comment

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

LVL 32

Expert Comment

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 Comment

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

0

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

what is

Equity=Equity (yesterday)

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

LVL 26

Expert Comment

Another question:

What happens when signal from currentdate-2 days = 0
0

LVL 26

Expert Comment

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

LVL 26

Expert Comment

What happens when delta = o

0

Author Comment

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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

Author Comment

Hi,

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

Regards,
Dennis
ErrorEquity.jpg
0

LVL 40

Expert Comment

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

LVL 40

Expert Comment

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

LVL 26

Expert Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 40

Expert Comment

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

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

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

LVL 40

Expert Comment

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

Author Comment

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

LVL 40

Expert Comment

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 Comment

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

Regards,
Dennis
debug.txt
0

Author Comment

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

LVL 26

Expert Comment

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

Author Comment

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

LVL 40

Expert Comment

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

LVL 26

Expert Comment

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

LVL 40

Expert Comment

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

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 Comment

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

LVL 40

Expert Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 26

Expert Comment

Take out the where clause.
0

Author Comment

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

Author Comment

Anybody has more suggestions??
0

LVL 26

Expert Comment

Run only the select part without the Update.
0

LVL 26

Expert Comment

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 Comment

Hi,

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

snap1.jpg
snap2.jpg
0

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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 Comment

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

Author Comment

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

LVL 40

Expert Comment

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 Comment

Hi Sharath_123

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

Author Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 40

Expert Comment

:) 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 Comment

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

Author Comment

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

Author Comment

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

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

Sorry, I meant in that table.
0

Author Comment

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

LVL 40

Expert Comment

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

Author Comment

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 Comment

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

LVL 40

Expert Comment

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 Comment

debug2.txt
0

Author Comment

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

LVL 40

Expert Comment

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 Comment

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

LVL 40

Expert Comment

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 Comment

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 Comment

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.

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

LVL 26

Expert Comment

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

LVL 40

Expert Comment

did you check my query?
0

Author Comment

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 Comment

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

LVL 26

Expert Comment

What exactly is not completely right?
0

Author Comment

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

LVL 51

Expert Comment

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 Comment

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

LVL 51

Expert Comment

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 Comment

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

LVL 51

Accepted Solution

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

Author Closing Comment

Thanks!
0

## Featured Post

### Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in beâ€¦
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paâ€¦
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦