November 8, 2018 at 10:48 am
Hi all, new to the forum here. I'm pretty rusty after not having touched this stuff for a few years and I can't figure out how to get what I want.
I am building a database to calculate investment performance.
At the moment, I'm attempting to have the daily ledger computed based on information contained in the following tables:
cashflows
dividends
interest
trades
I want to create a table/query that will combine all of these by date and account. I'm running into a problem, e.g. interest is paid on the 15th, but there is no cashflow, no trade, and no dividend on the 15th, so I can't join them on dates since some tables don't have entries on a particular date.
I have a table that contains security prices for each trading day, and I have created a query(view) that aggregates the dates from the security pricing table, cashflows, dividends, interest, and trades, so that I have every possible date that something could occur.
I want to have a query that shows:
id_account, date, amount_cashflows, amount_interest, amount_dividends
if there is no record in the cashflows, interest, or dividends tables on a particular date for a particular account id then 0
Here's the table definitions:
CREATE TABLE [dbo].[cashflows](
[id] [int] IDENTITY(1,1) NOT NULL,
[id_account] [int] NOT NULL,
[date_cashflow] [date] NOT NULL,
[amount] [numeric](18, 6) NOT NULL,
[notes] [varchar](150) NULL,
CONSTRAINT [PK_cashflows] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dividends](
[id] [int] IDENTITY(1,1) NOT NULL,
[id_account] [int] NOT NULL,
[id_security] [int] NOT NULL,
[date_payment] [date] NOT NULL,
[amount] [numeric](18, 6) NOT NULL,
CONSTRAINT [PK_dividends] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[interest](
[id] [int] IDENTITY(1,1) NOT NULL,
[id_account] [int] NOT NULL,
[date_interest] [date] NOT NULL,
[amount_interest] [numeric](18, 6) NOT NULL,
CONSTRAINT [PK_interest] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[trades](
[id] [int] IDENTITY(100000,1) NOT NULL,
[id_account] [int] NOT NULL,
[trade_date] [date] NOT NULL,
[id_trade_type] [int] NOT NULL,
[id_security] [int] NOT NULL,
[shares] [numeric](18, 6) NOT NULL,
[price] [numeric](18, 6) NOT NULL,
[commission] [numeric](18, 6) NOT NULL,
[fees] [numeric](18, 6) NOT NULL,
[notes] [varchar](150) NULL,
CONSTRAINT [PK_trades] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks in advance for any help, I'm rusty and my trial and error methods are failing here.
November 8, 2018 at 11:04 am
Create a Calendar table and use that as the central table to join the other tables using a left outer join from the Calendar table to the other tables.
November 8, 2018 at 1:40 pm
Lynn's suggestion is the way the go. Since your a little rusty here is an example on how to create a calendar table.
I normally have a dedicated Tally table and use permanent tables but this is something you can run for demonstration purposes. You can play around with it by adjusting the tally count.
DECLARE @Calendar TABLE (myDays DATE)
;WITH Tally (n) AS
(
-- 100 rows
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
INSERT INTO @Calendar (myDays)
SELECT
DATEADD(DAY, t.n, GETDATE())
FROM Tally t
SELECT * FROM @Calendar
November 9, 2018 at 10:04 am
thanks all for the help, this jogged my memory and worked perfectly
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply