December 5, 2006 at 1:36 pm
I have two tables.
1. Table A
2. Table B
Table A has the following columns
1. PolicyNumber
2. TableCode
3. SeriesCode
4. LossDate
5. EffectiveDate
Table B has the following columns
1. PolicyNumber
2. TableCode
3. SeriesCode
4. WaiverofPremium
I want to create a #temp table which must have the follwing columns
1. PolicyNumber
2. TableCode
3. SeriesCode
4. LossDate
5. EffectiveDate
6. WaiverofPremium
Business rule for this is as follows
a.--- need only those records in the temp temp table if all the three have a match with each other in Table A AND TABLE B(PolicyNumber,TableCode,SeriesCode)
b.---Where LossDate Is Not Null And DateAdd(yyyy, 10, EffectiveDate) < LossDate
December 5, 2006 at 1:54 pm
1) INNER JOIN tables ON " three have a match "
2) WHERE DateAdd(yyyy, 10, EffectiveDate) < LossDate
_____________
Code for TallyGenerator
December 5, 2006 at 2:00 pm
Just as Sergiy said:
INSERT INTO #tmpTable (you need to define this prior to this statement)
SELECT
a.PolicyNumber,
a.TableCode,
a.SeriesCode,
a.LossDate,
a.EffectiveDate,
b.WaiverofPremium
FROM tableA a
INNER JOIN tableB b
ON a.PolicyNumber = b.PolicyNumber AND a.TableCode = b.TableCode AND a.SeriesCode = b.SeriesCode
WHERE DATEADD(yyyy, 10, EffectiveDate) < LossDate
But the real question is, why do you want it in a temp table? What do you plan on doing with the data? Many temp table solutions can be replaced by using derived tables.
December 5, 2006 at 2:01 pm
Can you explain little with the syntax because i did used INNER JOIN for that it, did not work.
December 5, 2006 at 2:05 pm
What code did not work?
How did it fail (error, not returning the expected data)?
December 5, 2006 at 2:08 pm
SELECT
a.PolicyNumber,
a.TableCode,
a.SeriesCode,
a.LossDate,
a.EffectiveDate,
b.WaiverofPremium
FROM tableA a
INNER JOIN tableB b
ON a.PolicyNumber = b.PolicyNumber AND a.TableCode = b.TableCode AND a.SeriesCode = b.SeriesCode
WHERE DATEADD(yyyy, 10, EffectiveDate) < LossDate
i get the following error
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'PolicyNumber'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'TableCode'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'SeriesCode'.
December 5, 2006 at 2:13 pm
December 5, 2006 at 2:13 pm
Please post the real query you are using. This error is not possible with this code on the specified column.
For the where conditions, make sure you are using the identifier of the table (a or b in this case)
December 5, 2006 at 2:20 pm
i am sorry about that it was my fault it works but how will i insert record in the temp table because i want to sum the column "waiverofpremium"
Thanks a lot
December 5, 2006 at 2:25 pm
SELECT
a.PolicyNumber,
a.TableCode,
a.SeriesCode,
a.LossDate,
a.EffectiveDate,
SUM(b.WaiverofPremium) AS SumPremium
FROM tableA a
INNER JOIN tableB b
ON a.PolicyNumber = b.PolicyNumber AND a.TableCode = b.TableCode AND a.SeriesCode = b.SeriesCode
WHERE DATEADD(yyyy, 10, EffectiveDate) < LossDate
GROUP BY a.PolicyNumber,
a.TableCode,
a.SeriesCode,
a.LossDate,
a.EffectiveDate
You can add with rollup if you need the bottom line total... or just use the application to calculate that.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply