Help on SQL coding - 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 on SQL coding

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-15 : 08:46:39

Hello.

I am looking to write some script and was wondering if someone could help me with this.

The two tables involved in this would be one for our production data (prod_data) in which data is auto generated. Meaning a new record is generated after each production run. And one for our standard (tbl_PartStandard) which is used to hold the specifications and costs of each part. Both tables reside in the same database.

In production we have three production lines that produce parts. These lines are indentified by the field stack_no under the table prod_data. For example Coater 1 would generate stack numbers like 10002, 10003. Coater 2 would be 20002, 20003 and so on.

Our production record might say we produced 99 pcs on Coater 1 against a standard that says; For that part number you should have produced 100 and $1.50 ea. Therefore you were 99% efficient and produced $148.50.

What I am trying to do is write a script that can tell me which coater is generating the most $$$ in a day(this is a field in the tbl_PartStandard called sellprice) and which one is the most efficient in terms of parts produced per hour (a field in the prod_data called sheets_into_oven against a field in the tbl_PartStandard called StdRunsPerHour).

Here is some code I use to generate the costs by part (item_no) number:

Select item_no, Sum(prod.good_parts*part.SellPrice) As [profit]
From prod_data prod
Left Join tbl_PartStandard part
ON prod.item_no = part.PartNumber
Group By item_no
Order by [Profit] Desc

Here are the fields and tables that would be involved.

Table = Prod_data
Fields are item_no, sheets_into_oven, stack_no, date_time.

Table = tbl_PartStandard
Fields are StdRunsPerHour, sellprice.

The results I am looking for are:

1st result:
Coater x produced the most $$ on "today"

2nd result:
Coater y was the most efficient on "today"

Any help to get me jump started would be greatly appreciated here. If this is not clear let me know what I am missing...

Thanks,

John

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-15 : 20:32:50

Hi John

Given that this thread has had 27 reads and no responses, I'm guessing that people are like me and are having some trouble visualising the problem

Can you post some DDL (Create Table Statements) and some DML (Insert statements with sample date) ? That way we can recreate your table structure and will be able to help.

Damian

mcp111
Starting Member

44 Posts

Posted - 2004-03-16 : 11:57:53

how are prod_data and tbl_Partstandard related, i.e. what is the common field?

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-16 : 12:37:01

What I am trying to do is write a script that can tell me which coater is generating the most $$$ in a day(this is a field in the tbl_PartStandard called sellprice) and which one is the most efficient in terms of parts produced per hour (a field in the prod_data called sheets_into_oven against a field in the tbl_PartStandard called StdRunsPerHour).

Well, one small way you can make this more effecient. You want the best performing product $ wise and the best effeciency wise, which cant happen if you cant sell any so you dont need a left join. IfF nothing was produced/sold, it cant be a top performer.

This should work (no guarentees) for the first query and if so, give you an idea of how to approach the second.

select 'Coater ' + convert(varchar(10),item_no) + ' produced the most $$ on ' + convert(varchar(14),date_time,110)
from prod_data prod join tbl_partstandard part on prod.item_no = part.PartNumber
where date_time = getdate() --or other value you want to use
group by item_no, date_time
having sum(prod.good_parts*part.SellPrice) =
(select max(Sum(prod.good_parts*part.SellPrice)) as maxDollar from tbl_partstandard group by partnumber)

What its doing is calculating the top $$ value then finding the item with that top value. You can do something similar with the effeciency (although I'm not sure from you post what fields that would be)

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-17 : 09:16:20

I suspected my post was too vague. Let me try this again. I know you guys can help me. This forum is awesome. I'm yet to see anyone stumped here.

Ok.
Here is a query from yesterday’s production. Each record is considered a run. The 1st number in the stack_no field identifies the machine (ie. 1, 2 or 3). In this case there were two item numbers (also known as Part numbers) run, ISI0237 and ISI0032 on two different machines.

prod_data:

date_time item_no stack_no sheets_into_oven
3/16/04 6:52 PM ISI0237 20003 146
3/16/04 7:14 PM ISI0032 10002 145
3/16/04 7:45 PM ISI0237 20004 146
3/16/04 8:30 PM ISI0032 10003 144
3/16/04 8:57 PM ISI0237 20005 146
3/16/04 9:23 PM ISI0032 10004 144
3/16/04 9:49 PM ISI0237 20006 146
3/16/04 10:15 PM ISI0032 10005 144
3/16/04 10:41 PM ISI0237 20007 145
3/16/04 11:07 PM ISI0032 10006 145

Below is the Part Standard. It says for ISI0032 we should have produced 150 parts (each run) @ .79 ea. For part Number ISI0237 we should have produced 150 parts (each run) @ .77 ea.

tbl_PartStandard:

PartNumber SellPrice FramesPerSpin
ISI0032 0.79 150
ISI0237 0.77 150

Now, summing these totals we find that

Coater 1 (stack_no 1xxxx) we produced 722 of ISI0032 parts. Our standard says we should have produced 750. Therefore, our efficiency is 96.26% (722/750) and the total dollars produced were $570.38 (722 * .79).

Coater 2 (stack_no 2xxxx) we produced 729 of ISI0237 parts. Our standard says we should have produced 750. Therefore, our efficiency is 97.2% (729/750) and the total dollars produced were $561.33(729 * .77).

So in summary Coater # 2 was more efficient but produced fewer dollars. The final result I am looking for would provide me with at least a summary of this:

ex.

Machine Part Number Dollars Efficiency
Coater 1 ISI0032 $570.38 96.26%
Coater 2 ISI0237 $561.33 97.2%

But it would be helpful to design a second (and perhaps third) query to only give back the machine that (1) had the best efficiency and/or (2) the machine that made the most profit. This may be one in the same.

I hope this is clearer…..and look forward to your responses.

Thanks !!!

John

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-17 : 10:30:07

DECLARE @prod_data TABLE(
prod_data_id INT IDENTITY(1,1) PRIMARY KEY,
date_time DATETIME,
item_number CHAR(7),
stack_number CHAR(5),
sheets_into_oven INT)

INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 6:52 PM','ISI0237','20003',146)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 7:14 PM','ISI0032','10002',145)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 7:45 PM','ISI0237','20004',146)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 8:30 PM','ISI0032','10003',144)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 8:57 PM','ISI0237','20005',146)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 9:23 PM','ISI0032','10004',144)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 9:49 PM','ISI0237','20006',146)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 10:15 PM','ISI0032','10005',144)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 10:41 PM','ISI0237','20007',145)
INSERT @prod_data (date_time, item_number, stack_number, sheets_into_oven)
VALUES('3/16/04 11:07 PM','ISI0032','10006',145)

DECLARE @items TABLE(
item_number CHAR(7) PRIMARY KEY,
sell_price MONEY,
frames_per_spin INT)

INSERT @items(item_number, sell_price, frames_per_spin)
VALUES('ISI0032',0.79,150)
INSERT @items(item_number, sell_price, frames_per_spin)
VALUES('ISI0237',0.77,150)

DECLARE @results1 TABLE(
machine VARCHAR(15),
item_number CHAR(7),
sell_price_total MONEY,
efficiency_percent DECIMAL(5,4),
efficiency_rating INT IDENTITY(1,1))

INSERT @results1(
machine,
item_number,
sell_price_total,
efficiency_percent)

SELECT
'Coater ' + LEFT(pd.stack_number,1) AS machine,
pd.item_number,
SUM(pd.sheets_into_oven) * i.sell_price AS sell_price_total,
SUM(CAST(pd.sheets_into_oven AS DECIMAL(9,4)))/SUM(CAST(i.frames_per_spin AS DECIMAL(9,4))) AS efficency_percent
FROM
@prod_data pd
INNER JOIN @items i ON pd.item_number = i.item_number
GROUP BY
'Coater ' + LEFT(pd.stack_number,1),
pd.item_number,
i.sell_price
ORDER BY
SUM(CAST(pd.sheets_into_oven AS DECIMAL(9,4)))/SUM(CAST(i.frames_per_spin AS DECIMAL(9,4))) DESC

DECLARE @results2 TABLE(
machine VARCHAR(15),
item_number CHAR(7),
sell_price_total MONEY,
efficiency_percent DECIMAL(5,4),
efficiency_rating INT,
revenue_rating INT IDENTITY(1,1))

INSERT @results2(
machine,
item_number,
sell_price_total,
efficiency_percent,
efficiency_rating)

SELECT
r.machine,
r.item_number,
r.sell_price_total,
r.efficiency_percent,
r.efficiency_rating
FROM
@results1 r
ORDER BY
r.sell_price_total DESC

SELECT *
FROM @results2
ORDER BY machine

