Trying not to Write cursor.... Please help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying not to Write cursor.... Please help!

Author

Topic

mr2turbo18
Starting Member

10 Posts

Posted - 2005-07-28 : 15:56:30

Hello,

I'm trying to solve a problem without the use of a cursor.

Here is my current data...

UP_Field1 ST_Field2 FN_Field3 GQ_Field4 CT_Field5 TQ_Field6 TC_Field7 PD_Field8
-------------------- --------- ------------- ------------- ----------- ------------- ----------- -------------
00000001 00409 2.1500 12.0000 1 12.0000 4 25.0000
00000001 00409 2.1500 24.0000 2 48.0000 4 50.0000
00000001 00409 2.1500 36.0000 1 36.0000 4 25.0000
00000002 00371 2.1500 36.0000 1 36.0000 2 50.0000
00000002 00371 2.1500 48.0000 1 48.0000 2 50.0000
00000003 00490 2.3000 12.0000 1 12.0000 4 25.0000
00000003 00490 2.3000 24.0000 1 24.0000 4 25.0000
00000003 00490 2.3000 48.0000 2 96.0000 4 50.0000

I'd like to add a field TN_Field9 that does the following.
- For every distinct Field1, Field2, and Field3.
- Read records in order of Field4 ascending
- When reading the records add up the Field8.
Once Field8 reaches >= 75% then we will calculate for Field9.
- Field9 is calculated by the taking sum(Field6)/sum(Field2)
- This value created will be placed in all of the distinct Field1, Field2 and Field3.

Here is a sample of how I'd like it to look.

UP_Field1 ST_Field2 FN_Field3 GQ_Field4 CT_Field5 TQ_Field6 TC_Field7 PD_Field8 TN_Field9
-------------------- --------- ------------- ------------- ----------- ------------- ----------- ------------- --------------
00000001 00409 2.1500 12.0000 1 12.0000 4 25.0000 20
00000001 00409 2.1500 24.0000 2 48.0000 4 50.0000 20
00000001 00409 2.1500 36.0000 1 36.0000 4 25.0000 20
00000002 00371 2.1500 36.0000 1 36.0000 2 50.0000 42
00000002 00371 2.1500 48.0000 1 48.0000 2 50.0000 42
00000003 00490 2.3000 12.0000 1 12.0000 4 25.0000 33
00000003 00490 2.3000 24.0000 1 24.0000 4 25.0000 33
00000003 00490 2.3000 48.0000 2 96.0000 4 50.0000 33

I can't think of how I can do this w/o writing a cursor which I want to avoid at all costs.

Here is some start up code...

create table dbo.Practice
( UP_Field1 varchar(20),
ST_Field2 varchar(5),
FN_Field3 numeric(11,4),
GQ_Field4 numeric(11,4),
CT_Field5 int,
TQ_Field6 numeric(11,4),
TC_Field7 int,
PD_Field8 numeric(11,4)
)

truncate table dbo.Practice
insert dbo.Practice values ('00000001', '00409', 2.15, 12.00, 1, 12.00, 4, 25.00)
insert dbo.Practice values ('00000001', '00409', 2.15, 24.00, 2, 48.00, 4, 50.00)
insert dbo.Practice values ('00000001', '00409', 2.15, 36.00, 1, 36.00, 4, 25.00)

insert dbo.Practice values ('00000002', '00371', 2.15, 36.00, 1, 36.00, 2, 50.00)
insert dbo.Practice values ('00000002', '00371', 2.15, 48.00, 1, 48.00, 2, 50.00)

insert dbo.Practice values ('00000003', '00490', 2.30, 12.00, 1, 12.00, 4, 25.00)
insert dbo.Practice values ('00000003', '00490', 2.30, 24.00, 1, 24.00, 4, 25.00)
insert dbo.Practice values ('00000003', '00490', 2.30, 48.00, 2, 96.00, 4, 50.00)

-Richard the Newb

jhermiz

3564 Posts

Posted - 2005-07-28 : 15:58:34

Ack you need to seriously normalize this.
Your sums can be done through reporting or a view.
Look at group by and SUM() in BOL.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

mr2turbo18
Starting Member

10 Posts

Posted - 2005-07-28 : 18:40:14

I'm not sure how normalizing this will help? I can normalize it but I will have to pull it back together when I deliver it to my client. They need all the information to write claims.

Even if I normalize it and run sum's, how can I sum 2 rows w/o summing on the third as well(b/c 1st 2 would equal 75 percent, this is in regards to the first case)? I'm failing to understand how I can only pull the rows I need, w/o going row by row and summing the percentages till I hit 75 or greater.

-Richard the Newb

mr2turbo18
Starting Member

10 Posts

Posted - 2005-07-29 : 08:36:57

Rethinking about this, i only need a snippet of code to get the code running....

Say I have the following table....

