[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Sql query help

Posted on 2011-05-04
Medium Priority
231 Views
sql server 2008,  I have recipes for a batch of a product, I need help with the sql to do the calculation to be inserted in another table (archiving the cost for history).

item_num ingred qty cost
1     888    400 .0432
1    889     3000 .056
1    890     20    .76
13  777    30   .032
13 666     400 .031

in English:
1) for all items starting with the number 1
2) for the costs of each line  qty*cost (e.g. 400 * .0432 = 17.28)
3) Add the line costs for each  product (e.g. 17.28 + 168 + 15.2 = 200.48)
4) Divide the total cost by the sum of total qty (200.48 / 3420) = .05862
5) And then insert the item number, date and #4 into another table.
0
Question by:freshgrill

LVL 6

Assisted Solution

cfEngineers earned 1000 total points
ID: 35692180
ok so this is what i have so far

select sum(qty*cost) as totcost
,sum(qty) as totqty
,sum(qty*cost)/sum(qty) as newtot
from yourtable
where 1=1
and left(item_num,1) = 1

this will give you the calculations
im still trying to work out the rest.
0

LVL 11

Expert Comment

ID: 35692192
1) for all items starting with the number 1

select * from table where item_num like '1%'

2) for the costs of each line  qty*cost (e.g. 400 * .0432 = 17.28)

select item_num, ingred, qty, cost, qty*cost as totalcost
from table
where item_num like '1%'

3) Add the line costs for each  product (e.g. 17.28 + 168 + 15.2 = 200.48)

select sum(qty*cost)
from table
where item_num like '1%'

4) Divide the total cost by the sum of total qty (200.48 / 3420) = .05862

select sum(qty*cost)/count(*)
from table
where item_num like '1%'

5) And then insert the item number, date and #4 into another table.

insert into table2 (item_num, date, tcost)
select item_num, getdate(), sum(qty*cost)/count(*)
from table
where item_num like '1%'
0

LVL 23

Accepted Solution

wdosanjos earned 1000 total points
ID: 35692254

``````SELECT item_num, getdate() 'Date', cast(sum(qty * cost) / sum(qty) as decimal(10,5)) Rate
INTO #NewTable
FROM [Test2008].[dbo].[Ingr]
WHERE cast(item_num as varchar(50)) like '1%'
GROUP BY item_num
``````

``````item_num               Date                    Rate
---------------------- ----------------------- ---------------------------------------
1                      2011-05-04 14:04:50.167 0.05862
13                     2011-05-04 14:04:50.167 0.03107
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month19 days, 3 hours left to enroll