With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.
Course Of The Month
8 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
I want to update a column VIA insert trigger
Posted on 2009-05-11
I have a SQL query that creates a table, then creates triggers for the table. The triggers need to update a column in the new record from sums and counts in other tables. I'm pretty new to writing queries, so Im a bit confused. I just cannot seem to get the inserted record values to come up. I've tried using variabbles and using the inserted.values from the inserted table.
I need this working tomorrow so any help is appreciated.....
thanks
I need this working tomorrow so any help is appreciated.....
thanks
/* ---------- CREATE THE TABLE --------------------*/
CREATE TABLE [dwANALYZER].[CalData] (
[F1] [smalldatetime] NULL ,
[MasterAgencyID] [varchar] (20) COLLATE Latin1_General_BIN NULL ,
[ProducerID] [varchar] (5) COLLATE Latin1_General_BIN NULL ,
[Issue_State] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Category] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Line] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Form] [varchar] (4) COLLATE Latin1_General_BIN NULL ,
[Origin] [nvarchar] (10) COLLATE Latin1_General_BIN NULL ,
[Originator] [nvarchar] (80) COLLATE Latin1_General_BIN NULL ,
[Policy_Type] [nvarchar] (50) COLLATE Latin1_General_BIN NULL ,
[Quotes] [int] NULL ,
[QuotePremium] [decimal](38, 2) NULL,
[Issues] [int] NULL ,
[IssuePremium] [decimal](38, 2) NULL,
[Renewals] [int] NULL ,
[RenewalPremium] [decimal](38, 2) NULL,
[Cancels] [int] NULL ,
[CancelPremium] [decimal](38, 2) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*----------------- create triggers here ----------------------*/
CREATE TRIGGER Q_INSERT ON CalData AFTER INSERT
AS
BEGIN
DECLARE @F1 smalldatetime
DECLARE @MasterAgencyID varchar(20)
DECLARE @ProducerID varchar(5)
DECLARE @Issue_State nvarchar(50)
DECLARE @Category nvarchar (50)
DECLARE @Line nvarchar (50)
DECLARE @Form varchar (4)
DECLARE @Origin nvarchar (10)
DECLARE @Originator nvarchar (80)
DECLARE @Policy_Type nvarchar (50)
SELECT
@F1 = [F1],
@MasterAgencyID = [MasterAgencyID],
@ProducerID = [ProducerID],
@Issue_State = [Issue_State],
@Category = [Category],
@Line = [Line],
@Form = [Form],
@Origin = [Origin],
@Originator = [Originator],
@Policy_Type = [Policy_Type] FROM inserted
/* -------ive tried it two ways ------------------------*/
UPDATE CalData
Set [Quotes] = (SELECT COUNT(pqi.POLICY_NUMBER) AS Policies
FROM dbo.tblCalendarDate cal LEFT OUTER JOIN
dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi ON CONVERT(char(10), cal.F1, 102) = CONVERT(CHAR(10), pqi.QuoteDate, 102)
GROUP BY cal.F1, pqi.MasterAgencyID, pqi.ProducerID, pqi.Issue_State, pqi.Category, pqi.Line, pqi.Origin, pqi.Originator, pqi.Policy_Type, pqi.Form
HAVING (cal.F1 = inserted.F1) AND (pqi.MasterAgencyID = inserted.MasterAgencyID) AND (pqi.ProducerID = inserted.ProducerID) AND (pqi.Issue_State = inserted.Issue_State)
AND (pqi.Category = inserted.Category) AND (pqi.Line = inserted.Line) AND (pqi.Form = inserted.Form)
AND (pqi.Origin = inserted.Origin) AND (pqi.Originator = inserted.Originator) AND (pqi.Policy_Type = inserted.Policy_Type))
/* -------------------------and this way as well ---------------*/
UPDATE CalData
Set [QuotePremium] = (SELECT SUM(pqi.IssuedPremium) AS Premium
FROM dbo.tblCalendarDate cal LEFT OUTER JOIN
dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi ON CONVERT(char(10), cal.F1, 102) = CONVERT(CHAR(10), pqi.QuoteDate, 102)
GROUP BY cal.F1, pqi.MasterAgencyID, pqi.ProducerID, pqi.Issue_State, pqi.Category, pqi.Line, pqi.Origin, pqi.Originator, pqi.Policy_Type, pqi.Form
HAVING (cal.F1 = @F1) AND (pqi.MasterAgencyID = @MasterAgencyID) AND (pqi.ProducerID = @ProducerID) AND (pqi.Issue_State = @Issue_State)
AND (pqi.Category = @Category) AND (pqi.Line = @Line) AND (pqi.Form = @Form)
AND (pqi.Origin = @Origin) AND (pqi.Originator = @Originator) AND (pqi.Policy_Type = @Policy_Type))
end
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
8
2
20 Comments
The first update wont work because alias inserted is not known in that context. The second on should work with a fiew adjustment... see below
Also you can test in in Mgmt Studio by executing the select and supplying values for your variables. see further below
Hope this helps
Also you can test in in Mgmt Studio by executing the select and supplying values for your variables. see further below
Hope this helps
UPDATE CalData
Set [QuotePremium] = (SELECT SUM(pqi.IssuedPremium) AS Premium
FROM dbo.tblCalendarDate cal INNER JOIN dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi
ON CONVERT(char(10), cal.F1, 102) = CONVERT(CHAR(10), pqi.QuoteDate, 102)
WHERE (cal.F1 = @F1) AND (pqi.MasterAgencyID = @MasterAgencyID) AND (pqi.ProducerID = @ProducerID)
AND (pqi.Issue_State = @Issue_State) AND (pqi.Category = @Category) AND (pqi.Line = @Line) AND (pqi.Form = @Form)
AND (pqi.Origin = @Origin) AND (pqi.Originator = @Originator) AND (pqi.Policy_Type = @Policy_Type))
--To test
SET @F1 = ?
SET @MasterAgencyID = ?
SET @ProducerID = ?
SET @Issue_State = ?
SET @Category = ?
SET @Line = ?
SET @Form = ?
SET @Origin = ?
SET @Originator = ?
SET @Policy_Type = ?
SELECT SUM(pqi.IssuedPremium) AS Premium
FROM dbo.tblCalendarDate cal INNER JOIN dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi
ON CONVERT(char(10), cal.F1, 102) = CONVERT(CHAR(10), pqi.QuoteDate, 102)
WHERE (cal.F1 = @F1) AND (pqi.MasterAgencyID = @MasterAgencyID) AND (pqi.ProducerID = @ProducerID)
AND (pqi.Issue_State = @Issue_State) AND (pqi.Category = @Category) AND (pqi.Line = @Line) AND (pqi.Form = @Form)
AND (pqi.Origin = @Origin) AND (pqi.Originator = @Originator) AND (pqi.Policy_Type = @Policy_Type)
Dont I need the GROUP BY statement in order for the SUM and COUNT to work? (original code snippet line 63 & 73)
The variables come from the newly inserted row. Therefore the AFTER INSERT should supply them, No?
(original snippet line 44-54)
The variables come from the newly inserted row. Therefore the AFTER INSERT should supply them, No?
(original snippet line 44-54)
Active 3 days ago
Replace <PrimaryKey> with the Primary Key or unique column in CallData:
CREATE TRIGGER Q_INSERT ON CalData
AFTER INSERT
AS
BEGIN
UPDATE c
Set Quotes = d.Policies
FROM CalData c
Inner Join (
Select i.<PrimaryKey>,
COUNT(*) Policies
From dbo.tblCalendarDate cal
Inner Join Inserted i On cal.F1 = i.F1
Inner Join dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi ON cal.F1 = pqi.QuoteDate
And i.MasterAgencyID = pqi.MasterAgencyID
And i.ProducerID = pqi.ProducerID
And i.Issue_State = pqi.Issue_State
And i.Category = pqi.Category
And i.Line = pqi.Line
And i.Form = pqi.Form
And i.Origin = pqi.Origin
And i.Originator = pqi.Originator
AND i.Policy_Type = pqi.Policy_Type
Group By
i.<PrimaryKey>
) d On c.PrimaryKey = d.<PrimaryKey>
END
Active 3 days ago
This might be easier to read:
CREATE TRIGGER Q_INSERT ON CalData
AFTER INSERT
AS
BEGIN
UPDATE c
Set Quotes = d.Policies
FROM CalData c
Inner Join (
Select i.<PrimaryKey>,
COUNT(*) Policies
From dbo.tblCalendarDate cal
Inner Join Inserted i On cal.F1 = i.F1
Inner Join dbo.A_PolicyQuoteAndIssueW
And i.MasterAgencyID = pqi.MasterAgencyID
And i.ProducerID = pqi.ProducerID
And i.Issue_State = pqi.Issue_State
And i.Category = pqi.Category
And i.Line = pqi.Line
And i.Form = pqi.Form
And i.Origin = pqi.Origin
And i.Originator = pqi.Originator
AND i.Policy_Type = pqi.Policy_Type
Group By
i.<PrimaryKey>
) d On c.PrimaryKey = d.<PrimaryKey>
END
CREATE TRIGGER Q_INSERT ON CalData
AFTER INSERT
AS
BEGIN
UPDATE c
Set Quotes = d.Policies
FROM CalData c
Inner Join (
Select i.<PrimaryKey>,
COUNT(*) Policies
From dbo.tblCalendarDate cal
Inner Join Inserted i On cal.F1 = i.F1
Inner Join dbo.A_PolicyQuoteAndIssueW
And i.MasterAgencyID = pqi.MasterAgencyID
And i.ProducerID = pqi.ProducerID
And i.Issue_State = pqi.Issue_State
And i.Category = pqi.Category
And i.Line = pqi.Line
And i.Form = pqi.Form
And i.Origin = pqi.Origin
And i.Originator = pqi.Originator
AND i.Policy_Type = pqi.Policy_Type
Group By
i.<PrimaryKey>
) d On c.PrimaryKey = d.<PrimaryKey>
END
I see that you have used <PrimaryKey> .
Is this a variable or are you telling me to enter some data here?
There is no primary key for this table as many of the matching fields will contain nulls.
Is this a variable or are you telling me to enter some data here?
There is no primary key for this table as many of the matching fields will contain nulls.
You don't need a group by because you're only returing the one value SUM(pqi.IssuedPremium). The where clause will define which row make up the sum.
Yes, since the trigger is Insert After you will get the values when you do the select.
Yes, since the trigger is Insert After you will get the values when you do the select.
Active 3 days ago
>>Is this a variable or are you telling me to enter some data here? <<
Did you not see my comment:
"Replace <PrimaryKey> with the Primary Key or unique column in CallData:"
>>There is no primary key for this table as many of the matching fields will contain nulls.<<
That is too bad. Can you find some combination that is unique or failiing that add a unique column (an IDENTITY column for example)
Here is the problem with your approach:
Let's suppose that your INSERT statement adds 100 rows, then the following local variables will contain just one value (the last one in the Inserted logical table):
SELECT
@F1 = [F1],
@MasterAgencyID = [MasterAgencyID],
@ProducerID = [ProducerID],
@Issue_State = [Issue_State],
@Category = [Category],
@Line = [Line],
@Form = [Form],
@Origin = [Origin],
@Originator = [Originator],
@Policy_Type = [Policy_Type] FROM inserted
The Trigger fires once per statement and NOT once per row inserted.
Did you not see my comment:
"Replace <PrimaryKey> with the Primary Key or unique column in CallData:"
>>There is no primary key for this table as many of the matching fields will contain nulls.<<
That is too bad. Can you find some combination that is unique or failiing that add a unique column (an IDENTITY column for example)
Here is the problem with your approach:
Let's suppose that your INSERT statement adds 100 rows, then the following local variables will contain just one value (the last one in the Inserted logical table):
SELECT
@F1 = [F1],
@MasterAgencyID = [MasterAgencyID],
@ProducerID = [ProducerID],
@Issue_State = [Issue_State],
@Category = [Category],
@Line = [Line],
@Form = [Form],
@Origin = [Origin],
@Originator = [Originator],
@Policy_Type = [Policy_Type] FROM inserted
The Trigger fires once per statement and NOT once per row inserted.
Active 3 days ago
HBHoffman,
>>You don't need a group by because you're only returing the one value SUM(pqi.IssuedPremium).<<
How do you know? See my previous comments
>>Yes, since the trigger is Insert After you will get the values when you do the select.<<
That is only true if a single row is Inserted. Again, read up on how Triggers really work.
>>You don't need a group by because you're only returing the one value SUM(pqi.IssuedPremium).<<
How do you know? See my previous comments
>>Yes, since the trigger is Insert After you will get the values when you do the select.<<
That is only true if a single row is Inserted. Again, read up on how Triggers really work.
Ok, so maybe I'm going about this wrong.
I have the table created above that have fields from [F1] to [PolicyType] making the unique key. However they can contain Nulls. Each of the following fields are a COUNT or a SUM based on date JOINS to the tblCalendarDate table. I DO however have views for each of these calculations (Each view contains a COUNT and SUM, 4 views: Quotes, Issues, Cancels and Renewals) .
So maybe instead of a trigger, could I create the table and use a select statement for each of the fields that need calculations?
Here is the view SQL for quotes
I have the table created above that have fields from [F1] to [PolicyType] making the unique key. However they can contain Nulls. Each of the following fields are a COUNT or a SUM based on date JOINS to the tblCalendarDate table. I DO however have views for each of these calculations (Each view contains a COUNT and SUM, 4 views: Quotes, Issues, Cancels and Renewals) .
So maybe instead of a trigger, could I create the table and use a select statement for each of the fields that need calculations?
Here is the view SQL for quotes
SELECT TOP 100 PERCENT cal.F1, pqi.MasterAgencyID, pqi.ProducerID, pqi.Issue_State, pqi.Category, pqi.Line, pqi.Form, pqi.Origin, pqi.Originator,
pqi.Policy_Type, COUNT(pqi.POLICY_NUMBER) AS Policies, SUM(pqi.IssuedPremium) AS Premium
FROM dbo.tblCalendarDate cal LEFT OUTER JOIN
dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi ON CONVERT(char(10), cal.F1, 102) = CONVERT(CHAR(10), pqi.QuoteDate, 102)
GROUP BY cal.F1, pqi.MasterAgencyID, pqi.ProducerID, pqi.Issue_State, pqi.Category, pqi.Line, pqi.Origin, pqi.Originator, pqi.Policy_Type, pqi.Form
HAVING (cal.F1 < GETDATE() - 1)
ORDER BY cal.F1 DESC, pqi.MasterAgencyID, pqi.ProducerID
BTW
Here is the rest of the query. This is where it inserts the records
Here is the rest of the query. This is where it inserts the records
/*-----------------------------------------------------------------------------*/
INSERT INTO CalData
(F1,
MasterAgencyID ,
ProducerID,
Issue_State,
Category,
Line,
Form,
Origin,
Originator ,
Policy_Type
)
SELECT
cal.F1,
pqi.MasterAgencyID,
pqi.ProducerID,
pqi.Issue_State,
pqi.Category,
pqi.Line,
pqi.Form,
pqi.Origin,
pqi.Originator,
pqi.Policy_Type
FROM
dbo.tblCalendarDate cal LEFT OUTER JOIN
dbo.A_PolicyQuoteAndIssueWithInspectionsPlusW1 pqi
ON CONVERT(char(10), cal.F1, 102) = CONVERT(CHAR(10), pqi.QuoteDate, 102)
GROUP BY cal.F1, pqi.MasterAgencyID, pqi.ProducerID, pqi.Issue_State, pqi.Category, pqi.Line, pqi.Origin, pqi.Originator, pqi.Policy_Type, pqi.Form
HAVING (cal.F1 > GETDATE() - 90)
Active 3 days ago
>>Ok, so maybe I'm going about this wrong.<<
No necessarily. Did you try my code?
>>I have the table created above that have fields from [F1] to [PolicyType] making the unique key. However they can contain Nulls. <<
It would certainly help if you had a primary key. But the code I originally posted should also work.
No necessarily. Did you try my code?
>>I have the table created above that have fields from [F1] to [PolicyType] making the unique key. However they can contain Nulls. <<
It would certainly help if you had a primary key. But the code I originally posted should also work.
Is there a way to create the table and insert/replace Spaces instead of NULL. Wont that allow me to create a PRIMARY key?
And I did try your solution but without a primary key, It wont work.
And I did try your solution but without a primary key, It wont work.
Active 3 days ago
>>And I did try your solution but without a primary key, It wont work.<<
How do you know? I did not say you had to ahve one, I said it would help.
How do you know? I did not say you had to ahve one, I said it would help.
Onlines 13, 28 and 29 you reference <PrimaryKey> should I just reference the Columns that are unique?
Since there was no Primary key I didnt replace them with anything.
Can you post how you would write it without the primary key reference?
Since there was no Primary key I didnt replace them with anything.
Can you post how you would write it without the primary key reference?
One more thing, Is this going to work on all the records?
You said a trigger only works when a single row is inserted. Does a "Select into" qualify as multiple inserts?
I'm back at the office this morning (and overdue obviously). I'll try all of your suggestions and update you.
Thanks for all the time with this.
You said a trigger only works when a single row is inserted. Does a "Select into" qualify as multiple inserts?
I'm back at the office this morning (and overdue obviously). I'll try all of your suggestions and update you.
Thanks for all the time with this.
Active 3 days ago
>>Can you post how you would write it without the primary key reference?<<
CREATE TRIGGER Q_INSERT ON CalData
AFTER INSERT
AS
BEGIN
UPDATE c
Set Quotes = d.Policies
FROM CalData c
Inner Join (
Select i.MasterAgencyID,
i.ProducerID,
i.Issue_State,
i.Category,
i.Line,
i.Form,
i.Origin,
i.Originator,
i.Policy_Type,
COUNT(*) Policies
From dbo.tblCalendarDate cal
Inner Join Inserted i On cal.F1 = i.F1
Inner Join dbo.A_PolicyQuoteAndIssueW
And ISNULL(i.MasterAgencyID, '') = ISNULL(pqi.MasterAgencyID,
And ISNULL(i.ProducerID, '') = ISNULL(pqi.ProducerID, '')
And ISNULL(i.Issue_State, '') = ISNULL(pqi.Issue_State, '')
And ISNULL(i.Category, '') = ISNULL(pqi.Category, '')
And ISNULL(i.Line, '') = ISNULL(pqi.Line, '')
And ISNULL(i.Form, '') = ISNULL(pqi.Form, '')
And ISNULL(i.Origin, '') = ISNULL(pqi.Origin, '')
And ISNULL(i.Originator, '') = ISNULL(pqi.Originator, '')
AND ISNULL(i.Policy_Type, '') = ISNULL(pqi.Policy_Type, '')
Group By
i.MasterAgencyID,
i.ProducerID,
i.Issue_State,
i.Category,
i.Line,
i.Form,
i.Origin,
i.Originator,
i.Policy_Type
) d On ISNULL(i.MasterAgencyID, '') = ISNULL(d.MasterAgencyID, '')
And ISNULL(i.ProducerID, '') = ISNULL(d.ProducerID, '')
And ISNULL(i.Issue_State, '') = ISNULL(d.Issue_State, '')
And ISNULL(i.Category, '') = ISNULL(d.Category, '')
And ISNULL(i.Line, '') = ISNULL(d.Line, '')
And ISNULL(i.Form, '') = ISNULL(d.Form, '')
And ISNULL(i.Origin, '') = ISNULL(d.Origin, '')
And ISNULL(i.Originator, '') = ISNULL(d.Originator, '')
AND ISNULL(i.Policy_Type, '') = ISNULL(d.Policy_Type, '')
END
>>Is this going to work on all the records?<<
That was the point I was trying to make previously (#24365600) and why you have to join using the logical table Inserted as opposed to assiging the values to local variables.
But whether it ultimately "works" I have no clue. To be totally honest, I cannot follow entirely what you are trying to achieve.
CREATE TRIGGER Q_INSERT ON CalData
AFTER INSERT
AS
BEGIN
UPDATE c
Set Quotes = d.Policies
FROM CalData c
Inner Join (
Select i.MasterAgencyID,
i.ProducerID,
i.Issue_State,
i.Category,
i.Line,
i.Form,
i.Origin,
i.Originator,
i.Policy_Type,
COUNT(*) Policies
From dbo.tblCalendarDate cal
Inner Join Inserted i On cal.F1 = i.F1
Inner Join dbo.A_PolicyQuoteAndIssueW
And ISNULL(i.MasterAgencyID, '') = ISNULL(pqi.MasterAgencyID,
And ISNULL(i.ProducerID, '') = ISNULL(pqi.ProducerID, '')
And ISNULL(i.Issue_State, '') = ISNULL(pqi.Issue_State, '')
And ISNULL(i.Category, '') = ISNULL(pqi.Category, '')
And ISNULL(i.Line, '') = ISNULL(pqi.Line, '')
And ISNULL(i.Form, '') = ISNULL(pqi.Form, '')
And ISNULL(i.Origin, '') = ISNULL(pqi.Origin, '')
And ISNULL(i.Originator, '') = ISNULL(pqi.Originator, '')
AND ISNULL(i.Policy_Type, '') = ISNULL(pqi.Policy_Type, '')
Group By
i.MasterAgencyID,
i.ProducerID,
i.Issue_State,
i.Category,
i.Line,
i.Form,
i.Origin,
i.Originator,
i.Policy_Type
) d On ISNULL(i.MasterAgencyID, '') = ISNULL(d.MasterAgencyID, '')
And ISNULL(i.ProducerID, '') = ISNULL(d.ProducerID, '')
And ISNULL(i.Issue_State, '') = ISNULL(d.Issue_State, '')
And ISNULL(i.Category, '') = ISNULL(d.Category, '')
And ISNULL(i.Line, '') = ISNULL(d.Line, '')
And ISNULL(i.Form, '') = ISNULL(d.Form, '')
And ISNULL(i.Origin, '') = ISNULL(d.Origin, '')
And ISNULL(i.Originator, '') = ISNULL(d.Originator, '')
AND ISNULL(i.Policy_Type, '') = ISNULL(d.Policy_Type, '')
END
>>Is this going to work on all the records?<<
That was the point I was trying to make previously (#24365600) and why you have to join using the logical table Inserted as opposed to assiging the values to local variables.
But whether it ultimately "works" I have no clue. To be totally honest, I cannot follow entirely what you are trying to achieve.
OK So I modified the Code to use the unique columns instead of a primary key. it did not work. Attached is zip file of SQL. (2a)
A Second option is that I have views that have already summed the counts and sums that only require a single row be selected to update the inserted record. I tried to replicate the structure you've used but it hangs for a very long time. See Zip (2b)
The view is created by the code I posted 6-7 messages back.
2a-create-caldata-table.zip
2b-create-caldata-table.zip
A Second option is that I have views that have already summed the counts and sums that only require a single row be selected to update the inserted record. I tried to replicate the structure you've used but it hangs for a very long time. See Zip (2b)
The view is created by the code I posted 6-7 messages back.
2a-create-caldata-table.zip
2b-create-caldata-table.zip
OK here is the objective:
I have two tables
1) A table of calendar dates (cal), containing all the dates from 1/1/2006 to 12/31/2012
2) a table (PQI) that contains the following:
MasterAgencyID, ProducerID, Issue_State, Category, Line, Form, Origin, Originator, Policy_Type, QuoteDate, IssueDate, EffDate, CancelDate, Premium
I need to create a single table that groups the first 9 fields uniquelly, Counts the records and Sums the premium, for each of the Dates by way of the JOIN to the Calendar table. (therfore 4 updates) Naturally the dates will all be different therefore the Counts and Sums will be different.
Thast why I'm only trying to get the first one to work, figureing I can replicate the code to use the other dates once the first works.
I hope this helps
Thanks again.
I've tried the last code you sent and it cannot find the reference "i"
I have two tables
1) A table of calendar dates (cal), containing all the dates from 1/1/2006 to 12/31/2012
2) a table (PQI) that contains the following:
MasterAgencyID, ProducerID, Issue_State, Category, Line, Form, Origin, Originator, Policy_Type, QuoteDate, IssueDate, EffDate, CancelDate, Premium
I need to create a single table that groups the first 9 fields uniquelly, Counts the records and Sums the premium, for each of the Dates by way of the JOIN to the Calendar table. (therfore 4 updates) Naturally the dates will all be different therefore the Counts and Sums will be different.
Thast why I'm only trying to get the first one to work, figureing I can replicate the code to use the other dates once the first works.
I hope this helps
Thanks again.
I've tried the last code you sent and it cannot find the reference "i"
Active 3 days ago
>>it did not work.<<
Unfortunately that tell me nothing.
I suggest you ask for a refund and re-post. Hopefully someone will step up to the plate.
Good luck.
Unfortunately that tell me nothing.
I suggest you ask for a refund and re-post. Hopefully someone will step up to the plate.
Good luck.
It's hard to explain a problem sometimes, so I can see how it's even harder to explain a solution to a problem that hasnt been communicated completely. Even though it wasn't the answer I was looking for, you were thorough and helpful. It helped me understand how triggers work a bit better and, most of all, it helped me realize I was going about it wrong and made me look into other options. I actually got it done with cursors and updates/inserts. So you deserve the points for all your help. Thank you very much.
Featured Post
Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
Suggested Courses
Course of the Month8 days, 4 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.