Help with SQL Query - SQL Server Forums

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

SQL Server Development (2000)

Help with SQL Query

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-19 : 13:50:27

I have a database (tbl_3PrecipProduction) that contains raw material information recorded from or Production systems.

In one case I am trying to retrieve the usages (LBS) for a given raw material (preselected by operator). The generic ingredient is known as Fiber but can have several different types of raw materials. For example:

Raw Material 1 = P11
Raw Material 2 = F19
Raw Material 3 = P33
etc..

Each raw material is reported to SQL along with the weight that was used as shown in the fields below:

Fiber_Mat1_Req (this would be weight)
Fiber_Mat1_Desg (this would be the name (ex. P11))
Fiber_Mat2_Req
Fiber_Mat2_Desg
Fiber_Mat3_Req
Fiber_Mat3_Desg

I need to write a stored procedure that can search the fields, determine which field has the user specified raw material (designation or name), then total the usage ONLY for the associated raw material requirement (LBS) field. The raw materials vary, as do the weights, and can be used in in multiple fields. For example one production run will have P11 in (Fiber_Mat1_Desg) where the next production run will have P11 in (Fiber_Mat2_Desg). The weights (Fiber_Matx_Req) would be recorded accordingly.

Here might be two different examples. Assume the user would be searching for usages on the ingredient P11:

Example 1:
Production run X where 30 batches were made.

Fiber_Mat1_Req
300.2
Fiber_Mat1_Desg
F03

Fiber_Mat2_Req
20.3
Fiber_Mat2_Desg
P11

30 batches made would give 609 LBS of P11.

Example 2:
Production run Y where 55 batches were made.
Fiber_Mat1_Req
56.8
Fiber_Mat1_Desg
P11

Fiber_Mat2_Req
43.1
Fiber_Mat2_Desg
F13

55 batches made would give 3124 LBS of P11.

Ideally I would be able to query the entire table and see that (in this case) a total of 3733 LBS of P11 were used between production run X and Y.
NOTE: X and Y would more likely be time frames...

Any ideas on how I might approach this?

Thanks,

John

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 14:42:34

If you provided DDL and DML for this, you would get faster answers. DDL is CREATE TABLE statments for all tables involved. DML is INSERT INTO statements for sample data. Then we would need to see the expected result set using the sample data provided.

Tara

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-19 : 15:24:50

k...try this

DDL as Follows:

CREATE TABLE [dbo].[tbl_3PrecipProduction] (
[Record_ID] [numeric](18, 0) NULL ,
[Product] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Campaign_no] [numeric](18, 0) NOT NULL ,
[CaptureTime] [datetime] NOT NULL ,
[Batch_number] [numeric](18, 0) NOT NULL ,
[Fiber_Mat1_Req] [numeric](18, 2) NULL ,
[Fiber_Mat1_Desg] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fiber_Mat2_Req] [numeric](18, 2) NULL ,
[Fiber_Mat2_Desg] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fiber_Mat3_Req] [numeric](18, 2) NULL ,
[Fiber_Mat3_Desg] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

DML:

Case 1

Insert into tbl_3PrecipProduction
(Product,
Campaign_no,
CaptureTime,
Batch_number,
Fiber_Mat1_Req,
Fiber_Mat1_Desg,
Fiber_Mat2_Req,
Fiber_Mat2_Desg,
Fiber_Mat3_Req,
Fiber_Mat3_Desg)
values
(3400,
301298,
'8/11/04 4:32:00 AM',
23,
300.2,
'F03',
20.3,
'P11',
112,
'F43' )
GO

Case 2

Insert into tbl_3PrecipProduction
(Product,
Campaign_no,
CaptureTime,
Batch_number,
Fiber_Mat1_Req,
Fiber_Mat1_Desg,
Fiber_Mat2_Req,
Fiber_Mat2_Desg,
Fiber_Mat3_Req,
Fiber_Mat3_Desg)
values
(3400,
301298,
'8/11/04 4:32:00 AM',
23,
56.8,
'P11',
43.1,
'F13',
112,
'F43' )
GO

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-20 : 10:52:05

Just wondering if anyone has any thoughts on this ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-08-20 : 12:48:13

"Then we would need to see the expected result set using the sample data provided."
...note the words expected result set....

you're nearly there....we're tough taskmasters....help us with the final step!!!

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-23 : 07:56:15

The combined total usage of ingredient P11. In this case (only two records) it would be 20.3 + 56.8 = 77.1. The ingredient in question will change as this will be a variable supplied by the user. Where I am struggling is how to deal with this when the ingredient is used in two (or more) different systems (Fiber_Mat1_Req/Fiber_Mat1_Desg vs. Fiber_Mat2_Req/Fiber_Mat2_Desg).

TotalLbs
77.1
Ingredient
P11

When searching for P11 usage.

Hope this helps.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-23 : 12:31:29

Im finding it hard to believe I have stumped everyone here......... Am I not explaining this ok? Let me know if more info is needed...

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-23 : 13:01:09

Well it looks like you're just grouping by the ingredient...

And since it's denormalized, I guess a CASE Statement as well...

We'd need more sample data...and the matching result set...

Do your inserts like



INSERT INTO tbl_3PrecipProduction (Product, Campaign_no, CaptureTime, Batch_number
	, Fiber_Mat1_Req, Fiber_Mat1_Desg, Fiber_Mat2_Req, Fiber_Mat2_Desg, Fiber_Mat3_Req, Fiber_Mat3_Desg) 
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT  3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43'

Brett

8-)

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-23 : 14:20:08

Ok using your insert statement, I have modified it as follows:

INSERT INTO tbl_3PrecipProduction (Product, Campaign_no, CaptureTime, Batch_number
, Fiber_Mat1_Req, Fiber_Mat1_Desg, Fiber_Mat2_Req, Fiber_Mat2_Desg, Fiber_Mat3_Req, Fiber_Mat3_Desg)
SELECT 3400, 301298, '8/11/04 4:32:00 AM', 23,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT 3400, 301298, '8/11/04 4:52:00 AM', 24,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT 3400, 301298, '8/11/04 5:22:00 AM', 25,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT 3400, 301298, '8/11/04 5:42:00 AM', 26,300.2,'F03',20.3,'P11',112,'F43' UNION ALL
SELECT 3433, 301299, '8/11/04 8:32:00 AM', 1,28.6,'P11',212.3,'P13',110,'F46' UNION ALL
SELECT 3433, 301299, '8/11/04 8:52:00 AM', 2,28.6,'P11',212.3,'P13',110,'F46' UNION ALL
SELECT 3433, 301299, '8/11/04 9:22:00 AM', 3,28.6,'P11',212.3,'P13',110,'F46'

Note: That the product, campaign number, batch number and P11 location change on the 5th record. This would indicate a new order was started. The "P11" ingredient is still used but is now under a different raw material. I want to be able to query and get the total usage (lbs) for the raw material P11.

I would expect the results to look like this had the user queried for the entire table for the ingredient P11:

Total Lbs used
167

In this example, P11 is used in the first 4 batches (records) under Fiber_Mat2_req. You can tell because P11 shows in the Fiber_Mat2_desg field. It is also used in the last 3 batches (records) because it shows up in Fiber_Mat1_desg (along with its cooresponding weight of 28.6). In this case the result would include totals for 20.3+20.3+20.3+20.3 AND 28.6+28.6+28.6 for a total of 167.

I have had very little dealings with the Case select... perhaps you could show me an example of how this might work ?

Thanks.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-25 : 07:57:39

I've posted the DDL, DML and what results im looking for. Wonder if anyone had any more thoughts here....

Thanks...

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-25 : 10:08:29

Ok If I run this from the productional database:

Declare @matldesg varchar (10)
Set @matldesg = 'P11'

Select SUM(Fiber_Mat1_Req) As Mat1
From tbl_3PrecipProduction
Where Fiber_Mat1_Desg = @matldesg

Select SUM(Fiber_Mat2_Req) As Mat2
From tbl_3PrecipProduction
Where Fiber_Mat2_Desg = @matldesg

Select SUM(Fiber_Mat3_Req) As Mat3
From tbl_3PrecipProduction
Where Fiber_Mat3_Desg = @matldesg

Select SUM(Fiber_Mat4_Req) As Mat4
From tbl_3PrecipProduction
Where Fiber_Mat4_Desg = @matldesg

Select SUM(Fiber_Mat5_Req) As Mat5
From tbl_3PrecipProduction
Where Fiber_Mat5_Desg = @matldesg

Select SUM(Fiber_Mat6_Req) As Mat6
From tbl_3PrecipProduction
Where Fiber_Mat6_Desg = @matldesg

Select SUM(Fiber_Mat7_Req) As Mat7
From tbl_3PrecipProduction
Where Fiber_Mat7_Desg = @matldesg

I get:

Mat1
78550.00

Mat2
403166.90

Mat3
NULL

Mat4
13700.00

Mat5
NULL

Mat6
NULL

Mat7
NULL

Indicating this material was used in 3 out of the 7 possible ingredient categories in the entire DB. I'm simply looking for the SUM of all this......

Help?

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-26 : 07:52:13

hmmm....any possible solutions ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 08:02:37

would this work?

Select SUM(isnull(Fiber_Mat1_Req, 0)) + ... + SUM(isnull(Fiber_Mat7_Req,0)) As Mat7
From tbl_3PrecipProduction
Where Fiber_Mat7_Desg = @matldesg

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources