Locks And Forever Runnable Processes

Jul 20, 2005

Hello all,

I'm using SS 2000 and NT4 (and Access97 as front-end on another server)

Well, probably by lack of knowledge about table locks, I don't really know
where to start to present this problem. In Enterprise manager, section
"Management->Current activity->Locks/Objects", we have a couple (5-7) of
"forever runnable" processes, all related to two specific situations. Each
of them are for "SELECT" statements. It's been a long time since it's like
that. I've always been curious about them but the weren't causing any
problem. Now, after a modification, a third situation happened ("SELECT"
again)... and sometime a lock created by this new "forever runnable" process
blocks other functions that use the same table. All my table are linked with
an ODBC link.

Any help or suggestion where to search would be appreciated.

Thanks.

Yannick

View 1 Replies


ADVERTISEMENT

Excessive Blocking - Runnable Processes

May 23, 2006

Hi there,

Currently using SQL Server 2000 (SP4). The following condition started occurring last week:

- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking

Any help would be greatly appreciated.

Thanks.

View 7 Replies View Related

SQL Server 2008 :: Row Locks Not Escalating To Table Locks After 5000

Jul 16, 2015

I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:

insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15

[code]...

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:

SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC

The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.

A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.

View 9 Replies View Related

Whose Runnable

Apr 13, 1999

In a development database, a developer was running 11 simultaneous jobs (in
PB) doing inserts into 11 tables. Ten completed and he wanted to know which
one was still active. We suspected it was on the largest table with the
most inserts was the one still active.

We're running on MSSQL 6.5, sp4.

We looked at current activity -user activity and saw that there were two
active users. Myself and someone else but not the developer. Although one
hint was a small blue line in the icon of a red page surrounded by a red
circle and a red line going through it, I guess that was the indication of
which table.

When we logged onto ISQLW and ran sp_who on are separate machines as sa, we
saw different statuses on the same process.

I saw the process 31 as sleeping and the developer on his machine(also
logged on as sa) saw the process 31 as runnable?

My question is this correct? How do I tell if HIS process is running if I
can't see it on my machine when we are both logged on as SA?

Any help will be greatly appreciated. Thanks.

David Spaisman

View 2 Replies View Related

Transact SQL :: How To List All Locks (including NON-BLOCKING Locks)

Aug 5, 2015

We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system.  The query also included the Process ID of the process we needed to kill in order to get rid of the lock.

We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.

View 7 Replies View Related

Sp_who2 Status RUNNABLE

Jun 10, 2004

In the sql server analyzer, i ran sp_who2 store procedure to find out the procesess and locks on the sql server. I noticed on the status column some entries "RUNNABLE", does this could trigger blocked to other processess if not closed by our application? or What does runnable status mean?

View 1 Replies View Related

Runnable Master Awaiting Command

Nov 1, 2006

Hi,Database Server running slow. I used SP_WHO2 ACTIVE and the resulthas many ofXXXX SA RUNNABLE MASTER AWAITINGCOMMAND..........(XXXX is spid)and they stay for couples seconds.Last week , the server was running fine and during weekend ,noneof errors record in Windowsevent log or SQL Server log.Server is Windows 2003 server with 4 CPUs of xeon , 4 GB of memoryandMSSQL SERVER 200 SP4.I found out that every runnable master db tasks has shared lock onkey of'master.dbo.sysxlogins.sysxlogins' objectIs this the problem of slow running database server?Please advice.Peeud

View 1 Replies View Related

Processes Were Blocked By Other Processes

May 31, 2001

We are facing lot of problems with Blocking,can any one help us in this matter,The problem is as follows

We have SQL Server 7.0 running on Nt4.0, and three web servers and 5 application servers are accessing SQL server.
Till Yesterday everything was fine,Suddenly today more than 18 processes
were blocked by other(Like chain),First i killed some blocking process,then it was fine,once again it started and continuously some processes are blocked by other,and i found that all blocking process are running from webservers.I ran SQL Profiler to get some information,but no use.
I am not understanding why suddenly it happend,because we have't modified anything.Is there any way to overcome this situation,this is production server.
because of this users are getting slow responce/no responce.

----Here i want to know why it happend?
---How to trace the problem and fix it

Can any one help me please
Thanks
Ananth

View 1 Replies View Related

Can Profiler Run Forever?

Jan 9, 2008

I created a profiler to run on a remote server in local. Then I logout. After two hours, I login again. The profiler was closed. I don't know when and why. Did someone have same problem? Is this normal?

Thanks

ZYT

View 8 Replies View Related

SQL Query Taking Forever

Mar 1, 2006

I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT     dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage,                       dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM         dbo.tblErrorLevel INNER JOIN                      dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN                      dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.

View 2 Replies View Related

Sysprocesses Says: Wait Forever

Sep 30, 2004

I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.

Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

What should I do?

View 6 Replies View Related

SQL Server Job Running Forever

Dec 9, 2004

I have a job that is running for 2 days straight and the status reads: Performing Completion action.

I have tried to disable/Stop the job, but can't.

I have also tried to start the job which it won't because it is still running.

Is there a table that I can manual delete the schedule # or something along those lines to start all over again.

Thanks

Oh the normal time for the job to run is only 7 seconds.

Lystra

View 2 Replies View Related

Shrinkdatabase Taking Forever...

Mar 17, 2008

Hi all,

2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.

So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
dbcc shrinkdatabase('DB', truncateonly)
just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
dbcc shrinkdatabase('DB', 0)
has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
dbcc shrinkdatabase('DB', truncateonly)
again... still running...

The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??

Anyone has experience running shrink on large DBs?


thanks!

View 14 Replies View Related

Cte Query Taking Forever

May 14, 2008

I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..

; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update



thanks

View 8 Replies View Related

Help A Newbie And Live Forever... Maybe

Feb 22, 2006

Hi, sorry if this has been asked before but I'm pretty strapped for time...

I have two tables: [photos] and [photoFolders]

[photoFolders] contains information about photo albums on the site im creating. The information in [photos] lists information about all photos along with which [photoFolder] they belong. When the user logs in, I want to present a list of all 'folders' in their name along with the TOP image with the corresponding folderId...

[photos]
photoId
photoName
folderId
photoDescription
cUserId

[photoFolders]
folderId
folderName
eventDate
cUserId

Any help would be GREATLY appreciated

We're all going to hell... I guess I'll see you there!

View 15 Replies View Related

All Queries Are Taking Forever

Aug 23, 2007

what can I do?

all queries that used to work are taking forever now???

what can I do?

is there a max size for the db that I may have reached

please advise asap

View 20 Replies View Related

Why Would Adding Dbo. Cause My Function To Run Forever?

Jan 13, 2006

Lately I did a mass update on all our scripts and addeddbo. in front of all tables and other objects.There is a function that returns a table and the function isI think 300 lines. There are a lot of UNIONs, EXISTS,NOT EXISTS, etc...Anyhow there's a single place where if i add dbo. in frontof the table this function which is being called from an SP,when i run the SP from the QA or from the ASP applicationit runs forever. As soon as i remove the dbo. again it runsin 6-8 seconds. Here's the thing this same table reference existsa few lines below in the 2nd UNION for example andhaving a dbo. in front doesn't cause any problems.Even more confusing if i add Databasename.dbo.TableNameand run it again, i don't run into any problem.So it's almost like if i specify dbo. in front of the table, somehowSQL Server or our code is getting lost and searching for thistable in other databases?Has anyone run or seen such a problem?I am sure I can make changes to the code and end upwriting a different code but before I make changes I wouldlike to find out more about my mystery problem.I am running SQL 2000 SP4 and the same problem occuredon two different machines. Win 2000 Pro and Win 2000 Server.Any ideas, suggestions?Thank you

View 4 Replies View Related

Report Taking Forever To Come Up

Dec 14, 2007

I am using RS 2005 and SQL Server 2005. I am having a table with about 6 million rows. I am extracting about 2 milliion rows for a report. When i run the report as a single user the report comes up in 6-7 minutes but when i run the report with 2 users the report takes forever to come up.

The statistics are different each time sometimes 19 minutes sometimes 30 minutes. The report connects to the db with the same dbuser id for both the people running the report. The stored procedure being invoked uses temp tables and also indexes are created on the fly for these temp tables.

The moment 2 people are running the report and when i run an SP_WHO2 i see that one process id that is being started by reportserver blocks another process being run by reportserver.

Timeouts are not happening the report justs goes on forever to come up. Any help? Also if you need any more information please do let me know I will be glad to give them.

The report is a matrix report and there are 4 levels of grouping on the report.

Thanks in advance

View 1 Replies View Related

Take Forever To Take Database Offline

Mar 5, 2007

Hi,

I try to restore a database but it pop error said "the database is in use". So, I try to take the database offline so that I can store the database. But it takes me forever (1 hour till now). It is still showing "1 remaining". Do you have any idea why ? Thank you very much in advance!

View 4 Replies View Related

Keep Package Alive Forever

Oct 16, 2006

Hi there,

Does anyone know how i can keep an ssis package used for real time reporting alive no matter the amount of errors it gets? So for instance the server im streaming to is shutdown for maintenance, and the connection dies, its needs to just keep re-trying. In other words the maximum error count is infinite. i dont just want to set max err count high, i want it out of the picture all together.



Thanks

View 7 Replies View Related

Simple (?) SQL 7.0 Update Taking Forever...

May 3, 2000

I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...

View 3 Replies View Related

SQL Server Loads Over Network Take Forever

Feb 16, 1999

When I attempt to load a database from dump format across a network (100mb Ethernet) It takes forever. (15 hours for 16GB!) can anyone help me find a starting point to troubleshoot this?

Thanks!

-Chris

P.S. File Copies of the same size move at a rapid fashion, and I cannot find any bottlenecks in the network.

View 2 Replies View Related

Update Stmt Takes Forever

Nov 26, 1998

We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.

We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'

this returns immediately.

Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.

There is no index on UDF3 and WOID is the primary key.

Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.

name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB

View 1 Replies View Related

Stored Procedure Taking Forever To Run

Apr 9, 2008

Hi there i have a sql server 7 database that i copied across to another server this time running windows 2003 and sql server 8. I have a routine that runs every night on each machine. on the old machine it take about 2 hours to do. on this new machine it is taking up to 5.5 hours to do the exact same job. the results are the same but the time delay could become an issue later on so i would like to nip it in the bud now.

Does anyone ahe any suggestions as to why the code would run so much slower on a newer and better spec machine.

I have copied everything across so there is no difference in tables or stored procedures. is there an optimisation tool i can run ??

has anyone got any ideas ?

View 6 Replies View Related

NOLOCK Query Waits Forever

Apr 22, 2008

Back in the days of SQL 7.0 I used a lot of ODBC SELECT querying form VB applications, in which I implemented NOLOCK in order to prevent the primary business applications from being locked out of tables once the queries were run.

Now, quite a few years later, I'm busying myself converting a lot of old Access based forms and queries to TSQL on SQL-Server 2000, and wonder aimlessly why NOLOCK queries (simple select ones) are imensely slower than a standars select clause.


SELECT * FROM employees


This would be much much faster than the code below, but users would get "The current record could not be accessed, as it is being used by another user", evidently because I'm locking the record while producing the output.


SELECT * FROM employees (nolock)


So this could should - as I remember it - do a dirty read on table, not obstructing other users and give me a snapshot of date as they are, although they might be locked for edit.

Could anyone explain to me why the nOLOCK query fials to give me any output? It is as if the nolock request is waiting for the table/records to free? In which case I'll never be able to run a query.

Cheers in advance, Trin

View 14 Replies View Related

Report Taking Forever To Render

Jan 15, 2008



Hi All,

I have 2 reports that report on baiscally the same thing, just group differently.

Report 1 groups summary phone call stats by Department, Day, and Hour - which are all drop down options.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see daily stats ... the daily stats can then be expanded to see the hourly stats.

Kinda Like:
-------------------------
-Department 1
-10/1/2007
12:00 AM
1:00 AM
+10/2/2007

+Department 2
-------------------------

Report 2 shows the same summary stats by department and extension - which is also a drop down option.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see summary stats for each extension.

Kinda Like:
-------------------------
-Department 1
Ext 2005
Ext 2008

+Department 2
-------------------------


The queries for these reports run from the Management Studio in about 10 seconds each with the Report 1 query returning about 800 rows for the month of October 2007 and the Report 2 query returning about 30 rows for the same date range.

When the reports are rendered, Report 1 (with 800 rows) is rendered in about 20 seconds, while Report 2 (with only 30 rows) takes about 5 minutes to render.

