looping with SQL

Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFOYou don't need to loop. It would have been nice if you supplied exact DDL
and real INSERT statements. Here's an untested solution:
select
o.*
from
MyTable
where
o.id in
(
select top 1
i.id
from
MyTable i
where
i.Company = o.Company
order by
case
when i.Title like '%CEO%' then 1
when i.Title like '%CFO%' then 2
else 3
end
, o.id
)
It's not clear what you mean by the "first" title, so I used id as the
tiebreaker.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Logger" <Logger@.discussions.microsoft.com> wrote in message
news:635B49E6-0714-46FA-997D-B44B4F1F0085@.microsoft.com...
Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFO

Looping vs. Set operation - question

Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran DjuranovicIn the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join o
n IDs, because IDs from StaginTable (table variable in my case) are auto-inc
remented when populated from an XML file, so they don't necesserally match t
he IDs from ProductionTable.
Thanks
Goran
"JT" <someone@.microsoft.com> wrote in message news:ePL0zi8QGHA.3916@.TK2MSFTN
GP11.phx.gbl...
In the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||From your question, it sounded as if you have a situation where you are inse
rting from a staging table into a production table but the problem is that s
pecific rows cause the insert to fail. I was thinking perhaps the situation
is that the new row contains an ID that conflicts with the unique key constr
aint in the production table.
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:uKEm
7QHRGHA.3916@.TK2MSFTNGP11.phx.gbl...
Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join o
n IDs, because IDs from StaginTable (table variable in my case) are auto-inc
remented when populated from an XML file, so they don't necesserally match t
he IDs from ProductionTable.
Thanks
Goran
"JT" <someone@.microsoft.com> wrote in message news:ePL0zi8QGHA.3916@.TK2MSFTN
GP11.phx.gbl...
In the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Goran Djuranovic wrote:
> Hi JT,
> Thanks for your response, but you didn't answer my question.
That's because you didn't supply enough information. You didn't tell us
what "row failed" means. I assume you got an error message, so it would
help to state what the message is. Posting DDL also usually helps.
The point of JT's example is that if the error was a constraint
violation then you can use a query to filter out that row during the
INSERT. You can use a similar query to see which row(s) would have
failed. Since you didn't tell us what the error message is or show us
any data we can't give you the whole solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The short answer is, you have to use a cursor if you want isolate the exact
row where the error occured.
JT supplied you with a process for eliminating the errors, in this case dupi
cate key errors. You can also get null errors, data type conversion errors,
and constraint errors, not to mention truncation of data which can be lost
without generating an error.
Unless you know exactly what data is coming through and can identify the exp
ected errors ahead of time, you will have to use a cursor or have your appli
cation loop therough the records and make individual calls to the database,
which amounts to the same thing (although doing it in the app is more portab
le). There is another alternative, and that is to use a DTS package to load
the data and set the max errors accordingly. When the package is done inse
rting into the table you can use a select similar to what JT provided to ide
ntify the failed rows.
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Have you looked at Savepoints. They are a mechanism to roll back
portions of transactions. You create a savepoint using the SAVE
TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
TRANSACTION savepoint_name statement to roll back to the savepoint
instead of rolling back to the start of the whole transaction.|||Thanks to your responses, guys. I decided to go with the SET operation, and
before doing SET insert I just validate the data (not 100% safe but what is?
:-) ).
Goran Djuranovic
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142043315.953389.314120@.v46g2000cwv.googlegroups.com...
> Have you looked at Savepoints. They are a mechanism to roll back
> portions of transactions. You create a savepoint using the SAVE
> TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
> TRANSACTION savepoint_name statement to roll back to the savepoint
> instead of rolling back to the start of the whole transaction.
>

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.
|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?
Thank you

|||

He's saying that you could either do it in source extract query like this

Code Blockselect

....
,
case

when col1='small' then 's'

when col1='medium' then 'm'

end

from
....

|||

or embeded in the derived column component like this

Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")
|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:
Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.

|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?

You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.

|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?

Thank you

|||

He's saying that you could either do it in source extract query like this

Code Block

select

....

,

case

when col1='small' then 's'

when col1='medium' then 'm'

end

from

....

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.|||

I'll try it out and let you know how it went!

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?

You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.

|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?

Thank you

|||

He's saying that you could either do it in source extract query like this

Code Block

select

....

,

case

when col1='small' then 's'

when col1='medium' then 'm'

end

from

....

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.|||

I'll try it out and let you know how it went!

Lookup Arrays

I need to load an array of order numbers.. Then later I need to be able to
'lookup' in that area for a specific order number (for example one that user
keys into text box).
how is this accomplished? ive researched Array and Arraylist and cannot
figure how without a bunch of work.
There must be something simple for this.
Can you help?
Thanks.
jrHello Jr,
Welcome to the MSDN newsgroup.
From your description, you have an order number list which want to be
stored in a list container and you'll lookup some certain order numbers
from that container later in your SQL Reporting service's report, correct?
Based on my experience, since these code should be in your custom code
period or an separate assembly, so you're using them as custom code or
custom assembly in your report , right? If this is the case, what
available to us are all those utility or collection classes in the .net
framework system.collections namespace(Such as the Array, ArrayList you
mentioned).
For your scenario, I'm wondering how will you identify a certain order
number? Just through the orderID or will you give each order number another
identity(a key value) for lookup? Here are two generic cases:
1. If you just want to get the order item's index in the container(such as
Array) through the order number(suppose its of string type), you can just
Array.IndexOf method to locate a certain item in the Array:
#Array.IndexOf, m¨¦thode (Array, Object)
http://msdn2.microsoft.com/fr-fr/library/7eddebat.aspx
Here is a simple test class which intializing the Array(orderList) in the
type's static initializer and expose a public static method for lookup item
index in the array:
==================public class ArrayUtil
{
public static string[] OrderArray;
public ArrayUtil()
{
}
static ArrayUtil()
{
OrderArray = new string[10];
for (int i = 0; i < OrderArray.Length; i++)
{
OrderArray[i] = "Order_" + i;
}
}
public static int FindOrder(string num)
{
return Array.IndexOf(OrderArray, num);
}
}
=====================
2. If each order nunber will be stored with an identity value as the lookup
key, I think the "HashTable" class is the reasonable choice. HashTable
class support storing multiple key/value pairs and have method for looking
up a certain item in it via its key value:
#Hashtable Class
http://msdn2.microsoft.com/en-us/library/system.collections.hashtable.aspx
Hope this helps. If there is any paricular concerns in your scenario or if
you have any other ideas ,please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Jr,
How are doing on this issue, have you got any progress or does my last
reply helps you a little on this? If you have anything unclear or still
anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Lookup and OLEDB Command components programming

I need some help on SSIS programming. Can anybody give me a short example or tutorial how to write a code, which will show me use of "Lookup" and "OLEDB Command" components?Here's an example of using a Lookup (amongst other things): http://www.sqlis.com/default.aspx?311

The SCD Wizard produces a data-flow with an OLE DB Command in it so you could look at that!

-Jamie|||And how can I get a code from SCD Wizard?|||

Erch wrote:

And how can I get a code from SCD Wizard?

You can't. The SCD Wizard produces components, not code.

So am I correct in saying that you want to build a package programatically that contains a data-flow containing a LOOKUP and an OLE DB COMMAND? There isn't much resources around yet that shows how to do this - BOL is definately the best place to go.

-Jamie

Looking for process Ideas

I'm looking for Ideas on how to handle a Pldege Reminder process. For example; a pledge is made to pay $2400 over the next two years. They will pay $100 per month and each month a reminder will be sent. No real mistery if there is a balance you send a reminder. My problem is how to handle things like what if they want to pay quarterly or annually and how to determine if a payment is really due based on when they paid last, etc... You most likely see what I mean.

If anyone has done this in the past and/or has any ideas and is willing to share I would greatly appreciate any help.

Some stuff that may help you help me better:

tblClient (ClientID)

tblPledge (PledgeID, ClientID, PledegedAmt, PledgeDate,Frequency,NumberYears)

tblPledgePayments (PmtID, PledgeID,PmtAmt,PmtDate)

Using the following definition:

Client (Id, Name, Email)

Pledge (Id, ClientId, PledgedAmt, PledgeDate, Frequency, NumberOfPeriods)

PledgePayments (Id, PledgeId,PmtAmt,PmtDate)

Try

SELECT Name, Email FROM Client WHERE Id IN (

SELECT ClientId FROM Pledge, PledgePayments WHERE Frequency='M' AND PledgePayments.PledgeId = Pledge.Id

AND (SUM(PmtAmt) < (PledgedAmt * (DateDiff(m, GetDate(), PledgeDate) / NumberOfPeriods)))

You will need to look up DateDiff in Books-on-line and check that m is the indicator for months. Also that GetDate() and PledgeDate are the right way around.

HTH

|||Have you done this sort of thing in the past with pledges?|||

No! It is however a fairly simple bit of SQL. You will need to test the query with a number of test cases.

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.
> Jake
>

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake
"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.

> Jake
>

Looking for expression example to force a column to Uppercase

Hello,
I need to force a string column to uppercase. In Powerbuilder there is a
function upper(). Can you tell me the syntax to use in the expression editor
for RS. I can't seem to find an example in theReporting Services Help.
Thanks in advance!
--
JeanDim LowerCase, UpperCase As String
LowerCase = "Hello World 1234" ' String to convert.
UpperCase = UCase(LowerCase) ' Returns "HELLO WORLD 1234".
--
Jeff Lynch
"A BizTalk Enthusiast"
http://dotnetjunkies.com/WebLog/jlynch/
"jrak461" <jrak461@.discussions.microsoft.com> wrote in message
news:806D7B4F-274D-4A3D-93D4-F74B45B4579B@.microsoft.com...
> Hello,
> I need to force a string column to uppercase. In Powerbuilder there is a
> function upper(). Can you tell me the syntax to use in the expression
> editor
> for RS. I can't seem to find an example in theReporting Services Help.
> Thanks in advance!
> --
> Jean|||=Fields!YourField.Value.ToLower
or
=UCase(Fields!YourField.Value)
"jrak461" wrote:
> Hello,
> I need to force a string column to uppercase. In Powerbuilder there is a
> function upper(). Can you tell me the syntax to use in the expression editor
> for RS. I can't seem to find an example in theReporting Services Help.
> Thanks in advance!
> --
> Jean|||Fields!YourField.Value.ToUpper()
To see all the things you can do with strings, look up string methods in the
dotnet framework help.
=-Chris
"jrak461" <jrak461@.discussions.microsoft.com> wrote in message
news:806D7B4F-274D-4A3D-93D4-F74B45B4579B@.microsoft.com...
> Hello,
> I need to force a string column to uppercase. In Powerbuilder there is a
> function upper(). Can you tell me the syntax to use in the expression
editor
> for RS. I can't seem to find an example in theReporting Services Help.
> Thanks in advance!
> --
> Jean

Looking for DTS example

Hi all,
I am trying to automate calling a SQL Copy Data task. I need to provide the
source database dynamically at the least. I am using MSDE in a C3 program.
Does anyone have any examples or sites they can point me to? I am at a loss
after much searching.
ThanksMe too.
Apparently exporting a DTS to VB gives you the option of actually
controlling the DTS batch more, but you need Visual Basic installed (dunno
if it'll run in a VBA environment).
I've tried a DTS storage file & metadata services, the DTS storage file is
in binary and I can't get metadata services working just now...
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"S" <spamaway@.hotmail.com> wrote in message
news:OG5F71TqDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am trying to automate calling a SQL Copy Data task. I need to provide
the
> source database dynamically at the least. I am using MSDE in a C3
program.
> Does anyone have any examples or sites they can point me to? I am at a
loss
> after much searching.
> Thanks
>