Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

sql

Pattern Matching - Searching for Numeric or Alpha or Alpha-Numeric characters in a string

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.

Wildcard

Meaning

%

Any string of zero or more characters.

_

Any single character.

[ ]

Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).

Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

It is not quite the same. The LIKE pattern in TSQL will also match values like '0123A' and '1248mkfaliw' whereas the one in Access doesn't. The correct way to specify it is to do below:

WHERE Gift_Date NOT LIKE replicate('[0-9]', 4)

The replicate function just simplifies the repetition of the same pattern multiple times.

sql