Archive data suggestion

I have a table contains huge rows of data. Performance issue raised. I am
thinking archive some data so that the table will not be that big. The most
convience way is move it to another table. The problem is: will this solve
my performance problem? or I need to move it to another database to reduce
the database size?

Regards,
TrueNoTaking a chunk of your data out of a table will certainly improve
performance on queries against that table, if users have to access data
that contains data from both the old and new tables performance will
suffer. THink of it this way, if users are looking for a needle in a
haystack, decreasing the size of the haystack will decrease the length
of time to find the needle.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||EK9 (a@.a.com) writes:
> I have a table contains huge rows of data. Performance issue raised. I
> am thinking archive some data so that the table will not be that big.
> The most convience way is move it to another table. The problem is: will
> this solve my performance problem? or I need to move it to another
> database to reduce the database size?

Whether you put the archive table in the same or another database
does not affect performance for queries. It could reduce time for
backup and restore though.

However, it is far from certain than archiving data is the best way.
Maybe you need to review which indexes you have on the table.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Appending one datatable to another ?

Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?

You can use UNION ALL to join two tables all records with same structrure( watch for the identity issue). Like

Select col1, col2 From table1

UNION ALL

Select col1, col2 From table2

|||

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();

dt1.Merge(dt2);

This will append dt2 to dt1

Append without UNION

select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
UNION
select * from GEt_lu_Lookup_UNION -- rows 423
I want to get the full 1,817,571 rows without using a Union Statement, can
anyone help?On Tue, 8 Mar 2005 02:35:04 -0800, marcmc wrote:

>select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
>UNION
>select * from GEt_lu_Lookup_UNION -- rows 423
>I want to get the full 1,817,571 rows without using a Union Statement, can
>anyone help?
Hi marcmc,
My first reaction can only be: why' Is there any particular reason why
you want to avoid UNION?
In case you're worried about performance: UNION will attempt to remove
duplicates. If you're sure there are no duplicates (or if you don't want
them removed), use UNION ALL. This should be lots faster, as the step to
remove duplicates is skipped.
In case you have another reason for not wanting to use UNION: here's one
possible way to get the same results without using UNION or UNION ALL:
-- Same as UNION
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON a.Column1 = b.Column1
AND a.Column2 = b.Column2
AND ....
AND a.ColumnN = b.ColumnN
-- Same as UNION ALL
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON 1 = 2
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx Hugo. Beacause I am investigating the use of an indexed view. The new
code gets what I want but then i get...
CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
GO
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
disallowed constructs.
Any ideas...I've read the rules I just can't find why the workaround won't
work.|||Maybe its the full outer join in the design. The GEt_lu_Lookup_ST1 and
GEt_lu_Lookup_UNION are dummy temp tables.
CREATE VIEW GEv_lu_Lookup WITH SCHEMABINDING AS
SELECT COALESCE(a.Ge_lookup_id, b.Ge_lookup_id) AS Ge_lookup_id,
COALESCE(a.Ge_source_id, b.Ge_source_id) AS Ge_source_id,
COALESCE(a.Ge_lookup_code, b.Ge_lookup_code) AS Ge_lookup_code,
COALESCE(a.Ge_lookup_desc, b.Ge_lookup_desc) AS Ge_lookup_desc,
COALESCE(a.Ge_lookup_parent, b.Ge_lookup_parent) AS Ge_lookup_parent
FROM dbo.GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN dbo.GEt_lu_Lookup_UNION AS b
ON a.Ge_lookup_id = b.Ge_lookup_id
AND a.Ge_lookup_code = b.Ge_lookup_code
-- 1817512 rows in Time: 1:29|||On Tue, 8 Mar 2005 04:11:02 -0800, marcmc wrote:

>Thx Hugo. Beacause I am investigating the use of an indexed view. The new
>code gets what I want but then i get...
> CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
>[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
>[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
>GO
>Server: Msg 1936, Level 16, State 1, Line 1
>Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
>disallowed constructs.
>Any ideas...I've read the rules I just can't find why the workaround won't
>work.
Hi marcmc,
Can you post the CREATE VIEW statement used to create the view
GEv_lu_lookup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:

>Maybe its the full outer join in the design.
Hi marcmc,
Yes, that must be the reason. According to Books Online, outer joins are
not permitted in an indexed view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||any other ideas how I can get all the rows and index my view without the use
of UNION/OUTER JOIN or any other rule breakers?
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:
>
> Hi marcmc,
> Yes, that must be the reason. According to Books Online, outer joins are
> not permitted in an indexed view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 06:15:05 -0800, marcmc wrote:

>any other ideas how I can get all the rows and index my view without the us
e
>of UNION/OUTER JOIN or any other rule breakers?
Hi marcmc,
Not without knowing more about your actual business problem than I know
now. Please post the following:
1. Table structure of both tables, posted as CREATE TABLE statement. If
there are many columns, you may omit those that play no role of
importance. Do include all constraints and properties; especially
PRIMARY KEY constraint and UNIQUE constraints are important in this
case. (www.aspfaq.com/5006)
2. Some sample data to illustrate your situation. No need to post all
1.8 million rows, of course - just enoguh to give me a feeling for the
structure of your data. (http://vyaskn.tripod.com/code.htm#inserts)
3. Expected output from the posted sample data. Especially handling of
duplicates should be visible in the expected output.
4. A short but concise description of the actual business problem you're
trying to solve. Don't assume I know your business - I probably don't.
5. Also: what you are trying to accomplish with this indexed view. An
indexed view can never be a goal in itself; it can only be part of a way
to achieve some other goal. If you can elaborate on that goal, I can
help you find other, maybe even better ways to achieve the same goal.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Append Query Problem

I'm testing this query and it does not append any new rows from the
source tables unless the destination table is empty. I want to design
the query just to add new records from the destination tables and leave
existing ones in tact. Help appreciated. Query:

INSERT INTO tMASmembers ( Division, CustomerNumber, CustomerName,
AddressLine1, SalesPersonCode, SIC_Code, SIC_Desc )
SELECT AR1_CustomerMaster.Division, AR1_CustomerMaster.CustomerNumber,
AR1_CustomerMaster.CustomerName, AR1_CustomerMaster.AddressLine1,
AR1_CustomerMaster.SalesPersonCode, AR_90_UDF_AR_Customer.Sic_Code,
AR_90_UDF_AR_Customer.Sic_Desc
FROM AR1_CustomerMaster INNER JOIN AR_90_UDF_AR_Customer ON
(AR1_CustomerMaster.Division = AR_90_UDF_AR_Customer.Division) AND
(AR1_CustomerMaster.CustomerNumber =
AR_90_UDF_AR_Customer.CustomerNumber)
WHERE (((AR1_CustomerMaster.Division) Not In (Select
[tMASmembers].[Division] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerNumber) Not In (Select
[tMASmembers].[CustomerNumber] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerName) Not In (Select
[tMASmembers].[CustomerName] From [tMASmembers])) AND
((AR1_CustomerMaster.AddressLine1) Not In (Select
[tMASmembers].[AddressLine1] From [tMASmembers])) AND
((AR1_CustomerMaster.SalesPersonCode) Not In (Select
[tMASmembers].[SalesPersonCode] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Code) Not In (Select
[tMASmembers].[SIC_Code] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Desc) Not In (Select
[tMASmembers].[SIC_Desc] From [tMASmembers])));

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!One more note: In a test I did, it seems I can only append when I delete
the records from the destination table that all belong to an entire
division (01 or 02). So if I delete all the customers from div 01, I can
append them back. The division and customer number fields are the
primary keys and they come in as text from the ODBC import.

Help appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Frank Py" <fpy@.proactnet.com> wrote in message
news:3ffd9cd7$0$70302$75868355@.news.frii.net...
> One more note: In a test I did, it seems I can only append when I delete
> the records from the destination table that all belong to an entire
> division (01 or 02). So if I delete all the customers from div 01, I can
> append them back. The division and customer number fields are the
> primary keys and they come in as text from the ODBC import.
> Help appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You probably want something like this:

insert into tMASmembers (...)
select (...)
from
AR1_CustomerMaster CM inner join AR_90_UDF_AR_Customer C
on CM.Division = C.Division and CM.CustomerNumber = C.CustomerNumber
where not exists
(select * from tMASmembers tm
where tm.Division = CM.Division and
tm.CustomerNumber = CM.CustomerNumber)

Simon|||Thanks for the example.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Anyone work with Formula for Row?

I see the "lame" formula there in the editor for visibility.
=Fields!routing_Number.Value That is the rows identifier. I want to
compare the
Fields!NumRows.Value for the two Fields!status.Value.
Status could be "Cleared" or "Returned".
In Pseudo code
( Sum(Fields!NumRows[0].Value<Sum(Fields!NumRows[1].Value))
or maybe ?
=( Sum(Fields!NumRows[Fields!status.Value
="Cleared"].Value<Sum(Fields!NumRows[Fields!status.Value="Returned].Value))
Both fail?
This is an atempt to only show rows of report that have Returned >
Collected.
any ideas?
TIAHere is how I set the background color as expression:
=IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
"White")
Modify the above sample expression for your own needs.
Regards,
Roman Kiss Jr.
"_Stephen" wrote:
> I see the "lame" formula there in the editor for visibility.
> =Fields!routing_Number.Value That is the rows identifier. I want to
> compare the
> Fields!NumRows.Value for the two Fields!status.Value.
> Status could be "Cleared" or "Returned".
>
> In Pseudo code
> ( Sum(Fields!NumRows[0].Value<Sum(Fields!NumRows[1].Value))
> or maybe ?
> =( Sum(Fields!NumRows[Fields!status.Value
> ="Cleared"].Value<Sum(Fields!NumRows[Fields!status.Value="Returned].Value))
> Both fail?
> This is an atempt to only show rows of report that have Returned >
> Collected.
> any ideas?
>
> TIA
>
>
>
>
>
>|||"Roman JR." <rkiss_at_opentext_dot_com> wrote in message
news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> Here is how I set the background color as expression:
> =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
> "White")
I see that your comparing two separate columns in your data. Mine is
normalized, sample here:
NumRows RoutingNumber Status
60 011000138 CLEARED
36 011000138 RETURNED
13 011301798 RETURNED
13 011400071 CLEARED
23 011400495 CLEARED
12 011400495 RETURNED
In this case I don't want to print the row in the Matrix for 011000138
because 36 < 60. I want to print the row for 011301798 because they are all
returned. I want to skip the other rows.
Do you know how the data is held in the object so I can attempt to enumerate
between them?
TIA
__Stephen|||Hi Stephen,
Can't you just limit your resultset in your SQL data source?
I would create my report logic with the SQL statement.
(sample sql)
SELECT
sql1.returned_records,
sql1.RoutingNumber,
'Returned' as Status
FROM
(
SELECT
RoutingNumber,
MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
END) AS cleared_records,
MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
END) AS returned_records,
MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
END) - MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier)
ELSE 0 END) as returned_vs_cleared
FROM some_table
GROUP BY RoutingNumber
)sql1
WHERE sql1.returned_vs_cleared > 0
Regards,
Roman
"_Stephen" wrote:
> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> >
> > Here is how I set the background color as expression:
> >
> > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
> > "White")
> I see that your comparing two separate columns in your data. Mine is
> normalized, sample here:
> NumRows RoutingNumber Status
> 60 011000138 CLEARED
> 36 011000138 RETURNED
> 13 011301798 RETURNED
> 13 011400071 CLEARED
> 23 011400495 CLEARED
> 12 011400495 RETURNED
> In this case I don't want to print the row in the Matrix for 011000138
> because 36 < 60. I want to print the row for 011301798 because they are all
> returned. I want to skip the other rows.
> Do you know how the data is held in the object so I can attempt to enumerate
> between them?
> TIA
> __Stephen
>
>|||Or you can simply look for visibility under properties of the report, click
the plus sign that will expand the VISIBILITY properties, select expression
for HIDDEN and copy/paste the following expression:
=Fields!status.Value="Cleared"
You must apply this expression to every row in your report, this way only
RETURNED will show up.
Roman
"Roman JR." wrote:
> Hi Stephen,
> Can't you just limit your resultset in your SQL data source?
> I would create my report logic with the SQL statement.
> (sample sql)
> SELECT
> sql1.returned_records,
> sql1.RoutingNumber,
> 'Returned' as Status
> FROM
> (
> SELECT
> RoutingNumber,
> MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
> END) AS cleared_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
> END) AS returned_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
> END) - MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier)
> ELSE 0 END) as returned_vs_cleared
> FROM some_table
> GROUP BY RoutingNumber
> )sql1
> WHERE sql1.returned_vs_cleared > 0
>
> Regards,
> Roman
> "_Stephen" wrote:
> >
> > "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> > news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> > >
> > > Here is how I set the background color as expression:
> > >
> > > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
> > > "White")
> >
> > I see that your comparing two separate columns in your data. Mine is
> > normalized, sample here:
> > NumRows RoutingNumber Status
> > 60 011000138 CLEARED
> > 36 011000138 RETURNED
> > 13 011301798 RETURNED
> > 13 011400071 CLEARED
> > 23 011400495 CLEARED
> > 12 011400495 RETURNED
> >
> > In this case I don't want to print the row in the Matrix for 011000138
> > because 36 < 60. I want to print the row for 011301798 because they are all
> > returned. I want to skip the other rows.
> >
> > Do you know how the data is held in the object so I can attempt to enumerate
> > between them?
> >
> > TIA
> >
> > __Stephen
> >
> >
> >
> >|||Roman thanks. I think your missing the ease of the Matrix report, where
your presenting normalized data to the report engine and it does it's layout
voodoo keeping totals to the grouping conditions that you set.
That being said, my display for the DATA section is probably an array '
because this is the display formula in the textbox:
=Sum(Fields!NumRows.Value)
So the engine iterates through a distinct call for the column Fields!status.
It sees 2 Cleared and Returned.
I am surprised that the intellisence isn't picking this up, that the data is
an array.
I have been doing cross tab reports for 10+ years and don't ever remember
having to limit the rows in the report like this. Normally I'd do it in the
data collection phase.
If you have any other ideas drop me a line.
"Roman JR." <rkiss_at_opentext_dot_com> wrote in message
news:4F9367C4-0971-4947-B488-4592F4E7756D@.microsoft.com...
> Or you can simply look for visibility under properties of the report,
> click
> the plus sign that will expand the VISIBILITY properties, select
> expression
> for HIDDEN and copy/paste the following expression:
> =Fields!status.Value="Cleared"
> You must apply this expression to every row in your report, this way only
> RETURNED will show up.
> Roman
>
> "Roman JR." wrote:
>> Hi Stephen,
>> Can't you just limit your resultset in your SQL data source?
>> I would create my report logic with the SQL statement.
>> (sample sql)
>> SELECT
>> sql1.returned_records,
>> sql1.RoutingNumber,
>> 'Returned' as Status
>> FROM
>> (
>> SELECT
>> RoutingNumber,
>> MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE
>> 0
>> END) AS cleared_records,
>> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
>> 0
>> END) AS returned_records,
>> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
>> 0
>> END) - MAX(CASE WHEN status = 'Cleared' THEN
>> count(some_unique_identifier)
>> ELSE 0 END) as returned_vs_cleared
>> FROM some_table
>> GROUP BY RoutingNumber
>> )sql1
>> WHERE sql1.returned_vs_cleared > 0
>>
>> Regards,
>> Roman
>> "_Stephen" wrote:
>> >
>> > "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
>> > news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
>> > >
>> > > Here is how I set the background color as expression:
>> > >
>> > > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value,
>> > > "Red",
>> > > "White")
>> >
>> > I see that your comparing two separate columns in your data. Mine is
>> > normalized, sample here:
>> > NumRows RoutingNumber Status
>> > 60 011000138 CLEARED
>> > 36 011000138 RETURNED
>> > 13 011301798 RETURNED
>> > 13 011400071 CLEARED
>> > 23 011400495 CLEARED
>> > 12 011400495 RETURNED
>> >
>> > In this case I don't want to print the row in the Matrix for 011000138
>> > because 36 < 60. I want to print the row for 011301798 because they
>> > are all
>> > returned. I want to skip the other rows.
>> >
>> > Do you know how the data is held in the object so I can attempt to
>> > enumerate
>> > between them?
>> >
>> > TIA
>> >
>> > __Stephen
>> >
>> >
>> >
>> >|||You were right Roman I had to get the data straight in a new SP. Then take
it to a non matrix report.
"Roman JR." <rkiss_at_opentext_dot_com> wrote in message
news:A7457C10-5776-400D-BF22-24777A9EB523@.microsoft.com...
> Hi Stephen,
> Can't you just limit your resultset in your SQL data source?
> I would create my report logic with the SQL statement.
> (sample sql)
> SELECT
> sql1.returned_records,
> sql1.RoutingNumber,
> 'Returned' as Status
> FROM
> (
> SELECT
> RoutingNumber,
> MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
> END) AS cleared_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> 0
> END) AS returned_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> 0
> END) - MAX(CASE WHEN status = 'Cleared' THEN
> count(some_unique_identifier)
> ELSE 0 END) as returned_vs_cleared
> FROM some_table
> GROUP BY RoutingNumber
> )sql1
> WHERE sql1.returned_vs_cleared > 0
>
> Regards,
> Roman
> "_Stephen" wrote:
>> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
>> news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
>> >
>> > Here is how I set the background color as expression:
>> >
>> > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value,
>> > "Red",
>> > "White")
>> I see that your comparing two separate columns in your data. Mine is
>> normalized, sample here:
>> NumRows RoutingNumber Status
>> 60 011000138 CLEARED
>> 36 011000138 RETURNED
>> 13 011301798 RETURNED
>> 13 011400071 CLEARED
>> 23 011400495 CLEARED
>> 12 011400495 RETURNED
>> In this case I don't want to print the row in the Matrix for 011000138
>> because 36 < 60. I want to print the row for 011301798 because they are
>> all
>> returned. I want to skip the other rows.
>> Do you know how the data is held in the object so I can attempt to
>> enumerate
>> between them?
>> TIA
>> __Stephen
>>
>>|||Hi Stephen,
I am sorry for the delayed response.
Well, it is hard to give the exact solution to a problem without knowing
other report specifications/requirements.
I always try to control my report with SQL statement. I like to think that I
am pretty good in SQL, therefore any complex report requests are ususally
solved within my SQL statement and then easily brought over to Visual Studio
for data output manipulation (conditional expressions, hidden parameters,
etc..)
I am glad that I could help at least a little bit.
Regards,
Roman Kiss Jr.
"_Stephen" wrote:
> You were right Roman I had to get the data straight in a new SP. Then take
> it to a non matrix report.
>
> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> news:A7457C10-5776-400D-BF22-24777A9EB523@.microsoft.com...
> > Hi Stephen,
> >
> > Can't you just limit your resultset in your SQL data source?
> >
> > I would create my report logic with the SQL statement.
> > (sample sql)
> >
> > SELECT
> > sql1.returned_records,
> > sql1.RoutingNumber,
> > 'Returned' as Status
> > FROM
> > (
> > SELECT
> > RoutingNumber,
> > MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
> > END) AS cleared_records,
> > MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> > 0
> > END) AS returned_records,
> > MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> > 0
> > END) - MAX(CASE WHEN status = 'Cleared' THEN
> > count(some_unique_identifier)
> > ELSE 0 END) as returned_vs_cleared
> > FROM some_table
> > GROUP BY RoutingNumber
> > )sql1
> > WHERE sql1.returned_vs_cleared > 0
> >
> >
> > Regards,
> >
> > Roman
> >
> > "_Stephen" wrote:
> >
> >>
> >> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> >> news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> >> >
> >> > Here is how I set the background color as expression:
> >> >
> >> > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value,
> >> > "Red",
> >> > "White")
> >>
> >> I see that your comparing two separate columns in your data. Mine is
> >> normalized, sample here:
> >> NumRows RoutingNumber Status
> >> 60 011000138 CLEARED
> >> 36 011000138 RETURNED
> >> 13 011301798 RETURNED
> >> 13 011400071 CLEARED
> >> 23 011400495 CLEARED
> >> 12 011400495 RETURNED
> >>
> >> In this case I don't want to print the row in the Matrix for 011000138
> >> because 36 < 60. I want to print the row for 011301798 because they are
> >> all
> >> returned. I want to skip the other rows.
> >>
> >> Do you know how the data is held in the object so I can attempt to
> >> enumerate
> >> between them?
> >>
> >> TIA
> >>
> >> __Stephen
> >>
> >>
> >>
> >>
>
>