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

x
?
Solved

Sql query help

Posted on 2011-05-04
3
Medium Priority
?
231 Views
Last Modified: 2012-05-11
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
Comment
Question by:freshgrill
3 Comments
 
LVL 6

Assisted Solution

by:cfEngineers
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

by:David Kroll
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

by:
wdosanjos earned 1000 total points
ID: 35692254
Please try the following:

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

Open in new window


Result for your sample data:

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

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question