Hello,
Need a bit of help here. In one table i have my production data on some wheels. Each time the wheel is touched, it gets smaller. Then in another table I have the primary data of that wheel, ie price vendor, make model and scrap diameter.
What I'd like to figure out is, In one month, what was the wheel size to start off, what is the end size to give me a total size used during that month. If i could get that, then I think I would be able to figure out what the price cost for useage of that wheel was for the month.
Any help?
Thanks Brian
Could you please provide a description (preferrably one that's runnable in QA) of the tables involved, how they are related, and also a few rows of data that demonstrates wheels being 'touched'.
It does seem like what you want is to find start and end of month for a given wheel, and then subtract end-size from start-size, though it's pretty hard to know how to do it without knowledge of your tables or the data in them =;o)
/Kenneth
|||Lets say this is the wheel table
Wheel_NO New_Diameter Scrap_Diameter Current_Diameter Price Date_Purchased
12345678 15.2384 12.2500 14.3484 $19000.00 11/21/2005
12345677 15.2384 12.2500 15.1024 $18500.00 7/8/2005
12345679 15.2384 12.2500 13.8754 $17250.00 2/14/2005
12345673 15.2384 12.2500 14.9824 $16000.00 1/1/2004
This in the production History I have
Wheel_No Start_DT End_Dt E_Diameter Start_Dia
12345678 11/21/2005 15:34 11/21/2005 15:48 15.2225 15.2384
12345678 11/22/2005 15:34 11/22/2005 15:48 15.1055 15.2225
12345678 11/25/2005 15:34 11/25/2005 15:48 15.0054 15.1055
12345678 10/25/2006 15:34 10/25/2006 15:48 14.9654 15.0054
12345678 11/01/2006 15:34 11/01/2006 15:48 14.9561 14.9654
12345678 11/15/2006 15:34 11/15/2006 15:48 14.5466 14.9561
12345678 11/21/2006 15:36 11/21/2006 15:48 14.3484 14.5466
12345677 10/20/2006 14:36 10/20/2006 15:21 15.1024 15.2001
12345677 10/31/2006 14:36 10/31/2006 15:21 14.9856 15.1024
12345677 10/20/2006 14:36 10/20/2006 15:21 14.9548 14.9856
So from the production data table I want to know What was the Start diameter and the end diameter of each wheel that ground for November 2006
Does this help at all?
|||Brian:
Please take a look and see if this is on the right track:
set nocount on
declare @.wheel table
( wheel_no varchar (10) not null,
new_diameter numeric (9,4) not null,
scrap_diameter numeric (9,4) not null,
current_diameter numeric (9,4) not null,
price numeric (9,2) not null,
primary key (wheel_no)
)
insert into @.wheel values ('12345678', 15.2384, 12.2500, 14.3484, 19000.00)
insert into @.wheel values ('12345677', 15.2384, 12.2500, 15.1024, 18500.00)
insert into @.wheel values ('12345679', 15.2384, 12.2500, 13.8754, 17250.00)
insert into @.wheel values ('12345673', 15.2384, 12.2500, 14.9824, 16000.00)
--select * from @.wheeldeclare @.prodHistory table
( wheel_no varchar (10) not null,
start_dt datetime not null,
end_dt datetime not null,
e_diameter numeric (9,4) not null,
start_dia numeric (9,4) not null,
primary key (wheel_no, start_dt, start_dia)
)
insert into @.prodHistory values ('12345678', '11/21/5 15:34', '11/21/5 15:48', 15.2225, 15.2384)
insert into @.prodHistory values ('12345678', '11/22/5 15:34', '11/22/5 15:48', 15.1055, 15.2225)
insert into @.prodHistory values ('12345678', '11/25/5 15:34', '11/25/5 15:48', 15.0054, 15.1055)
insert into @.prodHistory values ('12345678', '10/25/6 15:34', '10/25/6 15:48', 14.9654, 15.0054)
insert into @.prodHistory values ('12345678', '11/1/6 15:34', '11/1/6 15:48', 14.9561, 14.9654)
insert into @.prodHistory values ('12345678', '11/15/6 15:34', '11/15/6 15:48', 14.5466, 14.9561)
insert into @.prodHistory values ('12345678', '11/21/6 15:34', '11/21/6 15:48', 14.3484, 14.5466)
insert into @.prodHistory values ('12345677', '10/20/6 14:36', '10/20/6 15:21', 15.1024, 15.2001)
insert into @.prodHistory values ('12345677', '10/31/6 14:36', '10/31/6 15:21', 14.9856, 14.9856)
insert into @.prodHistory values ('12345677', '10/20/6 14:36', '10/20/6 15:21', 14.9548, 14.9856)
--select * from @.prodHistory
-- -
-- This is the subquery where most of the work takes place
-- I show the output of this separately so that it is easire
-- to gauge whether or not this query is on track.
-- -
/*
select h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter)
as [Price / Dia],
month (h.start_dt) as month,
max (h.start_dia) as start_diameter,
min (h.e_diameter) as end_diameter,
max (h.start_dia) - min (e_diameter) as diff
from @.prodHistory h
inner join @.wheel w
on h.wheel_no = w.wheel_no
group by h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter),
month (h.start_dt)
*/-- wheel_no Price / Dia month start_diameter end_diameter diff
-- - -- -- --
-- 12345677 6190.6036675143889 10 15.2001 14.9548 .2453
-- 12345678 6357.9172801499129 10 15.0054 14.9654 .0400
-- 12345678 6357.9172801499129 11 15.2384 14.3484 .8900select wheel_no,
month,
convert (numeric (9,2), diff * [Price / Dia]) as Cost
from (select h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter)
as [Price / Dia],
month (h.start_dt) as month,
max (h.start_dia) as start_diameter,
min (h.e_diameter) as end_diameter,
max (h.start_dia) - min (e_diameter) as diff
from @.prodHistory h
inner join @.wheel w
on h.wheel_no = w.wheel_no
group by h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter),
month (h.start_dt)
) x
-- -- Sample Output:-- wheel_no month Cost
-- - --
-- 12345677 10 1518.56
-- 12345678 10 254.32
-- 12345678 11 5658.55
