Complex SQL or am I missing something?

[J Welsby]

I have two tables. One that contains a number of sequential years
(tblMasterYears) and one that contains some simulated data (tblSimData).

Tables with field names and example data as follows:

tblMasterYears:
Year
1
2
3
4
5
6
7
8
9
10

tblSimData:
Year Premium Loss Result
2 100 25 75
7 100 125 -25
9 100 200 -100

This database is an insurance model that simulates loss data. Depending on
expected frequency (ie the number of losses) and expected severity (ie the
size of each loss) the tblSimData will have a varying number of simulated
loss records produced. In practice, the database is run with 500k years of
simulation such that tblMasterYears will have 500k year records and
tblSimData will have approximately 75k loss records.

What I would like is to generate a SQL string that can provide a user form
with data for all years. Example from data above:
Year Premium Loss Result
1 100 0 100
2 100 25 75
3 100 0 100
4 100 0 100
5 100 0 100
6 100 125 -25
7 100 0 100
8 100 0 100
9 100 200 -100
10 100 0 100

When there are no loss records in tblSimData (ie years 1, 3-5, 7-8 and 10)
the simulated result is always the same (ie same premium of 100, loss of 0
and result of 100).

Currently, the database runs and the tblSimData contains all 500k records
(they are all created in SQL) which is obviously very redundant and
inefficient.

I have created a simple standalone database with a subquery to UNION two
query strings together but it completely chokes on only about 50K simulation
years. In doing so, I created a record in the tblSimData table as follows:
Year Premium Loss Result
0 100 0 100
which is used to provide the data for any years which are 'loss free' (ie
loss amount is 0 and is thus not in the tblSimData).

Anyone have any ideas on how this can be achieved? Thanks in advance.
James Welsby

 

[George Nicholson]

Anyone have any ideas on how this can be achieved?
A little unclear as to what your exact question is. If this doesn't address
it, please clarify

tblMasterYears: 10 records
tblSimData: 3 records
result set: 10 records with 100/0/100 for Premium/Loss/Result in "missing"
years.

SELECT tblMasterYears.Years, nz([tblSimData].[Premium],100) AS Premium,
nz([tblSimData].[Loss],0) AS Loss,
nz([tblSimData].[Premium],100)-nz([tblSimData].[Loss],0) AS Result
FROM tblMasterYears LEFT JOIN tblSimData ON tblMasterYears.Years =
tblSimData.Years;

Calculated values are generally not stored. I have taken the liberty of
pretending Result is not stored in a table and only calculated on-the-fly
(as above). This may not be desirable in your case, so feel free to ignore.

I also avoided using the reserved word Year as a Field name.

 

[J Welsby]

George, thanks for the reply and the ideas. Point taken on reserved words
and storing 'calculatable results' in tables.

I thought in my original thread I was clear on what I was trying to acheive
but will repeat here for clarity:etc.

In the 'missing years', I cannot rely on the NZ function and the hardcoding
of the values for the premium (100) and loss (0) values as these values will
change depending on the insurance contract being simulated in the db.

I had proposed to create a dummy record with Year of 0 which would hold
missing values for those missing years. Is this not a good idea in your
opinion?

Again, many thanks for your thoughts.
James

Anyone have any ideas on how this can be achieved?

A little unclear as to what your exact question is. If this doesn't address
it, please clarify

tblMasterYears: 10 records
tblSimData: 3 records
result set: 10 records with 100/0/100 for Premium/Loss/Result in "missing"
years.

SELECT tblMasterYears.Years, nz([tblSimData].[Premium],100) AS Premium,
nz([tblSimData].[Loss],0) AS Loss,
nz([tblSimData].[Premium],100)-nz([tblSimData].[Loss],0) AS Result
FROM tblMasterYears LEFT JOIN tblSimData ON tblMasterYears.Years =
tblSimData.Years;

Calculated values are generally not stored. I have taken the liberty of
pretending Result is not stored in a table and only calculated on-the-fly
(as above). This may not be desirable in your case, so feel free to ignore.

I also avoided using the reserved word Year as a Field name.

--
HTH,
George

I have two tables. One that contains a number of sequential years
(tblMasterYears) and one that contains some simulated data (tblSimData).

Tables with field names and example data as follows:

tblMasterYears:
Year
1
2
3
4
5
6
7
8
9
10

tblSimData:
Year Premium Loss Result
2 100 25 75
7 100 125 -25
9 100 200 -100

This database is an insurance model that simulates loss data. Depending
on
expected frequency (ie the number of losses) and expected severity (ie the
size of each loss) the tblSimData will have a varying number of simulated
loss records produced. In practice, the database is run with 500k years
of
simulation such that tblMasterYears will have 500k year records and
tblSimData will have approximately 75k loss records.

What I would like is to generate a SQL string that can provide a user form
with data for all years. Example from data above:
Year Premium Loss Result
1 100 0 100
2 100 25 75
3 100 0 100
4 100 0 100
5 100 0 100
6 100 125 -25
7 100 0 100
8 100 0 100
9 100 200 -100
10 100 0 100

When there are no loss records in tblSimData (ie years 1, 3-5, 7-8 and 10)
the simulated result is always the same (ie same premium of 100, loss of 0
and result of 100).

Currently, the database runs and the tblSimData contains all 500k records
(they are all created in SQL) which is obviously very redundant and
inefficient.

I have created a simple standalone database with a subquery to UNION two
query strings together but it completely chokes on only about 50K
simulation
years. In doing so, I created a record in the tblSimData table as
follows:
Year Premium Loss Result
0 100 0 100
which is used to provide the data for any years which are 'loss free' (ie
loss amount is 0 and is thus not in the tblSimData).

Anyone have any ideas on how this can be achieved? Thanks in advance.
James Welsby