That was fun. Blow it away guys. I'm sure there's a way to do it in one select, I just didn't want to spend the time on it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-17 : 11:00:01

[code]
declare @thedate datetime
set @thedate = '3/16/2004'

--Results
select distinct 'Coater ' + left(stack_no,1) coater,d.*
from
(
select
item_no,
(convert(decimal(10,2),sum(sheets_into_oven))) * sellprice dollars,
((convert(decimal(10,2),sum(sheets_into_oven))) / (count(item_no)* ps.framesperspin)) * 100 efficiency
from prod_data pd
join PartStandard ps on ps.partnumber = pd.item_no
where cast(date_time as varchar(12)) = cast(@thedate as varchar(12))
group by item_no, ps.framesperspin, ps.sellprice
) d
join prod_data pd on pd.item_no = d.item_no[/code]Most efficient and Most profitable would be a matter of selecting the top one and ordering by that column desc

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-17 : 13:38:40

man you guys are good! Thanks !!!

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-17 : 15:26:14

Ok guys...one more delima.

Here is what I ended with. And yes, it has already been modified from its original version.

--Change these varibles for different daily results
SET ANSI_NULLS on

Declare @SrtofDay datetime
Declare @EndofDay datetime

Set @SrtofDay =
'3/9/2004 6:00:00 AM'
Set @EndofDay =
'3/16/2004 11:55:00 PM'

SELECT 'Coater ' + LEFT(pd.stack_no,1) AS machine,
pd.item_no,
SUM(pd.sheets_into_oven) * i.sellprice AS sell_price_total,
SUM(CAST(pd.sheets_into_oven AS DECIMAL(9,4)))/SUM(CAST(i.framesperspin AS DECIMAL(9,4)))AS Frames_Efficency,
SUM((pd.good_parts)/((pd.parts_per_sheet)*(pd.sheets_into_oven))) As Sheets_Efficency
FROM
prod_data pd
INNER JOIN tbl_PartStandard i ON pd.item_no = i.PartNumber

WHERE
(pd.date_time BETWEEN @SrtofDay AND @EndofDay AND
good_parts is not null)

GROUP BY
'Coater ' + LEFT(pd.stack_no,1),pd.item_no,i.sellprice
ORDER BY
SUM(CAST(pd.sheets_into_oven AS DECIMAL(9,4)))/SUM(CAST(i.framesperspin AS DECIMAL(9,4))) DESC

In some cases the field good_parts has not been updated yet and there is a NULL value there. As expected i get an error:

Server: Msg 8134, Level 16, State 1, Line 13
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

I'm pretty sure i need to use the IsNull command here but a little puzzeled as to its exact placement. Bare with me, Im still learning... Thanks,
John

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-18 : 09:36:52

Actually you need to test for a zero with a case statement in your division. You also need to set null to zero. Something like this:

SELECT 'Coater ' + LEFT(pd.stack_no,1) AS machine,
pd.item_no,
SUM(pd.sheets_into_oven) * i.sellprice AS sell_price_total,
CASE
WHEN SUM(CAST(ISNULL(i.framesperspin,0) AS DECIMAL(9,4))) = 0 THEN 0
ELSE
SUM(CAST(ISNULL(pd.sheets_into_oven,0) AS DECIMAL(9,4)))/SUM(CAST i.framesperspin AS DECIMAL(9,4)))
END AS Frames_Efficency,
CASE
WHEN ISNULL(pd.parts_per_sheet,0) = 0 OR ISNULL(pd.sheets_into_oven,0) = 0 THEN 0
ELSE
SUM((pd.good_parts)/((pd.parts_per_sheet)*(pd.sheets_into_oven)))
END As Sheets_Efficency
FROM
prod_data pd
INNER JOIN tbl_PartStandard i ON pd.item_no = i.PartNumber

WHERE
(pd.date_time BETWEEN @SrtofDay AND @EndofDay AND
good_parts is not null)

GROUP BY
'Coater ' + LEFT(pd.stack_no,1),pd.item_no,i.sellprice
ORDER BY
CASE
WHEN SUM(CAST(ISNULL(i.framesperspin,0) AS DECIMAL(9,4))) = 0 THEN 0
ELSE
SUM(CAST(pd.sheets_into_oven AS DECIMAL(9,4)))/SUM(CAST(i.framesperspin AS DECIMAL(9,4)))
END DESC

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources