optimizing a query to delete duplicates

I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:

+----+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 123 |
+--+--+

As you can see, the data is tabular. This is how it is stored in the table:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 234 |
+--+----+----+

What I need is to delete all records having the same "Row" when there exists
the same set of records with a different (smaller, to be precise) "Row".
Using the example above, what I need to get is:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
+--+----+----+

A slow way of doing this seem to be:

DELETE FROM X
WHERE Row IN
(SELECT DISTINCT Row FROM X x1
WHERE EXISTS
(SELECT * FROM X x2
WHERE x2.Row < x1.Row
AND NOT EXISTS
(SELECT * FROM X x3
WHERE x3.Row = x2.Row
AND x3.FieldName = x2.FieldName
AND x3.FieldValue <> x1.FieldValue)))

Can this be done faster, better, and cheaper?my knee-jerk reaction is:

Why is it important to optimize it? I think you should delete the
duplicates, then create a constraint that prevents them from recurring.

If, for some reason, you are unable to fix the application that creates
these duplicates, and creating a constraint causes errors in the application
that you can't tolerate, then I suppose an alternative would be to create a
trigger that deletes them upon entry. Having a composite index on the
columns that are being duplicated would enable such a trigger to run
quickly.

But looking at your query, I find it strangely complex.

Why not just:

DELETE FROM X
WHERE EXISTS (SELECT * FROM X x2
WHERE x2.Row < x.Row
AND X.FieldName = x2.FieldName
AND X.FieldValue = x2.FieldValue)

Am I missing something? Your NOT EXISTS has me a bit confused... I think it
might delete data in situations other than described.

Also, NOT EXISTS is generally slow.|||On 2004-07-15, Aaron W. West <tallpeak@.hotmail.NO.SPAM> wrote:
> Why is it important to optimize it? I think you should delete the
> duplicates, then create a constraint that prevents them from recurring.

Such constraint may not be created. This table is a temporary table, where
data from an input file is loaded. Duplicate sets of records must be
deleted because the data then goes into permanent tables. Those table have
constraints against duplicates.

> But looking at your query, I find it strangely complex.

Me too. I'm trying to improve it. Its complexity seems to hinder its
performance.

> Why not just:
> DELETE FROM X
> WHERE EXISTS (SELECT * FROM X x2
> WHERE x2.Row < x.Row
> AND X.FieldName = x2.FieldName
> AND X.FieldValue = x2.FieldValue)

This would delete records that should not be deleted. Here's an example:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 666 |
+--+----+----+

Here the combination of values for "a" and "b" on every "Row" is
different. There are no duplicates here. The query that you proposed would
delete the second to last row

+--+----+----+
| 3 | a | 123 |
+--+----+----+

because it has the same FieldName and FieldValue as the first row.

Think of it the data this way:

+--+--+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 666 |
+--+--+

No duplicate rows here.|||Hi

You could try only selecting the correct data when you move it into the
permanent tables. But the following may work better:

DELETE FROM X1
FROM X X1 JOIN X X2
ON x2.Row < x1.Row
AND x1.Fieldvalue = x2.Fieldvalue
AND x1.FieldName = x2.FieldName

John

"Alexander Anderson" <no@.spam.com> wrote in message
news:slrncfe0ft.mk1.alex@.Toronto-HSE-ppp3682122.sympatico.ca...
> I have a DELETE statement that deletes duplicate data from a table. It
> takes a long time to execute, so I thought I'd seek advice here. The
> structure of the table is little funny. The following is NOT the table,
> but the representation of the data in the table:
> +----+
> | a | b |
> +--+--+
> | 123 | 234 |
> | 345 | 456 |
> | 123 | 123 |
> +--+--+
> As you can see, the data is tabular. This is how it is stored in the
table:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> | 3 | a | 123 |
> | 3 | b | 234 |
> +--+----+----+
> What I need is to delete all records having the same "Row" when there
exists
> the same set of records with a different (smaller, to be precise) "Row".
> Using the example above, what I need to get is:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> +--+----+----+
> A slow way of doing this seem to be:
> DELETE FROM X
> WHERE Row IN
> (SELECT DISTINCT Row FROM X x1
> WHERE EXISTS
> (SELECT * FROM X x2
> WHERE x2.Row < x1.Row
> AND NOT EXISTS
> (SELECT * FROM X x3
> WHERE x3.Row = x2.Row
> AND x3.FieldName = x2.FieldName
> AND x3.FieldValue <> x1.FieldValue)))
> Can this be done faster, better, and cheaper?

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 Query

Listed below is tsql statement that I set up execute every 60 minutes.
To check to see if all the values exist in the following list: '806478',
'806479','806480','806481' in the Call_Movements table.
I am only interest in the first six characters and I'm converting iNum data
type from money to varchar(20).
left(cast(iNum as varchar(20)),6)
Please help me optimize the t-sql statement listed below since this table is
large.
Is there away to change the following to binary format:
DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
listed below to be optimal?
Thank You,
T-SQL
DECLARE @.CNT_MTN_REC_0 SMALLINT
SET @.CNT_MTN_REC_0 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806478'))
--
-- Count the
--
DECLARE @.CNT_MTN_REC_1 SMALLINT
SET @.CNT_MTN_REC_1 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806479'))
--
--
--
DECLARE @.CNT_MTN_REC_2 SMALLINT
SET @.CNT_MTN_REC_2 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806480'))
--
--
--
DECLARE @.CNT_MTN_REC_3 SMALLINT
SET @.CNT_MTN_REC_3 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806481'))
--
--
--
if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
(@.CNT_MTN_REC_3 = 0)
BEGIN
PRINT "Error has Occurred'
ENDJoe K. (Joe K.@.discussions.microsoft.com) writes:
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum
> data type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this
> table is large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
It would have helped if you had posted the CREATE TABLE and CREATE INDEX
statements for the tables.
But first, there is no need to run four SQL statemennts.
This could either be done as:
Select count(iNum), left(cast(iNum as varchar(20)),6)
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
GROUP BY left(cast(iNum as varchar(20)),6)
This produces a result set of four rows. If you need to get the result
into variables, you can do:
Select @.CNT_MTN_REC_0 = SUM(CASE left(cast(iNum as varchar(20)),6)
WHEN '806478' THEN 1
ELSE 0
END),
..
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
As you can see, I have also changed the condition on Start_Time, in case
this column is indexed. When an indexed column appears in an expression
like in your query, the index is of on use. The query is still problematic
due to the >=. If the index on StartTime is clustered it is not much of
an issue, but if there is only a onn-clustered index, the optimizer is not
likely to pick it in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Joe,
Will you have iNum values that start with 806478 (or any of the other 3
values) followed by other numbers before the decimal point? For example
8064781.01?
If not, then make sure you add a nonclustered index on
Call_Movement(iNum, StartTime) and add the following predicates to the
WHERE clause:
AND iNum >= 806478
AND iNum < 806482
You could try the following query:
IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
FROM Call_Movements
WHERE StartTime >= DATEADD(minute, -60, CURRENT_TIMESTAMP)
AND iNum >= CAST(806478 AS money)
AND iNum < CAST(806482 AS money)
AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
'806481')
) < 4
BEGIN
PRINT "Error has Occurred'
END
HTH,
Gert-Jan
Joe K. wrote:
> Listed below is tsql statement that I set up execute every 60 minutes.
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum dat
a
> type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this table
is
> large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
> Thank You,
>
> T-SQL
> DECLARE @.CNT_MTN_REC_0 SMALLINT
> SET @.CNT_MTN_REC_0 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806478'))
> --
> -- Count the
> --
> DECLARE @.CNT_MTN_REC_1 SMALLINT
> SET @.CNT_MTN_REC_1 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806479'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_2 SMALLINT
> SET @.CNT_MTN_REC_2 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806480'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_3 SMALLINT
> SET @.CNT_MTN_REC_3 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806481'))
> --
> --
> --
> if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
> (@.CNT_MTN_REC_3 = 0)
> BEGIN
> PRINT "Error has Occurred'
> END|||And encapsulate LEFT operation to sub-query may be get more good
performance.
Gert-Jan Strik =E5=86=99=E9=81=93=EF=BC=9A
> Joe,
> Will you have iNum values that start with 806478 (or any of the other 3
> values) followed by other numbers before the decimal point? For example
> 8064781.01?
> If not, then make sure you add a nonclustered index on
> Call_Movement(iNum, StartTime) and add the following predicates to the
> WHERE clause:
> AND iNum >=3D 806478
> AND iNum < 806482
> You could try the following query:
> IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
> FROM Call_Movements
> WHERE StartTime >=3D DATEADD(minute, -60, CURRENT_TIMESTAMP)
> AND iNum >=3D CAST(806478 AS money)
> AND iNum < CAST(806482 AS money)
> AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
> '806481')
> ) < 4
> BEGIN
> PRINT "Error has Occurred'
> END
>
> HTH,
> Gert-Jan
>
> Joe K. wrote:
data
le is
=3D 0) OR|||"navyzhu@.gmail.com" wrote:
> And encapsulate LEFT operation to sub-query may be get more good
> performance.
I have not done performance tests to disprove it, but I highly doubt it!
I don't think LEFT will outperform CAST.
Besides, why use the proprietary LEFT when the standard CAST will do
just fine...
Gert-Jan
> Gert-Jan Strik 写道：
>

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