Is there a set based solution?

basically I am trying to figure out if there is a set based solution for the following situation.

When we import mainframe data to the sql server the table will look like this

PartNumber release demandQty BalOnHandQty ShortQty
A 1 10 23 0
A 2 5 23 0
A 3 13 23 0
A 4 2 23 0
A 5 12 23 0
B 1 11 14 0
B 2 7 14 0
B 3 20 14 0
B 4 6 14 0



After we run the query the table should look like ie amotize the balonhandqty and update the shortqty.

PartNumber release demandQty BalOnHandQty ShortQty
A 1 10 13 0
A 2 5 8 0
A 3 13 -5 5
A 4 2 -7 2
A 5 12 -19 12
B 1 11 3 0
B 2 7 -4 4
B 3 20 -24 20
B 4 6 -30 6

The only way I can up with a solution is by using a cursor which takes a long time. I was wondering if there is a set based or any other quicker solution.Whoever comes up with the answer deserves to be called a Guru...or a Grand Poobah :)

But out of curiosity, can you give us the formula? If nothing else you'll get a kick out of looking at how people can attempt to resolve this.|||update tbl
set BalOnHandQty = BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) ,
ShortQty = case when BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) < 0 then
BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) * -1 else 0 end
from tbl t1

maybe better is

update tbl
set BalOnHandQty = a.BalOnHandQty ,
ShortQty = case when a.BalOnHandQty < 0 then a.BalOnHandQty * -1 else 0 end
from
tbl t1
join
(select PartNumber, Release, BalOnHandQty = BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release)
from tbl t1) a
on a.PartNumber = t1.PartNumber
and a.Release = t1.Release|||Heck, I don't know that solving this problem is such a complicated task, but I give Kudos to Nigelrivett if he was able to figure out what the problem was based on the sparse information provided! Now THAT takes genious.

There is definitely a career for you in helpdesk support if you ever want it, Nigelrivett! ;)

blindman|||Originally posted by rdjabarov
Whoever comes up with the answer deserves to be called a Guru...or a Grand Poobah :)

But out of curiosity, can you give us the formula? If nothing else you'll get a kick out of looking at how people can attempt to resolve this.

Initially all the records for each partnumber will have the same BalOnHandQty. Then we get start subtracting demandQty from BalOnHandQty for each release for every partnumber. When the BalOnHandQty goes negative we update the shortQty.

I hope everybody can understand the problem!

I'am sorry I should have given more information when I first posted this problem.|||Heck, I don't know that solving this problem is such a complicated task
I didn't see you taking a shot at it!

nigelrivett, - that was quick! I just don't get it why the blind guy thinks you're good only for helpdesk support?|||Bit concerned about the design though.
This updates every balance so that it cannot be run twice.
It would probably be better if the rec with the lowest release (or maybe a new release 0 with the balance and demand qty 0) held the beginning balance and everything worked off that. Then you could run the update multiple times and get the same result.|||nigelrivett thanks for your replies. Iam not that good at set based stuff I am still trying to understand your queries. Once I get back to work I will try to run these queries on some test data and may then I can understand it better.|||rjabberon,

niggelrivett's solution is very simple and concise. That's what makes it good. "Inside every large program there is a small program screaming to get out." From what I've seen of your solutions, you'd do well to take heed.

From what I could see, the challenge in this issue lay in uncovering the requirements. That's pretty common in many I.T. projects, as you'll find out.

And by the way, the word for the day is "sarcasm". It's a light-hearted form of humor. Look it up: www.dictionary.com

blindman|||Originally posted by blindman
rjabberon,


Obviously is recalling a nick name more difficult than understanding the problem. Or was it sarcasm? Anyway, I bed you came up with the solution if niggelrivett (bravo!) wasn't quicker than you!|||Thanks for support, DoktorBlue! The blind guy is obviously not only blind, but also a stuck-up arrogant AH (I looked all this up following your link, bm). And I also like this phrase that I found there more and more (in fact, with every post of yours that is intended to insult me), - BLOW ME!|||Originally posted by blindman
rjabberon,

...From what I could see, the challenge in this issue lay in uncovering the requirements. That's pretty common in many I.T. projects, as you'll find out.
blindman

How many of those "I.T." projects have you participated, and how many have you actually pulled through yourself, and how many of those did you manage not to bring to a complete total disaster?
Originally posted by blindman
And by the way, the word for the day is "sarcasm". It's a light-hearted form of humor. Look it up: www.dictionary.com

blindman

"sarcasm" is not a "light-hearted form of humor". Check your own link...but since you're too busy fixing your "brilliant" solutions, I'll give you the right definition here:

www.dictionary.com (http://dictionary.reference.com/search?q=sarcasm)
A cutting, often ironic remark intended to wound.
A form of wit that is marked by the use of sarcastic language and is intended to make its victim the butt of contempt or ridicule.

Hypothetical (http://dictionary.reference.com/search?q=Hypothetical) - a word for the day for you.|||Originally posted by nigelrivett
Bit concerned about the design though.
This updates every balance so that it cannot be run twice.
It would probably be better if the rec with the lowest release (or maybe a new release 0 with the balance and demand qty 0) held the beginning balance and everything worked off that. Then you could run the update multiple times and get the same result.

Thanks a lot nigelrivett!

Your update query works great and is much quicker than my cursor based solution.

Is there a better way than this?

Hi,
I am adding a row to my table with the code below. It seems to be
inefficient to me as I have to basically build my command with the string
builder. There has to be a better way, no?
What I am trying to do is to search my table for the 2 variables ln1 and
ln2. If they are not found, then one row is added to the table with the
respective values of ln1 and ln2. So for example, if my table contained 100
rows and I perform a search for the values ln1 and ln2 and they are not
found, then one row is added to my table with the values of ln1 and ln2.
Therefore, my table will now have 101 rows.
The code below works, it's just that I think there must be a more efficient
way of doing it.
Another thing that I find that is strange is that when a row is added to my
table, it is added in between rows so that the ascending order is
maintained. Why is that, I as have never asked to sort it or index it.
TIA
Roy
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
namespace testing
{
class Class1
{
// private System.Data.DataSet dataSet;
[STAThread]
static void Main(string[] args)
{
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_2.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True";
int ln1 = 5;
int ln2 = 19;
StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2 (SOBN ,
BN1 ) SELECT ");
strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not exists
(select * from tblSQL_2 where SOBN = ");
strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
SqlConnection oConnection = new SqlConnection(strConnection);
SqlCommand oCommand = new SqlCommand(strCommand.ToString(), oConnection);
oCommand.Connection.Open();
oCommand.ExecuteNonQuery();
oCommand.Connection.Close();
}
}
}I suggest you use a parameterized SQL statement instead of building a SQL
statement string with the literal values. For example:
SqlCommand oCommand = new SqlCommand(
"INSERT INTO tblSQL_2 (SOBN ,BN1 ) " +
" SELECT @.In1, @.In2" +
" WHERE NOT EXISTS(" +
" SELECT *" +
" FROM tblSQL_2" +
" WHERE SOBN = @.In1 AND" +
" BN1 = @.In2)",
oConnection);
oCommand.Parameters.Add("@.In1", In1);
oCommand.Parameters.Add("@.In2", In2);

> Another thing that I find that is strange is that when a row is added to
> my
> table, it is added in between rows so that the ascending order is
> maintained. Why is that, I as have never asked to sort it or index it.
A table in a relational database is an unordered set of rows. Regardless of
insert order or indexing, you must specify ORDER BY to return data in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:RREff.8561$0u1.70303@.weber.videotron.net...
> Hi,
> I am adding a row to my table with the code below. It seems to be
> inefficient to me as I have to basically build my command with the string
> builder. There has to be a better way, no?
> What I am trying to do is to search my table for the 2 variables ln1 and
> ln2. If they are not found, then one row is added to the table with the
> respective values of ln1 and ln2. So for example, if my table contained
> 100
> rows and I perform a search for the values ln1 and ln2 and they are not
> found, then one row is added to my table with the values of ln1 and ln2.
> Therefore, my table will now have 101 rows.
> The code below works, it's just that I think there must be a more
> efficient
> way of doing it.
> Another thing that I find that is strange is that when a row is added to
> my
> table, it is added in between rows so that the ascending order is
> maintained. Why is that, I as have never asked to sort it or index it.
> TIA
> Roy
>
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_2.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True";
> int ln1 = 5;
> int ln2 = 19;
> StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2 (SOBN ,
> BN1 ) SELECT ");
> strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not exists
> (select * from tblSQL_2 where SOBN = ");
> strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
> SqlConnection oConnection = new SqlConnection(strConnection);
> SqlCommand oCommand = new SqlCommand(strCommand.ToString(), oConnection);
> oCommand.Connection.Open();
> oCommand.ExecuteNonQuery();
> oCommand.Connection.Close();
> }
> }
> }
>
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:RREff.8561$0u1.70303@.weber.videotron.net:

> Hi,
> I am adding a row to my table with the code below. It seems to be
> inefficient to me as I have to basically build my command with the
> string builder. There has to be a better way, no?
> What I am trying to do is to search my table for the 2 variables ln1
> and ln2. If they are not found, then one row is added to the table
> with the respective values of ln1 and ln2. So for example, if my table
> contained 100 rows and I perform a search for the values ln1 and ln2
> and they are not found, then one row is added to my table with the
> values of ln1 and ln2. Therefore, my table will now have 101 rows.
> The code below works, it's just that I think there must be a more
> efficient way of doing it.
> Another thing that I find that is strange is that when a row is added
> to my table, it is added in between rows so that the ascending order
> is maintained. Why is that, I as have never asked to sort it or index
> it.
> TIA
> Roy
>
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_
2.mdf;Integrate
> d Security=True;Connect Timeout=30;User Instance=True";
> int ln1 = 5;
> int ln2 = 19;
> StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2
> (SOBN , BN1 ) SELECT ");
> strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not
> exists (select * from tblSQL_2 where SOBN = ");
> strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
> SqlConnection oConnection = new SqlConnection(strConnection);
> SqlCommand oCommand = new SqlCommand(strCommand.ToString(),
> oConnection);
> oCommand.Connection.Open();
> oCommand.ExecuteNonQuery();
> oCommand.Connection.Close();
> }
> }
> }
Why use CLR at all, this can perfectly well be done with pure T-SQL. I
leave handling of NULLs to the reader.
create table demo (
val_a int,
val_b int
)
insert into demo values (1,1)
insert into demo values (1,2)
insert into demo values (2,1)
select count(1) from demo where (val_a = 1) and (val_b = 0)
create procedure prDemo @.a int, @.b int as
begin
declare
@.count int;
select
@.count = count(1)
from
demo
where
(val_a = @.a) and (val_b = @.b);
if @.count = 0
insert into demo values (@.a,@.b)
end;
exec prDemo 1,2
exec prDemo 2,2
select * from Demo
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi Dan,
Your code works great, but I do get some warning that says that this
line:
oCommand.Parameters.Add("@.ln1", ln1);
has been deprecated and to use:
AddWithValue(string ParameterName, Object value);
You said:
A table in a relational database is an unordered set of rows. Regardless
of
insert order or indexing, you must specify ORDER BY to return data in a
particular sequence.
That is what I thought too, but when I add the row it adds it in between
rows and preserving the ascending order. I never asked it to ORDER BY a
particular sequence.
BTW are you not from Montreal? I think that I once bought a computer
from you. Small world.
Thanks
Roy
*** Sent via Developersdex http://www.examnotes.net ***|||> has been deprecated and to use:
> AddWithValue(string ParameterName, Object value);
I agree that you should avoid the deprecated method. I didn't know which
version of .Net you are using.

> BTW are you not from Montreal? I think that I once bought a computer
> from you. Small world.
Not me - never been to Canada. A bit too cold for my blood :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:OO7pB8Q7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi Dan,
> Your code works great, but I do get some warning that says that this
> line:
> oCommand.Parameters.Add("@.ln1", ln1);
> has been deprecated and to use:
> AddWithValue(string ParameterName, Object value);
> You said:
> A table in a relational database is an unordered set of rows. Regardless
> of
> insert order or indexing, you must specify ORDER BY to return data in a
> particular sequence.
> That is what I thought too, but when I add the row it adds it in between
> rows and preserving the ascending order. I never asked it to ORDER BY a
> particular sequence.
> BTW are you not from Montreal? I think that I once bought a computer
> from you. Small world.
> Thanks
> Roy
>
> *** Sent via Developersdex http://www.examnotes.net ***|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:ObF82qQ7FHA.1000@.tk2msftngp13.phx.gbl:

> I suggest you use a parameterized SQL statement instead of building a
> SQL statement string with the literal values. For example:
I agree that parameterized is better in this case, but I do not see the
need of CLR. This appears to me to be a very good example of a case in
which CLR should not be used, but T-SQL.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi Ole,
Sorry for asking but what is T-SQL?
How do I implement this into my program. I am using C# and SQL 2005 Express
edition.
Do I just put this into my code?
Roy

> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971392C765414olekristianbangaas@.
207.46.248.16...
> "Roy Gourgi" <royng@.videotron.ca> wrote in
> news:RREff.8561$0u1.70303@.weber.videotron.net:
>
> 2.mdf;Integrate
> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||One more thing Ole, would this code be faster than the other code?
Thanks
Roy
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971392C765414olekristianbangaas@.
207.46.248.16...
> "Roy Gourgi" <royng@.videotron.ca> wrote in
> news:RREff.8561$0u1.70303@.weber.videotron.net:
>
> 2.mdf;Integrate
> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:FBHff.8901$0u1.153785@.weber.videotron.net:

> Sorry for asking but what is T-SQL?
> How do I implement this into my program. I am using C# and SQL 2005
> Express edition.
> Do I just put this into my code?
T-SQL is Transact SQL, the SQL variant used by SQL Server. By using code
like the one that I wrote on the SQL Server, you can call the stored
procedure multiple times later. If you do not have any experience with SQL,
I would strongly suggest that you learn a bit of SQL before you continue.
So, no you don't put this directly into your code, but make call to the
stored proceudre from your code.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:HDHff.8902$0u1.155354@.weber.videotron.net:

> One more thing Ole, would this code be faster than the other code?
As far as I've understood .NET CLR, it possibly would since the stored
procedure is precompiled. There are no way that the .NET code will be
faster.
The greatest advantage for you as a developer however, is the option to
modify the logic of the stored procedure in only one location.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Is Stored Procedure and 'in' broken in MSDE 2000/SQL 2000 SP4?

This is not the command that im running but demonstrates the problem just
fine, basically 'in' using statored procedures seems to be performing as 'in'
or 'is' where as sending a query direct (identical) only performs 'in' as
expected
Example
Use msdb
Select * From Sysalerts Where database_name in (Select
convert(sysname(128),null))
returns nothing
but put the select command in a stored proceedure and it will return all the
rows with database_name that is null
Am i not seeing/setting something?
Simon
On Tue, 21 Jun 2005 08:36:05 -0700, "Tuner Fich" <Tuner
Fich@.discussions.microsoft.com> wrote:

>This is not the command that im running but demonstrates the problem just
>fine, basically 'in' using statored procedures seems to be performing as 'in'
>or 'is' where as sending a query direct (identical) only performs 'in' as
>expected
>Example
>Use msdb
>Select * From Sysalerts Where database_name in (Select
>convert(sysname(128),null))
>returns nothing
>but put the select command in a stored proceedure and it will return all the
>rows with database_name that is null
>Am i not seeing/setting something?
>Simon
Hi Simon,
I guess that you normally have the setting SET ANSI_NULLS ON (which is
fine, as it makes SQL Server treat NULLS as defined in the ANSI
standard, making your code more portable). However, when creating stored
procedure, someow the setting gets changed to SET ANSI_NULLS OFF (which
is definitely NOT fine, as it makes SQL Server treat NULLS in a
non-standard way that might appear logical at first glance but is not,
and that will make other database programmers fail to understand your
code).
With the ANSI standard ebhaviour for NULLS, logical expressions use
three-valued logic (True, False and Unknown) and all comparisons to NULL
will always return Unknown. The only valid way to copmpare a column or
variable to NULL is to use "WHERE column IS [NOT] NULL".
Also, stop using [NOT] IN with a subselect, as they are a source of
confusion with ANSI standard settings, and they can always be
transformed into a [NOT] EXISTS subquery that usually performs better as
well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)