UP_Field1 ST_Field2 FN_Field3 GQ_Field4 PD_Field5
--------- --------- --------- --------- -------------
00000001 00409 2.1500 12.0000 25.0000
00000001 00409 2.1500 24.0000 50.0000
00000001 00409 2.1500 36.0000 25.0000
00000002 00371 2.1500 36.0000 50.0000
00000002 00371 2.1500 48.0000 50.0000
00000003 00490 2.3000 12.0000 25.0000
00000003 00490 2.3000 24.0000 25.0000
00000003 00490 2.3000 48.0000 50.0000

I need to pull the following records from it.

UP_Field1 ST_Field2 FN_Field3 GQ_Field4 PD_Field5
--------- --------- --------- --------- -------------
00000001 00409 2.1500 12.0000 25.0000
00000001 00409 2.1500 24.0000 50.0000
00000002 00371 2.1500 36.0000 50.0000
00000002 00371 2.1500 48.0000 50.0000
00000003 00490 2.3000 12.0000 25.0000
00000003 00490 2.3000 24.0000 25.0000
00000003 00490 2.3000 48.0000 50.0000

Does this help simplify it to make it easier to understand? I can only think of doing this by ordering the table, stepping through it, and adding up the Field5 until I hit 75 or greater(cursor). Then pull those records marked.

New startup code:

create table dbo.Practice1
( UP_Field1 varchar(20),
ST_Field2 varchar(5),
FN_Field3 numeric(11,4),
GQ_Field4 numeric(11,4),
PD_Field5 numeric(11,4)
)

truncate table dbo.Practice1
insert dbo.Practice1 values ('00000001', '00409', 2.15, 12.00, 25.00)
insert dbo.Practice1 values ('00000001', '00409', 2.15, 24.00, 50.00)
insert dbo.Practice1 values ('00000001', '00409', 2.15, 36.00, 25.00)

insert dbo.Practice1 values ('00000002', '00371', 2.15, 36.00, 50.00)
insert dbo.Practice1 values ('00000002', '00371', 2.15, 48.00, 50.00)

insert dbo.Practice1 values ('00000003', '00490', 2.30, 12.00, 25.00)
insert dbo.Practice1 values ('00000003', '00490', 2.30, 24.00, 25.00)
insert dbo.Practice1 values ('00000003', '00490', 2.30, 48.00, 50.00)

select * from dbo.Practice1

-Richard the Newb

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-29 : 10:21:39

We can help You do that w/o cursor.

But I don't fully understand, why are those in there ?
----------------------------------------------------
I need to pull the following records from it.

UP_Field1 ST_Field2 FN_Field3 GQ_Field4 PD_Field5
--------- --------- --------- --------- -------------
00000001 00409 2.1500 12.0000 25.0000
00000001 00409 2.1500 24.0000 50.0000
00000002 00371 2.1500 36.0000 50.0000
~~00000002 00371 2.1500 48.0000 50.0000~~
00000003 00490 2.3000 12.0000 25.0000
00000003 00490 2.3000 24.0000 25.0000
~~00000003 00490 2.3000 48.0000 50.0000~~

rockmoose

mr2turbo18
Starting Member

10 Posts

Posted - 2005-07-29 : 10:32:50

Because I need to retrieve at least the bottom 75% of the records for any given UPC, store, and price(Field1, 2, 3).

Reading only:
00000002 00371 2.1500 36.0000 50.0000
00000003 00490 2.3000 12.0000 25.0000
00000003 00490 2.3000 24.0000 25.0000
would only give me Quantity values of 50% for a given combination of (Field1, Field2, and Field3).

So I'm required to pull
00000002 00371 2.1500 48.0000 50.0000
00000003 00490 2.3000 48.0000 50.0000
as well to give me >= 75%.

Thanks.

-Richard the Newb

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-30 : 08:01:41

Try:

-- SELECT p1.* FROM dbo.Practice p1 ORDER BY p1.UP_Field1, p1.ST_Field2, p1.GQ_Field4

SELECT	p1.*
FROM	dbo.Practice p1
WHERE
	(	SELECT COALESCE(SUM(p2.PD_Field8),0)
		FROM dbo.Practice p2
		WHERE p1.UP_Field1 = p2.UP_Field1
		AND p1.ST_Field2 = p2.ST_Field2
		AND p1.GQ_Field4 > p2.GQ_Field4 ) < 75.0
ORDER BY
	UP_Field1
	,ST_Field2
	,GQ_Field4

rockmoose

mr2turbo18
Starting Member

10 Posts

Posted - 2005-07-31 : 11:51:49

Wow, that's great. Took me about 15minutes to understand it but I think I get how it works now(how SQL reads through it)! Thanks so much for your help!

-Richard the Newb

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-31 : 11:58:44

You are welcome.
I know, it's not crystal clear

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources