[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Column calculations and inserting into table

Posted on 2008-11-06
95
Medium Priority
?
649 Views
Last Modified: 2011-10-19
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
Comment
Question by:Schuttend
  • 43
  • 26
  • 21
  • +2
95 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22899028
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 27

Expert Comment

by:Zberteoc
ID: 22899080
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 27

Expert Comment

by:Zberteoc
ID: 22899092
Another thing that would help is to know what types are those columns and the new one, Equity
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:Schuttend
ID: 22899217
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 27

Expert Comment

by:Zberteoc
ID: 22899275
It helps but you didn't answered my question. Check my 2 posts above.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22899302
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

by:Schuttend
ID: 22899348
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22899416
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

by:Schuttend
ID: 22899508
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 27

Expert Comment

by:Zberteoc
ID: 22899622
what is

Equity=Equity (yesterday)

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

Expert Comment

by:Zberteoc
ID: 22899634
Another question:

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

Expert Comment

by:Zberteoc
ID: 22899643
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 27

Expert Comment

by:Zberteoc
ID: 22899656
What happens when delta = o

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

Author Comment

by:Schuttend
ID: 22899782
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 27

Expert Comment

by:Zberteoc
ID: 22900741
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22900754
If you get over existing rows in equityTable then you need to filter them out in the select statement.
0
 

Author Comment

by:Schuttend
ID: 22913913
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 41

Expert Comment

by:Sharath
ID: 22915219
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 41

Expert Comment

by:Sharath
ID: 22915229
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 27

Expert Comment

by:Zberteoc
ID: 22915399
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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22915440
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

by:Schuttend
ID: 22916749
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')

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22918367
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 27

Expert Comment

by:Zberteoc
ID: 22932360
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

by:Schuttend
ID: 22932751
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 27

Expert Comment

by:Zberteoc
ID: 22933172
I responded to that issue as well so check it and try it.
0
 
LVL 41

Expert Comment

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

Author Comment

by:Schuttend
ID: 22933992
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 

Open in new window

equity.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22934043
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

by:Schuttend
ID: 22934198
Please find attached the debug results of the query...

Regards,
Dennis
debug.txt
0
 

Author Comment

by:Schuttend
ID: 22934265
Also result from SELECT * from Data for comparison
datatbl.txt
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22934893
So, right now what's the problem? As I see the query works.
0
 

Author Comment

by:Schuttend
ID: 22935034
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 41

Expert Comment

by:Sharath
ID: 22935065
yes, i understand whats wrong here. I will look into this when i get some time.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22935099
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 41

Expert Comment

by:Sharath
ID: 22935198
>>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

by:Schuttend
ID: 22937731
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

by:Schuttend
ID: 22940253
Some errors in there, but we are getting there...
Any more ideas??
ident.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22943112
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 41

Expert Comment

by:Sharath
ID: 22943160
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

by:Schuttend
ID: 22943282
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 41

Expert Comment

by:Sharath
ID: 22943442
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

by:Schuttend
ID: 22943493
This code is not giving errors! However , no records are returned...
norows.jpg
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22945210
Take out the where clause.
0
 

Author Comment

by:Schuttend
ID: 22947909
I now took out the where clause, still no records are returned....
where.jpg
0
 

Author Comment

by:Schuttend
ID: 22948524
Anybody has more suggestions??
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22950689
Run only the select part without the Update.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22950747
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

by:Schuttend
ID: 22952796
Hi,

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


snap1.jpg
snap2.jpg
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22952880
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 27

Expert Comment

by:Zberteoc
ID: 22952923
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
	...

Open in new window

0
 

Author Comment

by:Schuttend
ID: 22952960
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) 

Open in new window

snap3.jpg
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22952971
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22952984
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

by:Schuttend
ID: 22953089
As you can see Zberteoc, your code is giving errors.
snap5.jpg
0
 

Author Comment

by:Schuttend
ID: 22953269
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

Open in new window

snap6.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22953326
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

by:Schuttend
ID: 22953381
Hi Sharath_123


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

Author Comment

by:Schuttend
ID: 22953413
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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22953974
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

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

Expert Comment

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

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

Author Comment

by:Schuttend
ID: 22954255
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 41

Expert Comment

by:Sharath
ID: 22954476
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

by:Schuttend
ID: 22954510
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'from'.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22954561
:) 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

by:Schuttend
ID: 22954669
Msg 207, Level 16, State 1, Line 33
Invalid column name 'SystemIdent'.
0
 

Author Comment

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

Author Comment

by:Schuttend
ID: 22955178
May it be important how which keys are define in table
-Data
-Equitytable ??

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

Expert Comment

by:Zberteoc
ID: 22955246
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 27

Expert Comment

by:Zberteoc
ID: 22955249
Sorry, I meant in that table.
0
 

Author Comment

by:Schuttend
ID: 22955266
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   

Open in new window

debug1.jpg
debug1.txt
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22955317
Did you get the correct result now? check your query result.
0
 

Author Comment

by:Schuttend
ID: 22955372
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

by:Schuttend
ID: 22955433
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 41

Expert Comment

by:Sharath
ID: 22955480
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

by:Schuttend
ID: 22955500
Please find attached...
debug2.txt
0
 

Author Comment

by:Schuttend
ID: 22955532
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 41

Expert Comment

by:Sharath
ID: 22955617
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

by:Schuttend
ID: 22955683
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 

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22955789
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

by:Schuttend
ID: 22958093
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

Open in new window

order.txt
order.jpg
0
 

Author Comment

by:Schuttend
ID: 22959650
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

Open in new window

solution.jpg
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22961220
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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22962757
did you check my query?
0
 

Author Comment

by:Schuttend
ID: 22963429
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

by:Schuttend
ID: 22963488
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 27

Expert Comment

by:Zberteoc
ID: 22964198
What exactly is not completely right?
0
 

Author Comment

by:Schuttend
ID: 22968275
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 

Open in new window

2daysago.jpg
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23014804
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

Open in new window

0
 

Author Comment

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

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

equitycalc.jpg
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23020285
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

by:Schuttend
ID: 23021888
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

Open in new window

calculation.jpg
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 23024350
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

by:Schuttend
ID: 31514091
Thanks!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question