The reports themselves are very similar, with the only difference being the grouping. It is weird that the report that returns the samller Dataset is actually taking longer to render.

One thing I did try was running the queries from the Data tab of the .rdl files (in visual studio) and the query for Report 2 took about 4 minutes to return data, while (as I mentioned above) it ran in about 10 seconds in Management Studio.

Has anyone else run into this?
Any suggestions?


Thanks in advance!
-Matt

View 1 Replies View Related

What Is Wrong With This Query..It's Taking Forever...

May 16, 2008



USE GLPDEMO
GO

select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'


DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)

DECLARE TCursor CURSOR for

SELECT TriggerName, TableName from Temp_TablesAndTriggers

OPEN TCursor

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

WHILE @@FETCH_STATUS = 0


select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable


EXECUTE sp_executesql @exestr;

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

CLOSE TCursor
DEALLOCATE TCursor

--DROP TABLE #Temp_TablesAndTriggers

View 4 Replies View Related

Report Running Continuously (forever)

Apr 9, 2007

I have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.



It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).



Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?



Here is the very long procedure:



CREATE PROCEDURE [dbo].[ISP_RPT_RENEWAL_RETENTION_TEST]

(

@FROM DATETIME,

@TO DATETIME,

@ASOF DATETIME,

@REPORT VARCHAR(10) = 'ADHOC')

AS

SET NOCOUNT ON

--DECLARE @FROM DATETIME

--DECLARE @TO DATETIME

--DECLARE @ASOF DATETIME

--DECLARE @REPORT VARCHAR(10)

--

--SET @REPORT = 'YTD'

--SET @FROM = '1/1/2006'

--SET @TO = '10/31/2006'

--SET @ASOF = '10/31/2006'

IF (@REPORT = 'ADHOC' OR @REPORT IS NULL OR @REPORT = '')

BEGIN

--Check to see if user has input data

--First of the previous month

IF (@FROM IS NULL OR @FROM = '') SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1

IF (@TO IS NULL OR @TO = '') SET @TO = dbo.PreviousMonthEndDate(GetDate())

IF (@ASOF IS NULL OR @ASOF = '') SET @ASOF = dbo.PreviousMonthEndDate(GetDate())

END

ELSE IF @REPORT = 'MTD'

BEGIN

--Disregard user input and run for MTD

--First of the previous month

SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1

SET @TO = dbo.PreviousMonthEndDate(GetDate())

SET @ASOF = dbo.PreviousMonthEndDate(GetDate())

END

ELSE IF @REPORT = 'YTD'

BEGIN

--Disregard user input and run for YTD

SET @FROM = dbo.FirstOfTheYear(GetDate())

SET @TO = dbo.PreviousMonthEndDate(GetDate())

SET @ASOF = dbo.PreviousMonthEndDate(GetDate())

END

SET @FROM = dbo.MidnightDate(@FROM)

SET @TO = dbo.MidnightDate(@TO)

SET @ASOF = dbo.MidnightDate(@ASOF)

/*** if user runs report before month end is closed, then use data from premium table

if however user runs report after month end close, then pull from reinsurance table ***/

DECLARE @CHECKDATE DATETIME

SELECT @CHECKDATE = MAX(MONTHENDDATE) FROM MPATREINSURANCE

WHERE MONTHENDDATE = @TO

/*********************************************** IDENTIFY ALL RENEWALS ***************************************************/

SELECT DISTINCT

RC.POLICYNO POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,

MCITCUST.FULLNAME ,MAX(ISNULL(RC1.POLICYNO,RC.PREVIOUSPOLICYNO)) PREVIOUSPOLICYNO ,RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS PMRSTATUS,V.COVERAGE

INTO #MPAV_RENEWALS

FROM MPATPMRC RC

INNER JOIN MCITCUST ON

RC.COMPANYID = MCITCUST.COMPANYID AND

RC.INSUREDNAMENO = MCITCUST.NAMENO

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V.POLICYID = RC.POLICYID AND

V.PMRSEQUENCE = RC.PMRSEQUENCE AND

V.SUBCOMPANYID = RC.SUBCOMPANYID

INNER JOIN MCITCUST M1 ON

MCITCUST.COMPANYID =M1.COMPANYID AND

MCITCUST.CURRENTNAMENO = M1.CURRENTNAMENO

INNER JOIN MPATPMRC RC1 ON

RC1.COMPANYID = M1.COMPANYID AND

RC1.INSUREDNAMENO = M1.NAMENO

LEFT OUTER JOIN MPATCGLUWVERSIONS V1 ON

V1.POLICYID = RC1.POLICYID AND

V1.PMRSEQUENCE = RC1.PMRSEQUENCE AND

V1.SUBCOMPANYID = RC1.SUBCOMPANYID

WHERE

RC.POLICYTYPE ='CGL' AND

RC.POLICYEFFDATE >= '1/1/1998' AND

--FIND THE VERY FIRST VALID TRANSACTION BY LOOKING AT WHICH ONE OF RN/NB/RW IS THE LATEST

RC.TXNISSUED <= @ASOF AND

RC1.TXNISSUED <= @ASOF AND

RC.PMRSEQUENCE = (SELECT MAX(PMRSEQUENCE) FROM MPATPMRC RC2

WHERE RC2.POLICYID = RC.POLICYID AND

RC2.SUBCOMPANYID = RC.SUBCOMPANYID AND

RC2.POLICYYEAR = RC.POLICYYEAR AND

RC2.TXNISSUED <= @ASOF AND

RC2.TXNTYPE IN ('RN','NB','RW','RE') AND

( (RC2.POLICYNO = RC.POLICYNO AND RC.POLICYNO IS NOT NULL)

OR (RC.POLICYNO IS NULL)

) AND

RC2.PMRSTATUS IN ('HS', 'EX', 'IN','PC','PA')AND

RC2.PROCESSINGSTATUS IN ('*','P','A','I','Q')

)

AND ((RC1.TXNTYPE ='CN' AND RC1.CANCELMETHOD <> 'F') OR (RC1.CANCELMETHOD IS NULL OR RC1.TXNTYPE <> 'CN'))

AND RC1.PMRSEQUENCE = (

SELECT MAX(PMRSEQUENCE) FROM MPATPMRC

WHERE MPATPMRC.POLICYID = RC1.POLICYID AND

MPATPMRC.POLICYNO = RC1.POLICYNO AND

MPATPMRC.TXNISSUED <= @ASOF AND

MPATPMRC.PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND

MPATPMRC.PROCESSINGSTATUS IN ('*','C')AND

MPATPMRC.TXNTYPE NOT IN ('EA','ER' )) AND

(

(RC1.TXNTYPE <>'CN' AND RC1.POLICYEXPDATE = RC.POLICYEFFDATE ) OR

(RC1.TXNTYPE ='CN' AND RC1.TXNEFFECTIVE = RC.POLICYEFFDATE )

)

AND (V.COVERAGE = 'B' AND V1.COVERAGE = 'P' OR V.COVERAGE = 'P' AND V1.COVERAGE = 'B' OR

V.COVERAGE = V1.COVERAGE)

GROUP BY RC.POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,

MCITCUST.FULLNAME , RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS ,V.COVERAGE



/**************************************FIND LAST VERSION*********************************/

SELECT

RC.POLICYNO,

RC.POLICYYEAR,

RC.POLICYID,

RC.SUBCOMPANYID,

RC.PMRSEQUENCE,

RC.TXNTYPE,

POLICYEXPDATE = CASE WHEN RC.TXNTYPE = 'CN' THEN RC.TXNEFFECTIVE ELSE RC.POLICYEXPDATE END,

REPORTDATES =CASE

WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE <= RC.TXNISSUED THEN RC.TXNISSUED

WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE > RC.TXNISSUED THEN RC.TXNEFFECTIVE

ELSE RC.POLICYEXPDATE END,

CN_REASON = CASE WHEN RC.TXNTYPE = 'CN' THEN

(CASE WHEN RC.TXNSUBTYPE IN ('PF','NP') THEN 'Non-pay'

WHEN RC.TXNSUBTYPE IN ('IN') THEN 'Ins Request'

WHEN RC.TXNSUBTYPE IN ('UW') THEN 'UW Reasons' ELSE 'Other' END)

Else '' END,

CN_METHOD = CASE WHEN RC.TXNTYPE = 'CN' THEN CANCELMETHOD ELSE '' END,

INS.FULLNAME INSURED,

RC.INSUREDNAMENO,

INS.CURRENTNAMENO,

RC.STATE,

RC.ORIGINCEPTIONDATE

INTO #LAST_VERSION

FROM MPATPMRC RC

INNER JOIN --LOOKING UP THE LATEST VERSION OF POLICIES

(SELECT DISTINCT POLICYNO, POLICYID, SUBCOMPANYID, MAX( PMRSEQUENCE) LASTPMR

FROM MPATPMRC

WHERE

POLICYTYPE ='CGL' AND

PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND

PROCESSINGSTATUS IN ('*','C') AND

TXNTYPE NOT IN ('EA','ER')

AND MPATPMRC.TXNISSUED <= @ASOF

GROUP BY POLICYNO, POLICYID, SUBCOMPANYID) LV ON

RC.POLICYNO = LV.POLICYNO AND

RC.SUBCOMPANYID = LV.SUBCOMPANYID AND

RC.POLICYID = LV.POLICYID AND

RC.PMRSEQUENCE = LV.LASTPMR

LEFT OUTER JOIN MCITCUST INS ON

RC.INSUREDNAMENO = INS.NAMENO

/*=============================================IDENTIFY RENEWALS FOR THE PERIOD==============================*/

SELECT R.POLICYNO,

R.CURRENTNAMENO,

R.POLICYID,

R.PMRSEQUENCE,

R.SUBCOMPANYID,

R.POLICYEFFDATE,

R.FULLNAME,

R.PREVIOUSPOLICYNO,

R.INSUREDNAMENO,

R.PROCESSINGSTATUS,

R.PMRSTATUS,

R.COVERAGE

INTO #RENEWALS

FROM #MPAV_RENEWALS R

LEFT OUTER JOIN #LAST_VERSION LV ON

R.POLICYNO = LV.POLICYNO

--take off pendings

WHERE LV.CN_METHOD <> 'F' AND

POLICYEFFDATE BETWEEN @FROM AND @TO

/* LETS GET RENEWAL BROKER */

SELECT

R.POLICYNO,

P.NAMENO,

C.FULLNAME,

A.CITY

INTO #RN_BRK

FROM #RENEWALS R

INNER JOIN MPATPRODUCERS P ON

R.POLICYID = P.POLICYID AND

R.PMRSEQUENCE = P.PMRSEQUENCE AND

R.SUBCOMPANYID = P.SUBCOMPANYID AND

PAYOR = 1

INNER JOIN MCITCUST C ON

P.NAMENO = C.NAMENO

INNER JOIN MCITADDR A ON

C.MAILINGADDRESS = A.ADDRESSID

/*==============================================GET RENEWING PREMIUMS=========================================*/

--this gives us written premium as of report date

SELECT DISTINCT

R.POLICYNO,

R.POLICYID,

MAX(R.SUBCOMPANYID) SUB,

MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,

MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,

MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,

MAX(MPATPMRC.UNDERWRITER)UW,

RN_DIRECT = SUM(WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),

RN_CEDED_REIN = SUM(WRITTENPREM3),

CEDED_RETAINED = 0

INTO #RN_PREM

FROM #RENEWALS R

LEFT OUTER JOIN MPATPMRC MPATPMRC ON

MPATPMRC.POLICYNO = R.POLICYNO

LEFT OUTER JOIN MPATPREMIUMS MPATPREMIUMS ON

MPATPMRC.COMPANYID = MPATPREMIUMS.COMPANYID AND

MPATPMRC.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND

MPATPMRC.POLICYID = MPATPREMIUMS.POLICYID AND

MPATPMRC.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID

WHERE @CHECKDATE IS NULL AND

MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND

((MPATPMRC.TXNISSUED >= MPATPMRC.TXNEFFECTIVE AND MPATPMRC.TXNISSUED BETWEEN @FROM AND @TO) OR

(MPATPMRC.TXNEFFECTIVE >= MPATPMRC.TXNISSUED AND MPATPMRC.TXNEFFECTIVE BETWEEN @FROM AND @TO))

GROUP BY R.POLICYNO , R .POLICYID

UNION ALL

--this gives us written premium as of report date

SELECT DISTINCT

R.POLICYNO,

R.POLICYID,

MAX(R.SUBCOMPANYID) SUB,

MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,

MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,

MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,

MAX(MPATPMRC.UNDERWRITER) UW,

RN_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,

RN_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,

CEDED_RETAINED= SUM(

(CASE WHEN CEDEDPERCENT1 <> 0 THEN

((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT2 <> 0 THEN

((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT3 <> 0 THEN

((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)

)

FROM #RENEWALS R

LEFT OUTER JOIN MPATPMRC MPATPMRC ON

MPATPMRC.POLICYNO = R.POLICYNO

LEFT OUTER JOIN MPATREINSURANCE MPATREINSURANCE ON

MPATPMRC.COMPANYID = MPATREINSURANCE.COMPANYID AND

MPATPMRC.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND

MPATPMRC.POLICYID = MPATREINSURANCE.POLICYID AND

MPATPMRC.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID AND

MPATPMRC.TREATYID = MPATREINSURANCE.TREATYID

WHERE @CHECKDATE IS NOT NULL AND

MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND

((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED BETWEEN @FROM AND @TO) OR

(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE BETWEEN @FROM AND @TO))

GROUP BY R.POLICYNO , R .POLICYID



/*======================= looks for all policies EXPIRED during report time==========================*/

SELECT

LV.POLICYYEAR,

LV.POLICYNO,

LV.POLICYID,

LV.SUBCOMPANYID,

LV.PMRSEQUENCE,

LV.TXNTYPE,

LV.POLICYEXPDATE,

REASONDROPPED =

CASE WHEN LV.TXNTYPE = 'CN' AND CN_REASON = 'Non-pay' AND R.DESCRIPTION IS NULL

THEN 'Cancelled for non pay'

ELSE

ISNULL(ISNULL(R.DESCRIPTION, CLOSEREASONDESC),'No reason entered') END,

CN_METHOD,

V.COVERAGE,

CURRENTNAMENO,

INSUREDNAMENO,

INSURED,

LV.STATE,

ORIGINCEPTIONDATE,

CLOSECATEGORYDESC,

CLOSEREASONDESC,

COMPETITORDESC,

MKTFOLLOWUPDESC

INTO #EXPIRED

FROM #LAST_VERSION LV

LEFT OUTER JOIN MCITINSURED ON

LV.INSUREDNAMENO = MCITINSURED.NAMENO

LEFT OUTER JOIN MSOTDROPREAS R ON

MCITINSURED.REASONDROPPED = R.REASONID

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V.POLICYID = LV.POLICYID AND

V.PMRSEQUENCE = LV.PMRSEQUENCE AND

V.SUBCOMPANYID = LV.SUBCOMPANYID

LEFT OUTER JOIN MSOTCLOSECATEGORIES CC ON

MCITINSURED.CLOSECATEGORYID = CC.CLOSECATEGORYID

LEFT OUTER JOIN MSOTCLOSEREASONS CR ON

MCITINSURED.CLOSEREASONID = CR.CLOSEREASONID

LEFT OUTER JOIN MSOTCOMPETITORS CO ON

MCITINSURED.COMPETITORID = CO.COMPETITORID

LEFT OUTER JOIN MSOTMKTFOLLOWUP MK ON

MCITINSURED.MKTFOLLOWUPID = MK.MKTFOLLOWUPID

WHERE REPORTDATES BETWEEN CAST(CAST(@FROM AS VARCHAR(11)) AS DATETIME) --DF CHANGE

AND CAST(CAST(@TO AS VARCHAR(11)) AS DATETIME)

/*==============================================GET EXPIRED POLICY INFO=========================================*/

--GET INFO FOR ALL TXNS SO U ALSO GET THE PREMIUMS

SELECT DISTINCT

MAX(MCITCUST.NAMENO) NAMENO,

R.RISKSEQUENCE,

MAX(MCITCUST.CURRENTNAMENO) CURRENTNAMENO,

MAX(MCITCUST.FULLNAME) FULLNAME,

RC.POLICYNO,

RC.POLICYID,

MAX(RC.SUBCOMPANYID) SUB,

MAX(RC.POLICYYEAR) POLICYYEAR,

EXPDATE = MAX(EX.POLICYEXPDATE),

SALES = SUM(CASE WHEN PR.COVERAGETYPE ='P' AND PR.PMRSEQUENCE = EX.PMRSEQUENCE THEN ISNULL(PR.SALES,0) ELSE 0 END),

MAX(RC.UNDERWRITER) UW,

P.HAZARDCLASS,

P.CLASSCODE PRODUCTCODE,

MAX(P.DESCRIPTION) [DESCRIPTION],

DISTRIBUTOR = (CASE WHEN P.DISTRIBUTOR = 1 THEN 'YES' ELSE 'NO' END),--R.PRODUCTCODE

MEMBER = MAX(CASE WHEN MEMBER = 1 THEN 'DEVICE' ELSE 'EXP' END),

SUM(WRITTENPREM1 + WRITTENPREM2) LAYER1_2,

EX_LIMIT = MAX(CASE WHEN V.COV_PROD_AGGREGATE = '1000000' OR V.COV_CGL_AGGREGATE = '1000000' THEN 1

WHEN V.COV_PROD_AGGREGATE = '2000000' OR V.COV_CGL_AGGREGATE = '2000000' THEN 2

WHEN V.COV_PROD_AGGREGATE = '3000000' OR V.COV_CGL_AGGREGATE = '3000000' THEN 3

WHEN V.COV_PROD_AGGREGATE = '4000000' OR V.COV_CGL_AGGREGATE = '4000000' THEN 4

WHEN V.COV_PROD_AGGREGATE = '5000000' OR V.COV_CGL_AGGREGATE = '5000000' THEN 5

WHEN V.COV_PROD_AGGREGATE = '6000000' OR V.COV_CGL_AGGREGATE = '6000000' THEN 6

WHEN V.COV_PROD_AGGREGATE = '7000000' OR V.COV_CGL_AGGREGATE = '7000000' THEN 7

WHEN V.COV_PROD_AGGREGATE = '8000000' OR V.COV_CGL_AGGREGATE = '8000000' THEN 8

WHEN V.COV_PROD_AGGREGATE = '9000000' OR V.COV_CGL_AGGREGATE = '9000000' THEN 9

WHEN V.COV_PROD_AGGREGATE = '10000000' OR V.COV_CGL_AGGREGATE = '10000000' THEN 10 END),

EX_BROKER_GROUP =

(SELECT [DESCRIPTION] FROM MSOTCOMMPLAN

WHERE

SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND

COMMISSIONPLAN =

(SELECT TOP 1 COMMISSIONPLAN FROM MAGTCOMMPLAN

WHERE NAMENO = MPATPRODUCERS.NAMENO AND SUBCOMPANYID =MPATPRODUCERS.SUBCOMPANYID AND

COMMRATEEFFDATE <= EX.POLICYEXPDATE

ORDER BY COMMRATEEFFDATE DESC )),

MAX(BROKER.FULLNAME) BRK_NAME,

MAX(MPATPRODUCERS.COMMISSIONRATE * 100) RATE,

MAX(AA.CITY) BRK_CITY

INTO #EX

FROM MPATPMRC RC

INNER JOIN MPATPREMIUMS PR ON

RC.POLICYID = PR.POLICYID AND

RC.SUBCOMPANYID = PR.SUBCOMPANYID AND

RC.PMRSEQUENCE = PR.PMRSEQUENCE

INNER JOIN #EXPIRED EX ON

EX.POLICYNO = RC.POLICYNO

INNER JOIN MCITCUST ON

RC.COMPANYID = MCITCUST.COMPANYID AND

RC.INSUREDNAMENO = MCITCUST.NAMENO

LEFT OUTER JOIN MPATCGLUWRISKS R ON

PR.RISKSEQUENCE = R.RISKSEQUENCE AND

EX.POLICYID = R.POLICYID AND

EX.PMRSEQUENCE = R.PMRSEQUENCE AND

EX.SUBCOMPANYID = R.SUBCOMPANYID

LEFT OUTER JOIN MSOTPRODUCTS P ON

R.PRODUCTCODE = P.PRODUCTCODE

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V .POLICYID = EX.POLICYID AND

V .PMRSEQUENCE =EX.PMRSEQUENCE AND

V .SUBCOMPANYID = EX.SUBCOMPANYID

INNER JOIN MPATPRODUCERS ON

EX.SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND

EX.POLICYID = MPATPRODUCERS.POLICYID AND

EX.PMRSEQUENCE = MPATPRODUCERS.PMRSEQUENCE AND

MPATPRODUCERS.PAYOR = 1

INNER JOIN MCITCUST BROKER ON

MPATPRODUCERS.COMPANYID = BROKER.COMPANYID AND

MPATPRODUCERS.NAMENO = BROKER.NAMENO

LEFT OUTER JOIN MCITADDR AA ON

BROKER.MAILINGADDRESS = AA.ADDRESSID

WHERE RC.PMRSTATUS IN ('IN','EX','HS','CN') AND

RC.PROCESSINGSTATUS IN ('*','C')

GROUP BY R.RISKSEQUENCE ,RC.POLICYNO, RC.POLICYID,P.HAZARDCLASS, P.CLASSCODE,

MPATPRODUCERS.SUBCOMPANYID, MPATPRODUCERS.NAMENO, EX.POLICYEXPDATE,P.DISTRIBUTOR

ORDER BY RC.POLICYNO





/*****************************************************************************************************************/

SELECT

R.POLICYNO,

EX_DIRECT = SUM( WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),

EX_CEDED_REIN = SUM(WRITTENPREM3),

EX_CEDED_RETAINED= 0

INTO #EX_PREM

FROM MPATPMRC R

INNER JOIN #EXPIRED E ON

R.POLICYNO = E.POLICYNO

LEFT OUTER JOIN MPATPREMIUMS ON

R.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND

R.POLICYID = MPATPREMIUMS.POLICYID AND

R.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID

WHERE @CHECKDATE IS NULL AND

((R.TXNISSUED >= R.TXNEFFECTIVE AND R.TXNISSUED <= @TO) OR

(R.TXNEFFECTIVE >= R.TXNISSUED AND R.TXNEFFECTIVE <= @TO))

GROUP BY R.POLICYNO

UNION ALL

SELECT

R.POLICYNO,

EX_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,

EX_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,

EX_CEDED_RETAINED=

SUM(

(CASE WHEN CEDEDPERCENT1 <> 0 THEN

((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT2 <> 0 THEN

((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT3 <> 0 THEN

((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)

)

FROM MPATPMRC R

INNER JOIN #EXPIRED E ON

R.POLICYNO = E.POLICYNO

LEFT OUTER JOIN MPATREINSURANCE ON

R.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND

R.POLICYID = MPATREINSURANCE.POLICYID AND

R.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID

WHERE @CHECKDATE IS NOT NULL AND

((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED <= @TO) OR

(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE <= @TO))

GROUP BY R.POLICYNO

/******************************************************************************************************************/

EXECUTE ISP_RPT_MKTSEGMENT

SET NOCOUNT OFF

SELECT

E.POLICYNO EXP_POLICY,

E.POLICYEXPDATE,

A.UW,

E.INSURED INS_NAME,

STATE INS_STATE,

BROKER,

BROKER_GROUP = CASE WHEN BROKER_GRP LIKE '%Others%' THEN 'Others'

WHEN BROKER_GRP LIKE '%Preferred%' THEN 'Preferred'

WHEN BROKER_GRP LIKE '%Marsh%' THEN 'Marsh'

WHEN BROKER_GRP LIKE '%Champion%' THEN 'Champion'

ELSE 'Others' END, COMM_RATE,

DOMINANT_PROD DOM_PROD,

DOMINANT_PROD_DESC DESCR,

MK.DISTRIBUTOR,

DOMINANT_HAZ DOM_HAZD,

EX_LIMIT,

MEMBER,

A.SALES,

MKT_SEGMENT,

ISNULL(EX_DIRECT,0) TOTAL_WRTTN,

(ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0)) TOTAL_CEDED,

CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_REIN,0) END ,

CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_RETAINED,0) END ,

NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ((ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0))) END,

XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ISNULL(EX_CEDED_REIN,0) END,

REASON_LOST =

CASE WHEN R.POLICYNO IS NULL THEN

(SELECT 'LOST -'+REASONDROPPED FROM #EXPIRED WHERE POLICYNO = E.POLICYNO)

ELSE '' END,

ISNULL(R.POLICYNO, '') RN_POLICY,

ISNULL(RN_DIRECT,0) RN_TOTAL_WRTTN,

(ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0)) RN_TOTAL_CEDED,

RN_CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_CEDED_REIN,0) END ,

RN_CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(CEDED_RETAINED,0) END ,

RN_NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ((ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0))) END,

RN_XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ISNULL(RN_CEDED_REIN,0) END,

[INSURED INFORCE TODAY?] = CASE WHEN (SELECT COUNT(*)

FROM MPATPMRC

WHERE PMRSTATUS ='IN' AND

INSUREDNAMENO IN

(SELECT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)

)>0 THEN 'YES' ELSE 'NO' END,

[FIRST_JOINED_DATE] =

ISNULL(

(SELECT MIN(DATEJOINED) FROM MCITINSURED

WHERE NAMENO IN

(SELECT DISTINCT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)), ORIGINCEPTIONDATE),

[# OF POLICY YEARS] = (

SELECT COUNT(DISTINCT POLICYYEAR)

FROM MPATPMRC

WHERE PMRSTATUS IN ('EX','HS','IN') AND TXNTYPE IN ('CV','NB','RN','RW') AND PROCESSINGSTATUS ='*'

AND POLICYNO IS NOT NULL AND

INSUREDNAMENO IN (SELECT DISTINCT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)),

MONTHS = (CASE WHEN MONTH(E.POLICYEXPDATE) = 1 THEN 'January'

WHEN MONTH(E.POLICYEXPDATE) = 2 THEN 'February'

WHEN MONTH(E.POLICYEXPDATE) = 3 THEN 'March'

WHEN MONTH(E.POLICYEXPDATE) = 4 THEN 'April'

WHEN MONTH(E.POLICYEXPDATE) = 5 THEN 'May'

WHEN MONTH(E.POLICYEXPDATE) = 6 THEN 'June'

WHEN MONTH(E.POLICYEXPDATE) = 7 THEN 'July'

WHEN MONTH(E.POLICYEXPDATE) = 8 THEN 'August'

WHEN MONTH(E.POLICYEXPDATE) = 9 THEN 'September'

WHEN MONTH(E.POLICYEXPDATE) = 10 THEN 'October'

WHEN MONTH(E.POLICYEXPDATE) = 11 THEN 'November'

WHEN MONTH(E.POLICYEXPDATE) = 12 THEN 'December' END),

BRK_CITY,

RB.FULLNAME RN_BROKER,

RB.CITY RN_BRK_CITY,

ORIG_EXP =

(SELECT MIN(POLICYEXPDATE) POLICYEXPDATE

FROM

MPATPMRC

WHERE POLICYNO = E.POLICYNO AND TXNTYPE IN ('NB','RN','RW')) ,

DROP_CATEGORY = CASE WHEN R.POLICYNO IS NULL THEN CLOSECATEGORYDESC ELSE '' END ,

DROP_REASON = CASE WHEN R.POLICYNO IS NULL THEN CLOSEREASONDESC ELSE '' END ,

COMPETITOR = CASE WHEN R.POLICYNO IS NULL THEN COMPETITORDESC ELSE '' END ,

MKTFOLLOWUP = CASE WHEN R.POLICYNO IS NULL THEN MKTFOLLOWUPDESC ELSE '' END,

MKT_SEGMENTSORT = MS.SORTORDER

FROM #EXPIRED E

LEFT OUTER JOIN #RENEWALS R ON

E.POLICYNO = R.PREVIOUSPOLICYNO

LEFT OUTER JOIN

(SELECT POLICYNO, SUM(SALES)SALES, MAX(UW) UW, SUM(LAYER1_2) PREM_2MIL, MAX(EX_LIMIT) EX_LIMIT,

MAX(BRK_NAME) BROKER, MAX(EX_BROKER_GROUP) BROKER_GRP, MAX(RATE) COMM_RATE, MAX(BRK_CITY) BRK_CITY

FROM #EX

GROUP BY POLICYNO ) A ON

E.POLICYNO = A.POLICYNO

LEFT OUTER JOIN #EX_PREM EP ON

E.POLICYNO = EP.POLICYNO

LEFT OUTER JOIN #RN_PREM RP ON

R.POLICYNO = RP.POLICYNO

LEFT OUTER JOIN MPAT_MKTSEGMENTS MK ON

A.POLICYNO = MK.POLICYNO

LEFT OUTER JOIN MSOTMARKETSEGMENT MS ON

MK.MKT_SEGMENT = MS.MARKETSEGMENT

LEFT OUTER JOIN #RN_BRK RB ON

R.POLICYNO = RB.POLICYNO

WHERE ISNULL(E.CN_METHOD,'X') <> 'F'

ORDER BY R.POLICYNO

View 7 Replies View Related

SELECT In A Table Takes FOREVER

May 25, 2006

SQL Server 2000, QA Database: A table called Telephone_Directory with just 4.000 records.

SELECT * FROM Telephone_Directory is taking forever.

If I stop the select after 1 second I see 162 rows.

If I stop the select after 1 minute I see again 162 rows.

Why this could be happening?

The same querie on Production Database is taking 6 seconds to retrieve the 4.000 records.





View 13 Replies View Related

Fill DataSet Takes Forever, Query Db 7 Sec

Jan 16, 2007

Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.

View 2 Replies View Related

Why Does 'alter Database NorthWind SET ENABLE_BROKER' Take Forever?

Feb 2, 2008

I am trying to execute the following query , in Management Studio. But it takes forever. Can someone tell me why is this happening? I am running the query in 'NorthWind' database.The windows account  under which I am logged into WinXP (windows authentication is enabled for the SQL Server database) is the database owner for NorthWind database.
alter database NorthWind SET ENABLE_BROKER

View 3 Replies View Related

Restore Database Problem - LOADING Forever

Sep 17, 2004

I had to restore a database late this afternoon. I have the database set to FULL recovery. Database backups are performed nightly and transaction log backups are performed every other hour. I decided to perform a point-in-time restore. When I restored this way everything seems to go ok and it finishes. Then the database is grayed and says "Loading". Although I tried 4 separate times, one time allowing over an hour, the grayed out database and "Loading" never goes away.

Freaking out I deleted the "Loading" database (didn't delete logs and backup files) and tried a manual restore from the previous night's backup file. It attached and restored properly and was ready to go in 2 minutes.

Of course I wanted to get the transaction log files restored too, since it had work from earlier today. So I tried another restore via point-in-time and got the same old messages. Currently, the database is running with the previous night's backup restored but the users aren't too keen on having to do 5 hours worth of work to catch up to the previous transaction log backup come Monday morning.

Any suggestions?

Thanks,
JB

View 2 Replies View Related

Update Operation Takes Forever! How Can I Speed It Up?

Jul 20, 2005

I'm having a problem with an update operation in a stored procedure. Itruns so slowly that it is unusable, unless I comment a part out in whichcase it is very fast. However, I need the whole thing :). I have atable of email addresses of people who want to get invited to parties.Each row contains information like email address, city, state, country,and preferences for what types of events are of interest.The primary key is an EMAILID, and has a unique constraint on the emailfield. The stored procedure receives the field data as arguments, andinserts the record if the email address passed is not in the database.This works perfectly. However, if the stored procedure is called for anemail address that already exists, it updates the existing row insteadof doing an insert. This way I can build a web page that lets peoplemodify their preferences, opt in and out of the list and so on.If I am doing an update, the stored procedure runs SUPER SLOW (and thepage times out) unless I comment out the part of the update statementfor city, state, country and zipcode. However, I really need to be ableto update this!My database has 29 million rows.Thank you for telling me anything about how I can speed up this update!Here is the SQL statement to run the stored procedure:declare @now datetime;set @now = GetUTCDate();EXEC usp_EMAIL_Subscribe @Email='dberman@sen.us', @OptOutDate=@now,@Opt_GenInterest=1, @Opt_DatePeople=0, @Opt_NewFriends=1,@Opt_OldFriends=0, @Opt_Business=1, @Opt_Couples=0, @OptOut=0,@Opt_Events=0, @City='Boston', @State='MA', @ZCode='02215',@Country='United States'Here is the stored procedure:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE [usp_EMAIL_Subscribe](@Email [varchar](50),@Opt_GenInterest [tinyint],@Opt_DatePeople [tinyint],@Opt_NewFriends [tinyint],@Opt_OldFriends [tinyint],@Opt_Business [tinyint],@Opt_Couples [tinyint],@OptOut [tinyint],@OptOutDate datetime,@Opt_Events [tinyint],@City [varchar](30), @State [varchar](20), @ZCode [varchar](10),@Country [varchar](20))ASBEGINdeclare @EmailID intset @EmailID = NULL-- Get the EmailID matching the provided email addressset @EmailID = (select EmailID from v_SENWEB_EMAIL_SUBSCRIBERS whereEmailAddress = @Email)-- If the address is new, insert the address and settings. Otherwise,UPDATE existing email profileif @EmailID is null or @EmailID = -1BeginINSERT INTO v_SENWEB_Email_Subscribers(EmailAddress, OptInDate, OptedInBy, City, StateProvinceUS, Country,ZipCode,GeneralInterest, MeetDate, MeetFriends, KeepInTouch, MeetContacts,MeetOtherCouples, MeetAtEvents)VALUES(@Email, GetUTCDate(), 'Subscriber', @City, @State, @Country, @ZCode,@Opt_GenInterest, @Opt_DatePeople,@Opt_NewFriends, @Opt_OldFriends, @Opt_Business, @Opt_Couples,@Opt_Events)EndElseBEGINUPDATE v_SENWEB_EMAIL_SUBSCRIBERSSET--City = @City,--StateProvinceUS = @State,--Country = @Country,--ZipCode = @ZCode,GeneralInterest = @Opt_GenInterest,MeetDate = @Opt_DatePeople,MeetFriends = @Opt_NewFriends,KeepInTouch = @Opt_OldFriends,MeetContacts = @Opt_Business,MeetOtherCouples = @Opt_Couples,MeetAtEvents = @Opt_Events,OptedOut = @OptOut,OptOutDate = CASEWHEN(@OptOut = 1)THEN @OptOutDateWHEN(@OptOut = 0)THEN 0ENDWHERE EmailID = @EmailIDENDreturn @@ErrorENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOFinally, here is the database schema for the table courtesy ofenterprise manager:CREATE TABLE [dbo].[EMAIL_SUBSCRIBERS] ([EmailID] [int] IDENTITY (1, 1) NOT NULL ,[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[OptinDate] [smalldatetime] NULL ,[OptedinBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[JobTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[WorkPhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[HomePhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AddressLine1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StateProvinceUS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StateProvinceOther] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[Country] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SubZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GeneralInterest] [tinyint] NULL ,[MeetDate] [tinyint] NULL ,[MeetFriends] [tinyint] NULL ,[KeepInTouch] [tinyint] NULL ,[MeetContacts] [tinyint] NULL ,[MeetOtherCouples] [tinyint] NULL ,[MeetAtEvents] [tinyint] NULL ,[OptOutDate] [datetime] NULL ,[OptedOut] [tinyint] NOT NULL ,[WhenLastMailed] [datetime] NULL) ON [PRIMARY]GOCREATE UNIQUE CLUSTERED INDEX [IX_EMAIL_SUBSCRIBERS_ADDR] ON[dbo].[EMAIL_SUBSCRIBERS]([EmailAddress]) WITH FILLFACTOR = 90 ON[PRIMARY]GOALTER TABLE [dbo].[EMAIL_SUBSCRIBERS] WITH NOCHECK ADDCONSTRAINT [DF_EMAIL_SUBSCRIBERS_OptedOut] DEFAULT (0) FOR [OptedOut],CONSTRAINT [DF_EMAIL_SUBSCRIBERS_WhenLastMailed] DEFAULT (null) FOR[WhenLastMailed],CONSTRAINT [PK_EMAIL_SUBSCRIBERS] PRIMARY KEY NONCLUSTERED([EmailID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_WhenLastMailed] ON[dbo].[EMAIL_SUBSCRIBERS]([WhenLastMailed] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptOutDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptOutDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptInDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptinDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_ZipCode] ON[dbo].[EMAIL_SUBSCRIBERS]([ZipCode]) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_STATEPROVINCEUS] ON[dbo].[EMAIL_SUBSCRIBERS]([StateProvinceUS]) ON [PRIMARY]GOMeet people for friendship, contacts,or romance using free instant messaging software! See a picture youlike? Click once for a private conversation with that person!<a href="http://www.sen.us"><imgsrc="http://www.sen.us/mirror/SENLogo_62_31.jpg"></a>*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved