Optimizing Store Procedure and Avoiding Cursors....

I'm trying to optimize an ETL batch procedure which takes an extremely long
time to run. I was using cursors for most part of the batch script on
different Store Procedures. Now I'm trying to remove the cursors and replace
them with alternative methods such as using temp tables.
I'm really puzzled as to why the cursor procedure runs much faster than
using temp tables... can anyone point out what is wrong to me?
Below is a snippet of 2 test SPs I've compared against.
TestTable holds 200000 records with 3 columns
1. Using Cursors (This procedure took 4 - 8 secs to run)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare test_cursor cursor for select header, body, footer from testTable
order by header
open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
set @.test = 0
while @.@.fetch_status = 0
begin
set @.test = @.test + 1
fetch next from test_cursor into @.header, @.body, @.footer
end
select @.test
close test_cursor
deallocate test_cursor
2. Using Temp Tables (This procedure took > 1 min)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare @.count int, @.loopcount int
select identity(int,1,1) id, header, body, footer into #temp from testtable
select @.count = @.@.rowcount
set @.loopcount = 1
while @.loopcount <= @.count
begin
select @.header = header, @.body = body, @.footer = footer from
#temp where id = @.loopcount
set @.loopcount = @.loopcount + 1
end
drop table #tempLooking at the code I think it maybe the the where clause in the tempdb,
rember that it has to search through all 200000 records whereas with the
cursor your going through them from start to finish, no searching.
Peter
"Happiness is nothing more than good health and a bad memory."
Albert Schweitzer
"Nestor" wrote:

> I'm trying to optimize an ETL batch procedure which takes an extremely lon
g
> time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and repla
ce
> them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from testtab
le
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>
>|||Both 1 and 2 are cursors! Looping through a temp table is just a cursor
in disguise. Unfortunately your sample code doesn't do anything useful
so it's impossible to suggest an alternative set-based solution without
using cursors. Your goal should be to avoid processing one row at a
time by ANY method.
If you need more help please post DDL, sample data INSERTs and show
your required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello Nestor,
Going through your code, I dont see any reason why the #temp based approach
should be any better
than the Cursor based approach (if it is supposed to be).
The #temp based approach, Loops anyway with a while Loop which is simillar
to Cursor based method.
Normally, when we say we have to get rid of the cursor, we mean to replace
the Cursor based approach
with a SET BASED APPROACH and your #temp based approach still does record by
record. And as Peter says
for each iteration a select is executed against the table.
Hope this helps.
Gopi
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Although I haven't tested it, I'd expect the pseudo-cursor would perform
much better if you add a unique clustered index on the #temp id column.
However, as David pointed out, changing a cursor to another iterative
technique isn't the ideal solution and might not be any faster than the
cursor.
We can't make any specific recommendations because the example you posted
does no productive work. If your actual process does something like execute
another proc for each row returned, the set-based solution would be to use
in-line set-based processing instead. This technique is often orders of
magnitude faster than an iterative approach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Dan,
Could you please share with me what "in-line set-based " means.
I was looking at a Stored Proc which has a nested cursors (One cursor inside
another cursor)
and the While loop of the second a SP is called with the two parameters
(each of them coming
from each of the Cursor code.
Here is the PSeudo-Code :
Declare Cursor Cursor1 Select * from table1
Open Cursor1
Fetch into @.var1
While @.@.FETCH_STATUS <> 1
Declare Cursor Cursor2 Select * from table2
Open Cursor2
Fetch into @.var2
While @.@.FETCH_STATUS <> 1
Exec Spname @.var1, @.var2
........
.....
DEALLOCATE Cursor1
DEALLOCATE Cursor2
I have noticed that the two cursors can be combined into one using
appropriate Joins.
However, I was under the impression I will still have to use a Cursor for
executing the SP
Could you please point me in the right direction if there is a way out.
Thanks,
Gopi
<< If your actual process does something like execute another proc for each
row returned, the set-based
solution would be to use in-line set-based processing instead>>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
> Although I haven't tested it, I'd expect the pseudo-cursor would perform
> much better if you add a unique clustered index on the #temp id column.
> However, as David pointed out, changing a cursor to another iterative
> technique isn't the ideal solution and might not be any faster than the
> cursor.
> We can't make any specific recommendations because the example you posted
> does no productive work. If your actual process does something like
> execute another proc for each row returned, the set-based solution would
> be to use in-line set-based processing instead. This technique is often
> orders of magnitude faster than an iterative approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||> However, I was under the impression I will still have to use a Cursor
for
> executing the SP
Yes, using an SP in this way forces you to use the iterative row-by-row
approach. The set-based solution is to perform whatever logic is
contained in SPname using a query against your tables so that the
entire operation is performed against the data set rather than one row
at a time. For data manipulation operations that usually has advantages
over the cursor in terms of performance, scalability and
maintainability.
David Portas
SQL Server MVP
--|||> Here is the PSeudo-Code :
Your pseudo-code effectively calls the proc for each row returned by the
Cartesian product of table1 and table2. Assuming that the executed proc
simply inserts into a third table, a working code example:
CREATE TABLE table1
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table1 VALUES(1)
INSERT INTO table1 VALUES(2)
INSERT INTO table1 VALUES(3)
CREATE TABLE table2
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table2 VALUES(4)
INSERT INTO table2 VALUES(5)
INSERT INTO table2 VALUES(6)
CREATE TABLE table3
(
Col1 int NOT NULL,
Col2 int NOT NULL,
PRIMARY KEY (Col1, Col2)
)
GO
CREATE PROC Spname
@.var1 int,
@.var2 int
AS
SET NOCOUNT ON
INSERT INTO Table3(Col1, Col2)
VALUES(@.var1, @.var2)
GO
DECLARE @.var1 int,
@.var2 int
DECLARE Cursor1 CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM table1
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.var1
WHILE @.@.FETCH_STATUS <> -1
BEGIN
DECLARE Cursor2 CURSOR LOCAL FOR SELECT * FROM table2
OPEN Cursor2
FETCH NEXT FROM Cursor2 INTO @.var2
WHILE @.@.FETCH_STATUS <> -1
BEGIN
EXEC Spname @.var1, @.var2
FETCH NEXT FROM Cursor2 INTO @.var2
END
CLOSE Cursor2
DEALLOCATE Cursor2
FETCH NEXT FROM Cursor1 INTO @.var1
END
CLOSE Cursor1
DEALLOCATE Cursor1
GO

> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
Exactly. That's the first step.

> However, I was under the impression I will still have to use a Cursor for
> executing the SP
Yes you will. This is why I suggested including the equivalent proc code
inline *instead of* executing the proc for each row. The entire cursor
script and proc code in my functional example could be replaced with a
single insert statement:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
CROSS JOIN table2 t2
Of course, a lot depends on exactly what the called proc does. Also, your
actual code probably isn't a CROSS JOIN. A simple INNER JOIN example:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
JOIN table2 t2 ON
t1.Col1 = t2.SomeColumn
If you need more help, you need to provide details on what the proc actually
does. Actual code will help.
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:%23Sx92CLJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Dan,
> Could you please share with me what "in-line set-based " means.
> I was looking at a Stored Proc which has a nested cursors (One cursor
> inside another cursor)
> and the While loop of the second a SP is called with the two parameters
> (each of them coming
> from each of the Cursor code.
> Here is the PSeudo-Code :
> Declare Cursor Cursor1 Select * from table1
> Open Cursor1
> Fetch into @.var1
> While @.@.FETCH_STATUS <> 1
>
> Declare Cursor Cursor2 Select * from table2
> Open Cursor2
> Fetch into @.var2
> While @.@.FETCH_STATUS <> 1
> Exec Spname @.var1, @.var2
> ........
> .....
> DEALLOCATE Cursor1
> DEALLOCATE Cursor2
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
> Could you please point me in the right direction if there is a way out.
> Thanks,
> Gopi
> << If your actual process does something like execute another proc for
> each row returned, the set-based
> solution would be to use in-line set-based processing instead>>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
>|||Thanks for pointing that out. I've got the code base from an article in
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
Am I doing it wrong with instructions from that article? In any case, what
exactly is SET BASED APPROACH? I do realise and know that SQL Server is
meant to be relational and not sequential, but I need to understand more on
how to apply the alternative first before trying to overhaul the scripts
running in the production servers.
Can anyone give me an equivalent of the cursor procedure I posted using SET
BASE APPROACH so that I can better relate?
Thanks.
Regards,
Nestor
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:e$w%23rZJJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hello Nestor,
> Going through your code, I dont see any reason why the #temp based
> approach should be any better
> than the Cursor based approach (if it is supposed to be).
> The #temp based approach, Loops anyway with a while Loop which is simillar
> to Cursor based method.
> Normally, when we say we have to get rid of the cursor, we mean to replace
> the Cursor based approach
> with a SET BASED APPROACH and your #temp based approach still does record
> by record. And as Peter says
> for each iteration a select is executed against the table.
> Hope this helps.
> Gopi
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The article demonstrates some techniques for iteration but doesn't give
any recommendations as to when to use them. 99.99% of the time
row-by-row processing isn't necessary so those techniques shouldn't be
required. The Set Based approach means that you use SQL DML statements
(basically SELECT, UPDATE, INSERT, DELETE) that operate on a SET of
rows at a time rather than ONE row at a time. That should always be the
standard, preferred way to write code.

> Can anyone give me an equivalent of the cursor procedure I posted
using SET
> BASE APPROACH so that I can better relate?
But your cursor didn't do anything except assign a few variables. How
can we show you how to replace it if we don't know what it's supposed
to do? What we need to know is what you are actually trying to achieve
then we can suggest the right solution. It may even be that there isn't
a set-based solution to your problem - that's rarely the case but we
can't tell you until we understand the problem.
The following article explains the best way to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
--
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing a hidious query...

I have the following query...
/****** Object: Stored Procedure dbo.BENESP_JournalEntrySearch Script
Date: 4/23/2004 11:45:30 AM ******/
CREATE PROCEDURE [DBO].[BSP_JournalEntrySearch]
@.Type int = null,
@.User varchar(50) = null,
@.EntryState int = 2, -- 1 open, 0 closed, 2 either
@.DateEnteredType int = 0, -- 0 both, 1 entrys only, 2 events only
@.DateEnteredStart datetime = null,
@.DateEnteredEnd datetime = null,
@.DateDueStart datetime = null,
@.DateDueEnd datetime = null,
@.AccountFor int = null,
@.messageText varchar(4000) = null,
@.loginname varchar(50) = '', -- Required login name for user perfoming
search
@.IncludeRouted bit = null,
@.enrollee int = null
AS
-- REMEMBER TO REMOVE TIME FROM DATES SO WE CAN CHECK THE DATE ONLY
-- DBO.RemoveTimeFromDate function removes time from datetime's
SET NOCOUNT ON
-- get message list and join it to its initial message from the events table
based on most recent message
select a.EventMessageText,JETR.* from
(select DISTINCT
People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntrySu
bTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
(select count(*) from JournalEntryJunStoredFiles where JournalEntryID =
journalentries.JournalEntryID) as AttachmentCount
from JournalEntries left outer join JournalEvents on
JournalEntries.JournalEntryID = JournalEvents.JournalEntryID
left outer join JournalEntrySubTypes on JournalEntries.JETSubTypeID =
JournalEntrySubTypes.JETSubTypeID
left outer join JournalEntryTypes on JournalEntrySubTypes.JETypeID =
JournalEntryTypes.JETypeID
left outer join JournalEntryTypePermissions on
JournalEntryTypes.JETypeID = JournalEntryTypePermissions.JETypeID
left outer join Accounts on JournalEntries.RelatedAccountID =
Accounts.AccountID
left outer join People on JournalEntries.Enrollee = People.PersonID
where 1=1 and (journalentries.creator = @.loginname or
JournalEntries.JournalEntryID in (select
JournalEntryJunJournalRoutingClass.JournalEntryID
from (JournalEntryJunJournalRoutingClass left outer join
JournalRoutingClasses on JournalEntryJunJournalRoutingClass.JRCID =
JournalRoutingClasses.JRCID)
left outer join JRCJunUsers on JournalRoutingClasses.JRCID
= JRCJunUsers.JRCID
where JRCJunUsers.loginname = @.loginname))
-- search criteria
AND (@.messageText IS NULL OR FREETEXT(JournalEvents.*,@.messagetext)) --
TEXT SEARCH
AND (@.enrollee IS NULL OR JournalEntries.Enrollee = @.enrollee)
AND (@.IncludeRouted IS NULL OR 1=1) -- is routed to user or their
own messages
AND (@.Type IS NULL OR JournalEntrySubTypes.JETSubTypeID = @.Type)
AND (@.AccountFor IS NULL OR JournalEntries.RelatedAccountID = @.AccountFor)
AND ((@.DateDueStart IS NULL AND @.DateDueEnd IS NULL) OR
DBO.RemoveTimeFromDate(JournalEntries.DueDate) between
DBO.RemoveTimeFromDate(@.DateDueStart) AND
DBO.RemoveTimeFromDate(@.DateDueEnd))
AND (@.User IS NULL OR (JournalEntries.Creator = @.user or
JournalEvents.Creator = @.user))
AND ((@.entrystate = 0 and JournalEntries.EntryClosed = 1)
OR (@.entrystate = 1 and JournalEntries.EntryClosed = 0)
OR (@.entrystate = 2 and (JournalEntries.EntryClosed = 0 or
JournalEntries.EntryClosed = 1)))
-- date range entry check stuff
AND (@.DateEnteredStart IS NULL OR ((@.DateEnteredType = 0 OR
@.DateEnteredType = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate)
>= @.dateEnteredStart) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
DBO.RemoveTimeFromDate(JournalEvents.EventDate) >=
DBO.RemoveTimeFromDate(@.DateEnteredStart)))
AND (@.DateEnteredEnd IS NULL OR ((@.DateEnteredType = 0 OR @.DateEnteredType
= 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate) <=
@.DateEnteredEnd) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
DBO.RemoveTimeFromDate(JournalEvents.EventDate) <=
DBO.RemoveTimeFromDate(@.DateEnteredEnd)))
AND permissionid in (select permissionid from
bene_users.dbo.UsersPermissions where loginname = @.loginname)) JETR
-- gets initial message for the entry after we know what messages we have
searched for
left outer join (SELECT JournalEntryID,EventMessageText FROM JournalEvents
je1 WHERE je1.EventDate = (SELECT MIN(je2.EventDate ) FROM JournalEvents
je2 WHERE je1.JournalEntryID = je2.JournalEntryID)) a on JETR.JournalEntryID
= a.JournalEntryID
GO
which takes about 14 seconds to run when I have the last left outer join on
it to get the initial text from the JouranlEvents table... without it it
takes under 1 second to run on a table of thousands of entries in the
journalentries table...
here is my trace on it
SET STATISTICS PROFILE ON
SQL:StmtCompleted 0 0 0 0 SET NOCOUNT ON -- get message list and join
it to its initial message from the events table based on most recent message
SP:StmtCompleted 0 0 0 0 select a.EventMessageText,JETR.* from (select
DISTINCT
People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntrySu
bTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
(select count(*) f
SP:StmtCompleted 153 34 91 0 exec BENESP_JournalEntrySearch
@.loginname='brian_henry'
SQL:StmtCompleted 169 50 97 0 SET STATISTICS PROFILE OFF
SQL:StmtCompleted 0 0 0 0
statistics on it
Application Profile Statistics
Timer resolution (milliseconds)
0 0 Number of INSERT, UPDATE, DELETE statements
0 0 Rows effected by INSERT, UPDATE, DELETE statements
0 0 Number of SELECT statements
2 2 Rows effected by SELECT statements
100 100 Number of user transactions
5 5 Average fetch time
0 0 Cumulative fetch time
0 0 Number of fetches
0 0 Number of open statement handles
0 0 Max number of opened statement handles
0 0 Cumulative number of statement handles
0 0
Network Statistics
Number of server roundtrips
3 3 Number of TDS packets sent
3 3 Number of TDS packets received
209 209 Number of bytes sent
252 252 Number of bytes received
832650 832650
Time Statistics
Cumulative client processing time
0 0 Cumulative wait time on server replies
2.75185e+007 2.75185e+007
any idea how to speed up that last join?! i need that message appended
server side because it takes even more time client side to do it
programmatically in the application
thanks for any help!
here is the basic DDL for this too... i didnt include relations or keys in
it though...
=====================================
CREATE TABLE [dbo].[JRCJunUsers] (
[JRCID] [int] NOT NULL ,
[loginName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntries] (
[JournalEntryID] [int] IDENTITY (1, 1) NOT NULL ,
[RelatedAccountID] [int] NULL ,
[DueDate] [datetime] NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoCreation] [bit] NOT NULL ,
[EntryDate] [datetime] NOT NULL ,
[CloseDate] [datetime] NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastModifiedBy] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryClosed] [bit] NOT NULL ,
[JETSubTypeID] [int] NOT NULL ,
[Enrollee] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunJournalRoutingClass] (
[JournalEntryID] [int] NOT NULL ,
[JRCID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunStoredFiles] (
[JournalEntryID] [int] NOT NULL ,
[FileID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunUsersStatus] (
[loginName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JournalEntryID] [int] NOT NULL ,
[IsClosed] [bit] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntrySubTypes] (
[JETSubTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JETypeID] [int] NOT NULL ,
[DefaultJRC] [int] NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryTypePermissions] (
[JETypeID] [int] NOT NULL ,
[PermissionID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryTypes] (
[JETypeID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeName] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AssociatedFormIDCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEventActions] (
[EventActionID] [int] IDENTITY (1, 1) NOT NULL ,
[EventActionName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ContacteeRequired] [bit] NOT NULL ,
[ActionPerformed] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsAvailable] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEvents] (
[EventID] [int] IDENTITY (1, 1) NOT NULL ,
[JournalEntryID] [int] NOT NULL ,
[EventDate] [datetime] NOT NULL ,
[EventActionID] [int] NOT NULL ,
[Contactee] [int] NULL ,
[ContacteeHandEntered] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EventMessageText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IssueResolved] [bit] NOT NULL ,
[ActionRequired] [bit] NOT NULL ,
[EventHappenedDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalRoutingClasses] (
[JRCID] [int] IDENTITY (1, 1) NOT NULL ,
[JRCName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsPublic] [bit] NOT NULL ,
[Active] [bit] NOT NULL ,
[SystemClass] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountName] [char] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AccountIndustry] [int] NULL ,
[AccountSubIndustry] [int] NULL ,
[RGCompanyID] [int] NULL ,
[BrokerOfRecordsID] [int] NULL ,
[AccountOwner] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Inactive] [bit] NOT NULL ,
[SICCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountDirector] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountBA] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountCSA] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountWeBill] [bit] NOT NULL ,
[AccountBillingAssociate] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AccountExecutive] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountType] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[People] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleInitial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Suffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prefix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [datetime] NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobTitle] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InformalName] AS (rtrim([firstname]) + ' ' + rtrim([lastname]))
) ON [PRIMARY]
GOYes, the query is hidious :-)
A few remarks:
1) you should consider using Dynamic SQL instead of all the optional
parameter handling. The current query will not be able to use any index
on the columns mentioned in the WHERE clause
2) you should use inner joins instead of outer joins when you can. For
example, the query contains the subquery
select JournalEntryJunJournalRoutingClass.JournalEntryID
from JournalEntryJunJournalRoutingClass
left outer join JournalRoutingClasses
on JournalEntryJunJournalRoutingClass.JRCID =
JournalRoutingClasses.JRCID
left outer join JRCJunUsers
on JournalRoutingClasses.JRCID = JRCJunUsers.JRCID
where JRCJunUsers.loginname = @.loginname
The WHERE clause of this query will remove all rows of "left outer"
table JournalEntryJunJournalRoutingClass without related rows in
JournalRoutingClasses and JRCJunUsers. IOW, you can change the two "left
outer join"s to "inner join"s. This will give the optimizer more freedom
in its access path analysis.
3) The resultset "a" will contain just one row for each JournalEntryID.
This means, that you could rewrite the current statement from:
select a.EventMessageText,JETR.*
from (
select DISTINCT
People.InformalName
,Accounts.AccountName
..
,(
select count(*)
from JournalEntryJunStoredFiles
where JournalEntryID = journalentries.JournalEntryID
) as AttachmentCount
from JournalEntries
..
) JETR
left outer join (
SELECT JournalEntryID,EventMessageText
FROM JournalEvents je1
WHERE je1.EventDate = (
SELECT MIN(je2.EventDate)
FROM JournalEvents je2
WHERE je1.JournalEntryID = je2.JournalEntryID
)
) a on JETR.JournalEntryID = a.JournalEntryID
To:
select DISTINCT
People.InformalName
,Accounts.AccountName
..
,(
select count(*)
from JournalEntryJunStoredFiles
where JournalEntryID = journalentries.JournalEntryID
) as AttachmentCount
,(
SELECT EventMessageText
FROM JournalEvents je1
WHERE je1.JournalEntryID = journalentries.JournalEntryID
AND je1.EventDate = (
SELECT MIN(je2.EventDate)
FROM JournalEvents je2
WHERE je2.JournalEntryID = journalentries.JournalEntryID
)
) as EventMessageText
from JournalEntries
..
4) Remove the DISTINCT keyword if it is not necessary.
Hope this helps,
Gert-Jan
Brian Henry wrote:
> I have the following query...
> /****** Object: Stored Procedure dbo.BENESP_JournalEntrySearch Script
> Date: 4/23/2004 11:45:30 AM ******/
> CREATE PROCEDURE [DBO].[BSP_JournalEntrySearch]
> @.Type int = null,
> @.User varchar(50) = null,
> @.EntryState int = 2, -- 1 open, 0 closed, 2 either
> @.DateEnteredType int = 0, -- 0 both, 1 entrys only, 2 events only
> @.DateEnteredStart datetime = null,
> @.DateEnteredEnd datetime = null,
> @.DateDueStart datetime = null,
> @.DateDueEnd datetime = null,
> @.AccountFor int = null,
> @.messageText varchar(4000) = null,
> @.loginname varchar(50) = '', -- Required login name for user perfoming
> search
> @.IncludeRouted bit = null,
> @.enrollee int = null
> AS
> -- REMEMBER TO REMOVE TIME FROM DATES SO WE CAN CHECK THE DATE ONLY
> -- DBO.RemoveTimeFromDate function removes time from datetime's
> SET NOCOUNT ON
> -- get message list and join it to its initial message from the events tab
le
> based on most recent message
> select a.EventMessageText,JETR.* from
> (select DISTINCT
> People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntry
SubTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
> (select count(*) from JournalEntryJunStoredFiles where JournalEntryID =
> journalentries.JournalEntryID) as AttachmentCount
> from JournalEntries left outer join JournalEvents on
> JournalEntries.JournalEntryID = JournalEvents.JournalEntryID
> left outer join JournalEntrySubTypes on JournalEntries.JETSubTypeID =
> JournalEntrySubTypes.JETSubTypeID
> left outer join JournalEntryTypes on JournalEntrySubTypes.JETypeID =
> JournalEntryTypes.JETypeID
> left outer join JournalEntryTypePermissions on
> JournalEntryTypes.JETypeID = JournalEntryTypePermissions.JETypeID
> left outer join Accounts on JournalEntries.RelatedAccountID =
> Accounts.AccountID
> left outer join People on JournalEntries.Enrollee = People.PersonID
> where 1=1 and (journalentries.creator = @.loginname or
> JournalEntries.JournalEntryID in (select
> JournalEntryJunJournalRoutingClass.JournalEntryID
> from (JournalEntryJunJournalRoutingClass left outer join
> JournalRoutingClasses on JournalEntryJunJournalRoutingClass.JRCID =
> JournalRoutingClasses.JRCID)
> left outer join JRCJunUsers on JournalRoutingClasses.JRC
ID
> = JRCJunUsers.JRCID
> where JRCJunUsers.loginname = @.loginname))
> -- search criteria
> AND (@.messageText IS NULL OR FREETEXT(JournalEvents.*,@.messagetext)) -
-
> TEXT SEARCH
> AND (@.enrollee IS NULL OR JournalEntries.Enrollee = @.enrollee)
> AND (@.IncludeRouted IS NULL OR 1=1) -- is routed to user or their
> own messages
> AND (@.Type IS NULL OR JournalEntrySubTypes.JETSubTypeID = @.Type)
> AND (@.AccountFor IS NULL OR JournalEntries.RelatedAccountID = @.AccountFo
r)
> AND ((@.DateDueStart IS NULL AND @.DateDueEnd IS NULL) OR
> DBO.RemoveTimeFromDate(JournalEntries.DueDate) between
> DBO.RemoveTimeFromDate(@.DateDueStart) AND
> DBO.RemoveTimeFromDate(@.DateDueEnd))
> AND (@.User IS NULL OR (JournalEntries.Creator = @.user or
> JournalEvents.Creator = @.user))
> AND ((@.entrystate = 0 and JournalEntries.EntryClosed = 1)
> OR (@.entrystate = 1 and JournalEntries.EntryClosed = 0)
> OR (@.entrystate = 2 and (JournalEntries.EntryClosed = 0 or
> JournalEntries.EntryClosed = 1)))
> -- date range entry check stuff
> AND (@.DateEnteredStart IS NULL OR ((@.DateEnteredType = 0 OR
> @.DateEnteredType = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate)
> DBO.RemoveTimeFromDate(JournalEvents.EventDate) >=
> DBO.RemoveTimeFromDate(@.DateEnteredStart)))
> AND (@.DateEnteredEnd IS NULL OR ((@.DateEnteredType = 0 OR @.DateEnteredTy
pe
> = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate) <=
> @.DateEnteredEnd) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
> DBO.RemoveTimeFromDate(JournalEvents.EventDate) <=
> DBO.RemoveTimeFromDate(@.DateEnteredEnd)))
> AND permissionid in (select permissionid from
> bene_users.dbo.UsersPermissions where loginname = @.loginname)) JETR
> -- gets initial message for the entry after we know what messages we have
> searched for
> left outer join (SELECT JournalEntryID,EventMessageText FROM JournalEvents
> je1 WHERE je1.EventDate = (SELECT MIN(je2.EventDate ) FROM JournalEvents
> je2 WHERE je1.JournalEntryID = je2.JournalEntryID)) a on JETR.JournalEntry
ID
> = a.JournalEntryID
> GO
> which takes about 14 seconds to run when I have the last left outer join o
n
> it to get the initial text from the JouranlEvents table... without it it
> takes under 1 second to run on a table of thousands of entries in the
> journalentries table...
> here is my trace on it
> SET STATISTICS PROFILE ON
> SQL:StmtCompleted 0 0 0 0 SET NOCOUNT ON -- get message list and join
> it to its initial message from the events table based on most recent messa
ge
> SP:StmtCompleted 0 0 0 0 select a.EventMessageText,JETR.* from (select
> DISTINCT
> People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntry
SubTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
> (select count(*) f
> SP:StmtCompleted 153 34 91 0 exec BENESP_JournalEntrySearch
> @.loginname='brian_henry'
> SQL:StmtCompleted 169 50 97 0 SET STATISTICS PROFILE OFF
> SQL:StmtCompleted 0 0 0 0
> statistics on it
> Application Profile Statistics
> Timer resolution (milliseconds)
> 0 0 Number of INSERT, UPDATE, DELETE statements
> 0 0 Rows effected by INSERT, UPDATE, DELETE statements
> 0 0 Number of SELECT statements
> 2 2 Rows effected by SELECT statements
> 100 100 Number of user transactions
> 5 5 Average fetch time
> 0 0 Cumulative fetch time
> 0 0 Number of fetches
> 0 0 Number of open statement handles
> 0 0 Max number of opened statement handles
> 0 0 Cumulative number of statement handles
> 0 0
> Network Statistics
> Number of server roundtrips
> 3 3 Number of TDS packets sent
> 3 3 Number of TDS packets received
> 209 209 Number of bytes sent
> 252 252 Number of bytes received
> 832650 832650
> Time Statistics
> Cumulative client processing time
> 0 0 Cumulative wait time on server replies
> 2.75185e+007 2.75185e+007
> any idea how to speed up that last join?! i need that message appended
> server side because it takes even more time client side to do it
> programmatically in the application
> thanks for any help!
> here is the basic DDL for this too... i didnt include relations or keys in
> it though...
[snip]|||for security reasons dynamic SQL is not an option, but thanks for the other
ideas, no one gets select persmission on any tables, just stored procedure
execute permissions
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:435D5DE8.704A3C31@.toomuchspamalready.nl...
> Yes, the query is hidious :-)
> A few remarks:
> 1) you should consider using Dynamic SQL instead of all the optional
> parameter handling. The current query will not be able to use any index
> on the columns mentioned in the WHERE clause
> 2) you should use inner joins instead of outer joins when you can. For
> example, the query contains the subquery
> select JournalEntryJunJournalRoutingClass.JournalEntryID
> from JournalEntryJunJournalRoutingClass
> left outer join JournalRoutingClasses
> on JournalEntryJunJournalRoutingClass.JRCID =
> JournalRoutingClasses.JRCID
> left outer join JRCJunUsers
> on JournalRoutingClasses.JRCID = JRCJunUsers.JRCID
> where JRCJunUsers.loginname = @.loginname
> The WHERE clause of this query will remove all rows of "left outer"
> table JournalEntryJunJournalRoutingClass without related rows in
> JournalRoutingClasses and JRCJunUsers. IOW, you can change the two "left
> outer join"s to "inner join"s. This will give the optimizer more freedom
> in its access path analysis.
> 3) The resultset "a" will contain just one row for each JournalEntryID.
> This means, that you could rewrite the current statement from:
> select a.EventMessageText,JETR.*
> from (
> select DISTINCT
> People.InformalName
> ,Accounts.AccountName
> ...
> ,(
> select count(*)
> from JournalEntryJunStoredFiles
> where JournalEntryID = journalentries.JournalEntryID
> ) as AttachmentCount
> from JournalEntries
> ...
> ) JETR
> left outer join (
> SELECT JournalEntryID,EventMessageText
> FROM JournalEvents je1
> WHERE je1.EventDate = (
> SELECT MIN(je2.EventDate)
> FROM JournalEvents je2
> WHERE je1.JournalEntryID = je2.JournalEntryID
> )
> ) a on JETR.JournalEntryID = a.JournalEntryID
> To:
> select DISTINCT
> People.InformalName
> ,Accounts.AccountName
> ...
> ,(
> select count(*)
> from JournalEntryJunStoredFiles
> where JournalEntryID = journalentries.JournalEntryID
> ) as AttachmentCount
> ,(
> SELECT EventMessageText
> FROM JournalEvents je1
> WHERE je1.JournalEntryID = journalentries.JournalEntryID
> AND je1.EventDate = (
> SELECT MIN(je2.EventDate)
> FROM JournalEvents je2
> WHERE je2.JournalEntryID = journalentries.JournalEntryID
> )
> ) as EventMessageText
> from JournalEntries
> ...
> 4) Remove the DISTINCT keyword if it is not necessary.
> Hope this helps,
> Gert-Jan
> Brian Henry wrote:
> [snip]|||I respect that security policy. Please note that the stored procedure
could be the one that is generating and executing the dynamic SQL. Maybe
that is within the limits of the security policy.
Good luck,
Gert-Jan
Brian Henry wrote:
> for security reasons dynamic SQL is not an option, but thanks for the othe
r
> ideas, no one gets select persmission on any tables, just stored procedure
> execute permissions
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:435D5DE8.704A3C31@.toomuchspamalready.nl...|||thanks! forgot to check the joins... must of been in a daze... been sick for
a few ws now... trying to code while sick with mono... which is resulting
in some very hidious SQL... just the last join alone being an outer join
which should of been an inner join reduced it from 14 seconds of execution
time (it created over 5 million rows server side and filtered it to 500)
with the outer join... to the inner join which now takes under 1 second to
execute and only created 600 rows max for the 500 it displays server side
(baseing on the trace of the execution plan and how many rows are being
moved where and such) thanks for the help
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:435E6ED8.89722775@.toomuchspamalready.nl...
>I respect that security policy. Please note that the stored procedure
> could be the one that is generating and executing the dynamic SQL. Maybe
> that is within the limits of the security policy.
> Good luck,
> Gert-Jan
>
> Brian Henry wrote:sql

Optimizer in Stored Procedure

Hello
We have a question about the optimizer in Stored Procedure.
The Problem ist as follow
We have 3 tabels, every tabel has columns
key1 ((rtrim(([number] + right(('0000' + convert(varchar
(4),[position])),4) + right(('00' + convert(varchar(2),
[type])),2)))))
number char(8)
position num(4)
type num(2)
every tabel has a index on the column key1
We take the following statement in der sql query analyzer
set rowcount 1
select key1 from tabel1 where key1 < '12345678123412'
union all
select key1 from tabel2 where key1 < '12345678123412'
union all
select key1 from tabel3 where key1 < '12345678123412'
order by key1 desc
The executen plan gives the following information
a index seek over index from key1 ordered backward for
tabel1, tabel2 and tabel3 ist executed (exectly 1 lines is
selected for every tabel)
with merg join concatenation are the tabels merged.
now we take an output with max 3 lines very fast
this is exactil what we want.
When we put this statement in a Stored procedure and
execute this stored procedure, the execution is very long
when there are many lines in the tabels
The executen plan gives for the executen from the stored
procedure the following information
a index scann over index form key1 for tabel1, tabel2 and
tabel3 ist executed (most of the lines of the tabels will
be selected)
a compute scalar / filter / sort ist peformed vor every
scann
with merg join concatenation are the tabels merged.
now we take an output with max 3 lines very slow when
there are many lines in the tables
Wy is there a difference betwen this two execution planes
can anybody help us
thanks very much
PeterPeter,
this question can only be answered based on the actual queries. Usually,
the queries you run in Query Analyser or not identical to the queries in
a stored procedure (because of local variables, etc.).
If you are using SQL Server 7.0 or later, you could drop the "set
rowcount" and use the TOP keyword.
select TOP 1 key1 from (
select key1 from tabel1 where key1 < '12345678123412'
union all
select key1 from tabel2 where key1 < '12345678123412'
union all
select key1 from tabel3 where key1 < '12345678123412'
) as T
order by key1 desc
Hope this helps,
Gert-Jan
Peter Wyss wrote:
> Hello
> We have a question about the optimizer in Stored Procedure.
> The Problem ist as follow
> We have 3 tabels, every tabel has columns
> key1 ((rtrim(([number] + right(('0000' + convert(varchar
> (4),[position])),4) + right(('00' + convert(varchar(2),
> [type])),2)))))
> number char(8)
> position num(4)
> type num(2)
> every tabel has a index on the column key1
> We take the following statement in der sql query analyzer
> set rowcount 1
> select key1 from tabel1 where key1 < '12345678123412'
> union all
> select key1 from tabel2 where key1 < '12345678123412'
> union all
> select key1 from tabel3 where key1 < '12345678123412'
> order by key1 desc
> The executen plan gives the following information
> a index seek over index from key1 ordered backward for
> tabel1, tabel2 and tabel3 ist executed (exectly 1 lines is
> selected for every tabel)
> with merg join concatenation are the tabels merged.
> now we take an output with max 3 lines very fast
> this is exactil what we want.
> When we put this statement in a Stored procedure and
> execute this stored procedure, the execution is very long
> when there are many lines in the tabels
> The executen plan gives for the executen from the stored
> procedure the following information
> a index scann over index form key1 for tabel1, tabel2 and
> tabel3 ist executed (most of the lines of the tabels will
> be selected)
> a compute scalar / filter / sort ist peformed vor every
> scann
> with merg join concatenation are the tabels merged.
> now we take an output with max 3 lines very slow when
> there are many lines in the tables
> Wy is there a difference betwen this two execution planes
> can anybody help us
> thanks very much
> Petersql

Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but then starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_date)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT index seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David
|||We are Declaring the variables and using a SELECT to set the value when we cut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Employees Union may not have been entered correctly which comes from our mainframe.
All our Employee data is from the mainframe, but the mileage_rate table has no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut and pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_date.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>
|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effective_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)
sql

Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is call
ed from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer
and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but the
n starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_da
te)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_
start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints
it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT in
dex seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_dat
e.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David|||We are Declaring the variables and using a SELECT to set the value when we c
ut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Empl
oyees Union may not have been entered correctly which comes from our mainfra
me.
All our Employee data is from the mainframe, but the mileage_rate table has
no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut a
nd pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_dat
e.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is ca
lled from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyz
er and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but t
hen starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >
= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effectiv
e_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hint
s it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)

optimize the stored procedure

CREATE PROCEDURE emp_summary

@.emp_id int ,@.start_date datetime=0,@.end_date datetime=0

AS

SET NOCOUNT ON

IF @.start_date=0 AND @.end_date=0

BEGIN

SET @.end_date=getdate()

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date

END

ELSE

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date+1
GO

This is the Stored procedure i wrote to get the emp summary with date range and with no date ranges.If i pass start_date and end_Date Sp executes 'else' part if dont pass the parameters it execultes 'IF' part.Can i optimize this SP further?I'd use:CREATE PROCEDURE
@.emp_id INT
, @.start_date DATETIME = NULL
, @.end_date DATETIME = NULL
AS

SELECT *
FROM emp
WHERE emp_id = @.emp_id
AND join_date BETWEEN Coalesce(@.start_date, join_date) AND Coalesce(@.end_date, join_date)

RETURN
GOIf you can't afford the table scan, I'd resort to using dynamic SQL, but I'd try this first.

-PatP

Optimize procedure

hi

below is stored procedure takes 5 to 10 minuute to execute..

i want to make this fast...below is code......

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

exec USp_Reconciliation 'RECONCILIATION'

ALTER PROCEDURE USp_Reconciliation
(
@.CommandType varchar(50)=null,
@.fileFormatId numeric =null,
@.FDate varchar(10)=null,
@.TDate varchar(10)=null
)

AS

BEGIN

IF @.CommandType='RECONCILIATION'
BEGIN

DECLARE @.DataFormat_Name VARCHAR(50)
DECLARE @.DataFormat_ID NUMERIC
DECLARE @.Folio_No VARCHAR(50)
DECLARE @.Scheme_Code VARCHAR(50)
DECLARE @.SchemeGroup VARCHAR(50)
DECLARE @.Amount NUMERIC
DECLARE @.Cheque_No VARCHAR(50)
DECLARE @.Compare_Status VARCHAR(50)
DECLARE @.Tradedate DATETIME
DECLARE @.Sip_Status CHAR(1)
DECLARE @.DrCr CHAR(1)
DECLARE @.Format_ID NUMERIC

Blocks for Mutiple equal countExact 'Mutiple Records' updated to 'Success' //for Instrm_no and Cheque_no

select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
into #MultipleChequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)
--Select * from #MultipleChequeno where format_id=82

--//For Folio_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name
into #Multiplefoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

//For Channels User_Trxn_no for Multiple Records
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
into #Multiple_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

-- Update CAMS files-
--

For IBank CMS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultipleChequeno.cams_upload_Details_id from #MultipleChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultipleChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultipleChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultipleChequeno.additionalfield6 and
#MultipleChequeno.format_id in ('82'))

--For HDFC CMS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultipleChequeno.cams_upload_Details_id from #MultipleChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultipleChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultipleChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultipleChequeno.additionalfield13 and
#MultipleChequeno.format_id in ('83'))

--IBANK SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('85','86'))

--BJ SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='EC' and
#Multiplefoliono.format_id in ('88','89'))

--HDFC SI-
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiple_user_trxnno.cams_upload_Details_id from #Multiple_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Multiple_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiple_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiple_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiple_user_trxnno.scheme_code and
#Multiple_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Multiplefoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.format_id in ('99'))

-- Update CMS files-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplechequeno.cms_upload_Details_id from #Multiplechequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplechequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplechequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#Multiplechequeno.additionalfield6 and
#Multiplechequeno.format_id in ('82'))

-HDFC-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplechequeno.cms_upload_Details_id from #Multiplechequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplechequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplechequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield13=#Multiplechequeno.additionalfield13 and
#Multiplechequeno.format_id in ('83'))

IBANK SI-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('85','86'))

-BJ SI

update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='EC' and
#Multiplefoliono.format_id in ('88','89'))

HDFC SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiple_user_trxnno.cms_upload_Details_id from #Multiple_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Multiple_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiple_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiple_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiple_user_trxnno.scheme_code and
#Multiple_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplefoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.format_id in ('99'))

drop table #MultipleChequeno
drop table #Multiplefoliono
drop table #Multiple_user_trxnno

Exact 'Pending' to pending updated to 'Success'Blocks for Pending equal count
--//for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
into #PendingChequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no) and count(cams.instrm_no)>1

--//For Folio_no Exact 'Pending' updated to 'Success
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name
into #Pendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no) and count(cams.Folio_no)>1

//For Channels User_Trxn_no for 'Pending'-- Exact 'Pending' updated to 'Success
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
into #Pending_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2) and count(cams.user_trxnno)>1

-- Update CAMS files-
--
--IBANK--

update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #PendingChequeno.cams_upload_Details_id from #PendingChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#PendingChequeno.additionalfield6 and
#PendingChequeno.format_id in ('82'))

--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #PendingChequeno.cams_upload_Details_id from #PendingChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#PendingChequeno.additionalfield13 and
#PendingChequeno.format_id in ('83'))

--IBANK SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('85','86'))

BJ SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='EC' and
#Pendingfoliono.format_id in ('88','89'))

HDFC SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('90','91'))

--Channel
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pending_user_trxnno.cams_upload_Details_id from #Pending_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Pending_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pending_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pending_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pending_user_trxnno.scheme_code and
#Pending_user_trxnno.format_id in ('98'))

--RTGS-
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Pendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.format_id in ('99'))

-- Update CMS files-pending to pending --
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #PendingChequeno.cms_upload_Details_id from #PendingChequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#PendingChequeno.additionalfield6 and
#PendingChequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #PendingChequeno.cms_upload_Details_id from #PendingChequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield13=#PendingChequeno.additionalfield13 and
#PendingChequeno.format_id in ('83'))

--IBANK SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('85','86'))

--BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='EC' and
#Pendingfoliono.format_id in ('88','89'))

--HDFC SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pending_user_trxnno.cms_upload_Details_id from #Pending_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Pending_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pending_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pending_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pending_user_trxnno.scheme_code and
#Pending_user_trxnno.format_id in ('98'))

--RTGS-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Pendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.format_id in ('99'))

drop table #PendingChequeno
drop table #Pendingfoliono
drop table #Pending_user_trxnno

For Updating the Exact count 'Redundant' to Success'
--//for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #Redundantchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)

--//For Folio_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #Redundantfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

//For Channels User_Trxn_no for 'Pending'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #Redundant_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

-- Update CAMS filesREDUNDANT to REDUNDANT-
--
--IBANK
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantchequeno.cams_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_slip_No=#Redundantchequeno.additionalfield6 and
#Redundantchequeno.format_id in ('82'))
-- HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantchequeno.cams_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_slip_No=#Redundantchequeno.additionalfield13 and
#Redundantchequeno.format_id in ('83'))

--IBANK SI --
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='EC' and
#Redundantfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
pruamc.Tbl_CAMS_UploadDetails.name=#Redundantfoliono.additionalfield2 and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundant_user_trxnno.cams_upload_Details_id from #Redundant_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Redundant_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundant_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundant_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundant_user_trxnno.scheme_code and
#Redundant_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.format_id in ('99'))

-- Update CMS filesREDUNDANT to REDUNDANT-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantchequeno.cms_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#Redundantchequeno.additionalfield6 and
#Redundantchequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantchequeno.cms_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#Redundantchequeno.additionalfield13 and
#Redundantchequeno.format_id in ('83'))

--IBANK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='EC' and
#Redundantfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundant_user_trxnno.cms_upload_Details_id from #Redundant_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Redundant_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundant_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundant_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundant_user_trxnno.scheme_code and
#Redundant_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.format_id in ('99'))

drop table #Redundantchequeno
drop table #Redundantfoliono
drop table #Redundant_user_trxnno

-- Update CAMS filesREDUNDANT to PENDING-
--

-for Channels exact enter of 'Pending' in CMS and 'Redundant' in Purchase
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #RedundantchequenoChannel from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending' and cms.format_id='98'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

--Select * from #RedundantchequenoChannel
--CHANNEL-- CAMS UPDATE

update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantchequenoChannel.cams_upload_Details_id from #RedundantchequenoChannel join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#RedundantchequenoChannel.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantchequenoChannel.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantchequenoChannel.scheme_code and
#RedundantchequenoChannel.format_id in ('98'))

--CHANNEL-- CMS UPDATE
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantchequenoChannel.cms_upload_Details_id from #RedundantchequenoChannel join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#RedundantchequenoChannel.user_trxnno
where --pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantchequenoChannel.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantchequenoChannel.scheme_code and
#RedundantchequenoChannel.format_id in ('98'))

For Updating the Exact count 'Redundant' in Purchase and 'Pending' in CMS to Success'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #RedundantPendingchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)

--//For exact count of 'Pending' in CMS and 'Redundant' in Purchase for CMS Banks on folio_no basis
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #RedundantPendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingchequeno.cams_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#RedundantPendingchequeno.additionalfield6 and
#RedundantPendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingchequeno.cams_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#RedundantPendingchequeno.additionalfield13 and
#RedundantPendingchequeno.format_id in ('83'))

--IBNAK SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='EC' and
#RedundantPendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
pruamc.Tbl_CAMS_UploadDetails.name=#RedundantPendingfoliono.additionalfield2 and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('90','91'))

--RTGS

update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.format_id in ('99'))

-- Update CMS filesREDUNDANT to PENDING-
--
--IBANK
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingchequeno.cms_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield6=#RedundantPendingchequeno.additionalfield6 and
#RedundantPendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingchequeno.cms_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#RedundantPendingchequeno.additionalfield13 and
#RedundantPendingchequeno.format_id in ('83'))

--IBNAK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('85','86'))

--BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='EC' and
#RedundantPendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('90','91'))

--RTGS --
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.format_id in ('99'))

drop table #RedundantPendingchequeno
drop table #RedundantPendingfoliono
drop table #RedundantchequenoChannel

-- Update FOR CHANNEL filesMULTIPLE to PENDING-
--

-for Channels exact enter of 'Pending' in CMS and 'Multiple Records' in Purchase
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #MultiplechequenoChannel from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending' and cms.format_id='98'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

-- CHANNEL--CAMS UPDATE
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplechequenoChannel.cams_upload_Details_id from #MultiplechequenoChannel join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#MultiplechequenoChannel.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplechequenoChannel.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplechequenoChannel.scheme_code and
#MultiplechequenoChannel.format_id in ('98'))

-- CHANNEL--CMS UPDATE
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplechequenoChannel.cms_upload_Details_id from #MultiplechequenoChannel join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#MultiplechequenoChannel.user_trxnno
where --pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplechequenoChannel.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplechequenoChannel.scheme_code and
#MultiplechequenoChannel.format_id in ('98'))

-- Update CAMS filesMULTIPLE to PENDING-
--

For Updating the Exact count 'Multiple Records' in Purchase and 'Pending' in CMS to Success'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #MultiplePendingchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)

--//For exact count of 'Pending' in CMS and 'Multiple Records' in Purchase for CMS Banks on folio_no basis
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #MultiplePendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingchequeno.cams_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingchequeno.cams_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultiplePendingchequeno.additionalfield6 and
#MultiplePendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingchequeno.cams_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingchequeno.cams_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultiplePendingchequeno.additionalfield13 and
#MultiplePendingchequeno.format_id in ('83'))

--IBANK SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='EC' and
#MultiplePendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('90','91'))

--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.format_id in ('99'))

-- Update CMS filesMULTIPLE to PENDING-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingchequeno.cms_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield6=#MultiplePendingchequeno.additionalfield6 and
#MultiplePendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingchequeno.cms_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#MultiplePendingchequeno.additionalfield13 and
#MultiplePendingchequeno.format_id in ('83'))

--IBANK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='EC' and
#MultiplePendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('90','91'))

--RTGS --
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.format_id in ('99'))

drop table #MultiplePendingchequeno
drop table #MultiplePendingfoliono
drop table #MultiplechequenoChannel

DECLARE CUR_DATAFORMAT CURSOR FOR
SELECT DataFormat_ID,DataFormat_Name FROM Tbl_DataFormat WITH(NOLOCK) WHERE DataFormat_Isactive =1 AND DataFormat_Name <>'CAMS'
OPEN CUR_DATAFORMAT
FETCH NEXT FROM CUR_DATAFORMAT INTO @.DataFormat_ID,@.DataFormat_Name

WHILE @.@.FETCH_STATUS =0
BEGIN

IF @.DataFormat_Name ='IBANK'

Begin

proper query for updating success record in purchase file
select instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessIBANK from tbl_cams_uploaddetails WITH(NOLOCK)
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
cams_schemegroup=cms_schemegroup --and
--tbl_cams_uploaddetails.instrm_date=tbl_cms_uploaddetails.instrm_date --'Added Gopal feb 07 new criteria
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and tbl_cms_uploaddetails.Format_ID=82 and
--Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploaddetails.additionalfield6 and
Payment_Mechanism<>'EC' and Payment_Mechanism<>'M' and Payment_Mechanism <> 'TR'
group by instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
having count(instrm_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANK
where tbl_cams_uploaddetails.instrm_no=#SuccessIBANK.instrm_no and
tbl_cams_uploaddetails.amount=#SuccessIBANK.amount and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessIBANK.cams_schemegroup and
tbl_cams_uploaddetails.payin_slip_no=#SuccessIBANK.payin_slip_no and
--tbl_cams_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cams_uploaddetails.compare_status='Pending'

proper query for updating success record in bank file
print 'g6'
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success', ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANK
where tbl_cms_uploaddetails.cheque_no=#SuccessIBANK.instrm_no and
tbl_cms_uploaddetails.amount=#SuccessIBANK.amount and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessIBANK.cams_schemegroup and
tbl_cms_uploaddetails.additionalfield6=#SuccessIBANK.payin_slip_no and
--tbl_cms_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cms_uploaddetails.compare_status='Pending'and tbl_cms_uploaddetails.Format_ID=82

drop table #SuccessIBANK

DD for IBank
select instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessIBANKDD from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
cams_schemegroup=cms_schemegroup --and
--tbl_cams_uploaddetails.instrm_date=tbl_cms_uploaddetails.instrm_date --'Added Gopal feb 07 new criteria
where tbl_cams_uploaddetails.compare_status='Pending' and
(0.98 * tbl_cams_uploaddetails.amount <= tbl_cms_uploaddetails.amount
and tbl_cams_uploaddetails.amount >= tbl_cms_uploaddetails.amount)

and tbl_cms_uploaddetails.compare_status='Pending' and tbl_cms_uploaddetails.Format_ID=82 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploaddetails.additionalfield6 and
Payment_Mechanism in ('D','P')
group by instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
having count(instrm_no) = 1

--

-After chnage as on 6th march
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success', ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANKDD
where tbl_cms_uploaddetails.cheque_no=#SuccessIBANKDD.instrm_no and
-- tbl_cms_uploaddetails.amount =0.98 * #SuccessIBANKDD.amount or
-- tbl_cms_uploaddetails.amount = #SuccessIBANKDD.amount or
-- tbl_cms_uploaddetails.amount < 0.98 * #SuccessIBANKDD.amount and
(0.98 * #SuccessIBANKDD.amount <= tbl_cms_uploaddetails.amount
and #SuccessIBANKDD.amount >= tbl_cms_uploaddetails.amount) and

tbl_cms_uploaddetails.cms_schemegroup=#SuccessIBANKDD.cams_schemegroup and
tbl_cms_uploaddetails.additionalfield6=#SuccessIBANKDD.payin_slip_no and
--tbl_cms_uploaddetails1.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cms_uploaddetails.compare_status='Pending'and tbl_cms_uploaddetails.Format_ID=82

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANKDD
where tbl_cams_uploaddetails.instrm_no=#SuccessIBANKDD.instrm_no and
-- 0.98 * tbl_cams_uploaddetails.amount = #SuccessIBANKDD.amount or
-- tbl_cams_uploaddetails.amount = #SuccessIBANKDD.amount or
-- 0.98 * tbl_cams_uploaddetails.amount < #SuccessIBANKDD.amount and
(0.98 * tbl_cams_uploaddetails.amount <= #SuccessIBANKDD.amount
and tbl_cams_uploaddetails.amount >= #SuccessIBANKDD.amount) and

tbl_cams_uploaddetails.cams_schemegroup=#SuccessIBANKDD.cams_schemegroup and
tbl_cams_uploaddetails.payin_slip_no=#SuccessIBANKDD.payin_slip_no and
--tbl_cams_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cams_uploaddetails.Payment_Mechanism in ('D','P')

--
drop table #SuccessIBANKDD

end
IF @.DataFormat_Name ='HDFC'
Begin

proper query for updating success record in purchase file hdfc bank
select instrm_no
--,tbl_cams_uploadDetails.instrm_date
,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessHDFC from tbl_cams_uploaddetails WITH(NOLOCK)
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount AND
--tbl_cams_uploadDetails.instrm_date=tbl_cms_uploadDetails.instrm_date And --'Added Gopal feb 07
tbl_cams_uploaddetails.cams_schemegroup=tbl_cms_uploadDetails.cms_schemegroup --'Added Gopal feb 07
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending'and Format_ID=83 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploadDetails.additionalfield13 and
Payment_Mechanism<>'EC'and Payment_Mechanism<>'M' and Payment_Mechanism <> 'TR'
group by instrm_no,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
--,tbl_cams_uploadDetails.instrm_date
having count(instrm_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFC
where tbl_cams_uploaddetails.instrm_no=#SuccessHDFC.instrm_no and
tbl_cams_uploaddetails.amount=#SuccessHDFC.amount and
--tbl_cams_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cams_uploaddetails.cams_schemegroup=#SuccessHDFC.cams_schemegroup and
--tbl_cams_uploaddetails.payin_slip_no=#SuccessHDFC.payin_slip_no and
tbl_cams_uploaddetails.compare_status='Pending'

proper query for updating success record in bank file hdfc bank

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFC
where tbl_cms_uploaddetails.cheque_no=#SuccessHDFC.instrm_no and
tbl_cms_uploaddetails.amount=#SuccessHDFC.amount and
tbl_cms_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cms_uploaddetails.cms_schemegroup=#SuccessHDFC.cams_schemegroup and --added 26-Feb- 07 by gopal
tbl_cms_uploaddetails.additionalfield13=#SuccessHDFC.payin_slip_no and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=83

drop table #SuccessHDFC

--DD for HDFC
--For DD charges for HDFC
select instrm_no
--,tbl_cams_uploadDetails.instrm_date
,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessHDFCDD from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount AND
--tbl_cams_uploadDetails.instrm_date=tbl_cms_uploadDetails.instrm_date And --'Added Gopal feb 07
tbl_cams_uploaddetails.cams_schemegroup=tbl_cms_uploadDetails.cms_schemegroup --'Added Gopal feb 07
where tbl_cams_uploaddetails.compare_status='Pending' and
(0.98 * tbl_cams_uploaddetails.amount <= tbl_cms_uploaddetails.amount
and tbl_cams_uploaddetails.amount >= tbl_cms_uploaddetails.amount)
and tbl_cms_uploaddetails.compare_status='Pending'and Format_ID=83 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploadDetails.additionalfield13 and
Payment_Mechanism in ('D','P')
group by instrm_no,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
--,tbl_cams_uploadDetails.instrm_date
having count(instrm_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCDD
where tbl_cams_uploaddetails.instrm_no=#SuccessHDFCDD.instrm_no and
--0.98 * tbl_cams_uploaddetails.amount = #SuccessHDFCDD.amount or 0.98 * tbl_cams_uploaddetails.amount < #SuccessHDFCDD.amount or tbl_cams_uploaddetails.amount = #SuccessHDFCDD.amount and
(0.98 * tbl_cams_uploaddetails.amount <= #SuccessHDFCDD.amount
and tbl_cams_uploaddetails.amount >= #SuccessHDFCDD.amount) and

--tbl_cams_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cams_uploaddetails.cams_schemegroup=#SuccessHDFCDD.cams_schemegroup and
--tbl_cams_uploaddetails.payin_slip_no=#SuccessHDFCDD.payin_slip_no and
tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism in ('D','P')

--Added on 21 mar
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCDD
where tbl_cms_uploaddetails.cheque_no=#SuccessHDFCDD.instrm_no and
--tbl_cms_uploaddetails.amount=#SuccessHDFC.amount and
(0.98 * #SuccessHDFCDD.amount <= tbl_cms_uploaddetails.amount
and #SuccessHDFCDD.amount >= tbl_cms_uploaddetails.amount) and
tbl_cms_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cms_uploaddetails.cms_schemegroup=#SuccessHDFCDD.cams_schemegroup and --added 26-Feb- 07 by gopal
tbl_cms_uploaddetails.additionalfield13=#SuccessHDFCDD.payin_slip_no and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=83

drop table #SuccessHDFCDD

--
end

IF @.DataFormat_Name ='RTGS'
Begin

proper query for updating success record in purchase file RTGS
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup,tbl_cams_uploaddetails.trade_date into #SuccessRTGS from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
cams_schemegroup=cms_schemegroup and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=99
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup,trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessRTGS
where tbl_cams_uploaddetails.folio_no=#SuccessRTGS.folio_no and
tbl_cams_uploaddetails.amount=#SuccessRTGS.amount and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessRTGS.cams_schemegroup and
tbl_cams_uploaddetails.trade_date=#SuccessRTGS.trade_date and
tbl_cams_uploaddetails.compare_status='Pending'

proper query for updating success record in bank file RTGS

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessRTGS
where tbl_cms_uploaddetails.folio_no=#SuccessRTGS.folio_no and
tbl_cms_uploaddetails.amount=#SuccessRTGS.amount and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessRTGS.cams_schemegroup and
tbl_cms_uploaddetails.tradedate=#SuccessRTGS.trade_date and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=99

drop table #SuccessRTGS

end

IF @.DataFormat_Name='CHANNEL'
Begin

proper query for updating success record in purchase file Channel

select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Folio_No,--tbl_cms_uploadDetails.Folio_No Folio_No1,
--Nachiket
--tbl_cams_uploaddetails.name
tbl_cams_uploaddetails.user_trxnno
into #SuccessChannel from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.cams_schemegroup from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on

tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount --and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate
--Nachiket

and tbl_cams_uploaddetails.user_trxnno=tbl_cms_uploaddetails.additionalfield2
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=98

group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.Scheme_Code,
tbl_cams_uploadDetails.Folio_No,
--,tbl_cms_uploadDetails.Folio_No--Nachiket
--,tbl_cams_uploadDetails.name
tbl_cams_uploaddetails.user_trxnno
having count(tbl_cams_uploaddetails.trade_date) =1

--select * from #SuccessChannelG
-- drop table #SuccessChannelG

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessChannel
where -tbl_cams_uploaddetails.trade_date=#SuccessChannel.trade_date and by Nachiket on 15th March as per CR
tbl_cams_uploaddetails.amount=#SuccessChannel.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessChannel.Scheme_Code and
tbl_cams_uploaddetails.compare_status='Pending'

and tbl_cams_uploaddetails.user_trxnno=#SuccessChannel.user_trxnno

proper query for updating success record in bank file Channel

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessChannel
where -tbl_cms_uploaddetails.tradedate=#SuccessChannel.trade_date and by Nachiket on 15th March as per CR
tbl_cms_uploaddetails.amount=#SuccessChannel.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessChannel.Scheme_Code and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=98

and tbl_cms_uploaddetails.additionalfield2=#SuccessChannel.user_trxnno
--

drop table #SuccessChannel
End

IF @.DataFormat_Name ='IBANK SUCCESS' OR @.DataFormat_Name ='IBANK FAILURE'
Begin

proper query for updating success record in purchase file IBank Success
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism into #SuccessIBankSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no AND
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate --ADDED GOPAL 07 FEB
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (85,86) and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success' ,ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBankSIP
where tbl_cams_uploaddetails.folio_no=#SuccessIBankSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessIBankSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessIBankSIP.Scheme_Code and
tbl_cams_uploaddetails.trade_date=#SuccessIBankSIP.trade_date and--ADDED GOPAL 07 NEW CR
tbl_cams_uploaddetails.compare_status='Pending' and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'

proper query for updating success record in bank file IBank Success

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBankSIP
where tbl_cms_uploaddetails.folio_no=#SuccessIBankSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessIBankSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessIBankSIP.Scheme_Code and
tbl_cms_uploaddetails.tradedate=#SuccessIBankSIP.trade_date and --ADDED GOPAL 07 NEW CR tbl_cms_uploaddetails.compare_status='Pending'
#SuccessIBankSIP.Payment_Mechanism='M'
drop table #SuccessIBankSIP

end

IF @.DataFormat_Name ='BJ SUCCESS' OR @.DataFormat_Name ='BJ FAILURE'
Begin

proper query for updating success record in purchase file BJ Success
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate --GOPAL 07 CR
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89) and Tbl_CAMS_UploadDetails.Payment_Mechanism='EC'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessBJSIP
where tbl_cams_uploaddetails.folio_no=#SuccessBJSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessBJSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessBJSIP.Scheme_Code and
tbl_cams_uploaddetails.trade_date=#SuccessBJSIP.trade_date and--GOPAL 07 CR
tbl_cams_uploaddetails.compare_status='Pending' and tbl_cams_uploaddetails.Payment_Mechanism='EC'

proper query for updating success record in bank file BJ Success

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessBJSIP
where tbl_cms_uploaddetails.folio_no=#SuccessBJSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessBJSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessBJSIP.Scheme_Code and
tbl_cms_uploaddetails.tradedate=#SuccessBJSIP.trade_date and--GOPAL 07 CR
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89) and #SuccessBJSIP.Payment_Mechanism='EC'

drop table #SuccessBJSIP
end

IF @.DataFormat_Name ='HDFC SUCCESS' OR @.DataFormat_Name ='HDFC FAILURE'
Begin

proper query for updating success record in purchase file HDFC SUCCESS
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Trade_date
--Nachiket
,tbl_cams_uploaddetails.name,tbl_cams_uploaddetails.Payment_Mechanism
into #SuccessHDFCSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploadDetails.Trade_date=tbl_cms_uploadDetails.Tradedate --GOPAL 07 CR
Nachiket
--and tbl_cams_uploadDetails.name=tbl_cms_uploadDetails.additionalfield2
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (90,91) and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Trade_date
--Nachiket
,tbl_cams_uploaddetails.name,tbl_cams_uploaddetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success' ,
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCSIP
where tbl_cams_uploaddetails.folio_no=#SuccessHDFCSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessHDFCSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessHDFCSIP.Scheme_Code and
tbl_cams_uploadDetails.Trade_date=#SuccessHDFCSIP.Trade_date and --GOPAL 07 CR
tbl_cams_uploaddetails.compare_status='Pending' and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
--Nachiket
--and tbl_cams_uploaddetails.name=#SuccessHDFCSIP.name

proper query for updating success record in bank file HDFC SUCCESS

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)
from #SuccessHDFCSIP
where tbl_cms_uploaddetails.folio_no=#SuccessHDFCSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessHDFCSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessHDFCSIP.Scheme_Code and
tbl_cms_uploadDetails.Tradedate=#SuccessHDFCSIP.Trade_date and --GOPAL 07 CR
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (90,91) and #SuccessHDFCSIP.Payment_Mechanism='M'
--Nachiket
--and tbl_cms_uploaddetails.additionalfield2=#SuccessHDFCSIP.name

drop table #SuccessHDFCSIP
end

FETCH NEXT FROM CUR_DATAFORMAT INTO @.DataFormat_ID,@.DataFormat_Name

END
CLOSE CUR_DATAFORMAT
DEALLOCATE CUR_DATAFORMAT
--FORMAT END

SELECT top 30 CAMS.Name,CAMS.Scheme_Code,
CAMS.Trade_Date,CAMS.Posted_Date, CAMS.Amount, CAMS.Instrm_No, CAMS.Instrm_Date,
CAMS.Bank
from Tbl_CAMS_UploadDetails CAMS WITH(NOLOCK)
where CaMS.Compare_Status = 'Success' and ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)

SELECT top 30 CAMS.Name,CAMS.Scheme_Code,
CAMS.Trade_Date,CAMS.Posted_Date, CAMS.Amount, CAMS.Instrm_No, CAMS.Instrm_Date,
CAMS.Bank
from Tbl_CAMS_UploadDetails CAMS WITH(NOLOCK) where CaMS.Compare_Status = 'Pending'

END

i know its very vast procedure....not written by me..so i want to optimize it........

any idea?

thanx a lot