This is a challenge for someone experienced with SQL (MS-Access in this case)
对于有SQL经验的人来说这是一个挑战(在这种情况下为MS-Access)
I have 2 tables: holdings and valuations.
我有2个表:持有和估值。
holdings contains all the assets a particular account holds on a given date and their respective values. These are the fields:
馆藏包含特定帐户在给定日期持有的所有资产及其各自的价值。这些是领域:
id{primary key/auto-inc}, accountid {int}, holdingdate {date}, holdingid {int}, holdingvalue {double}
id {primary key / auto-inc},accountid {int},holdingdate {date},holdingid {int},holdingvalue {double}
valuations contains the sum of the holdings of a particular account on a given date. These are the fields:
估值包含特定日期特定账户的持有总和。这些是领域:
id{primary key/auto-inc}, accountid {int}, valuationdate {date}, valuation {double}.
id {primary key / auto-inc},accountid {int},valuationdate {date},valu {double}。
Post Jan 2012, for every valuation in the valuations table I have a corresponding collection of holdings in the holdings table whose summed value = the valuation.
2012年1月后,对于估值表中的每个估值,我在持股表中有相应的持股集合,其总和值=估值。
Prior to this date I only have the valuations, no holdings.
在此之前,我只有估值,没有持股。
For example in valuations I would have a record like so:
例如,在估值中,我会有这样的记录:
id | accountid | valuationdate | valuation
------------------------------------------------
56 | 12345 | 2013-03-31 | 2000000
Correspondingly I would have these holdings (which add up to valuation for this account on this date:
相应地,我会拥有这些资产(这相当于此账户的估值:
id | accountid | holdingdate | holdingid | holdingvalue
---------------------------------------------------------------
250 | 12345 | 2013-03-31 | 16 | 1000000
251 | 12345 | 2013-03-31 | 38 | 500000
252 | 12345 | 2013-03-31 | 27 | 500000
As mentioned above, there are some cases where I only have the record in the valuations table, I have no corresponding holdings.
如上所述,在某些情况下,我只在估价表中有记录,我没有相应的持股。
In order to simplify/optimize my database structure I want to eliminate the valuations table as it is essentially duplicating information that should be present in the holdings table by simply summing the assets for an account on a given date. In order to obtain a client's valuation going forward I can simply sum their holdings for a given date, negating the need for the valuations table completely.
为了简化/优化我的数据库结构,我想要消除估价表,因为它基本上是通过简单地在给定日期汇总帐户的资产来复制应该存在于馆藏表中的信息。为了获得客户的估值,我可以简单地将他们的持股量与给定日期相加,而无需完全取消估值表。
My goal is to populate the holdings table with the data from valuations for dates where it doesn't exist.
我的目标是使用估值数据填充馆藏表中不存在的日期。
Essentially, if no holdings exist for an account number/valuation date combination then insert a dummy holding (holdingid = 999) to the holdings table for that account number/valuation date equal to the valuation on that date.
基本上,如果账号/估价日期组合不存在持股,则将持有(holdid = 999)的虚拟持有(holdid = 999)插入到该账号/估值日期的持股表中,该日期等于该日期的估值。
Is it possible to construct an SQL query to achieve the above?
是否有可能构造一个SQL查询来实现上述目标?
2 个解决方案
#1
3
Either one of these should work:
其中任何一个应该工作:
insert into holdings (accountid, holdingdate, holdingid, holdingvalue)
select v.accountid, v.valuationdate, 999, v.valuation
from valuations v
left join holdings h on h.accountid=v.accountid and h.holdingdate=v.valuationdate
where h.holdingdate is null
EDIT: Corrected the second version to use a correlated WHERE clause.
insert into holdings (accountid, holdingdate, holdingid, holdingvalue)
select v.accountid, v.valuationdate, 999, v.valuation
from valuations v
where v.valuationdate not in (select distinct holdingdate from holdings where accountid=v.accountid)
#2
0
You could use WHERE NOT IN based on the date.
您可以根据日期使用WHERE NOT IN。
INSERT INTO holdings
(accountid, holdingdate, holdingid, holdingvalue)
SELECT accountid, valuationdate, NULL, valuation
FROM valuations
WHERE valuationdate NOT IN (
SELECT holdingdate
FROM holdings
)
I don't know if you still need both an Id column and a holdingid column. You'll have to decide what to do with those.
我不知道你是否还需要Id列和holdingid列。你必须决定如何处理这些。
#1
3
Either one of these should work:
其中任何一个应该工作:
insert into holdings (accountid, holdingdate, holdingid, holdingvalue)
select v.accountid, v.valuationdate, 999, v.valuation
from valuations v
left join holdings h on h.accountid=v.accountid and h.holdingdate=v.valuationdate
where h.holdingdate is null
EDIT: Corrected the second version to use a correlated WHERE clause.
insert into holdings (accountid, holdingdate, holdingid, holdingvalue)
select v.accountid, v.valuationdate, 999, v.valuation
from valuations v
where v.valuationdate not in (select distinct holdingdate from holdings where accountid=v.accountid)
#2
0
You could use WHERE NOT IN based on the date.
您可以根据日期使用WHERE NOT IN。
INSERT INTO holdings
(accountid, holdingdate, holdingid, holdingvalue)
SELECT accountid, valuationdate, NULL, valuation
FROM valuations
WHERE valuationdate NOT IN (
SELECT holdingdate
FROM holdings
)
I don't know if you still need both an Id column and a holdingid column. You'll have to decide what to do with those.
我不知道你是否还需要Id列和holdingid列。你必须决定如何处理这些。