I need to divide my total by the QTY, but QTY lives on every row

Question

I need to divide my total by the QTY, but QTY lives on every row

SSCrazy

Points: 2257

April 3, 2018 at 3:23 pm

I hope i explain this correctly
I am dealing with BoMs . . .
If there are 50 rows of parts, for example, on a BoM where the BoM QTY is 6, I need to divide the total of my 50 lines of duty per part by 6. If the BoM Qty is 60, i need to divide by 60.
I've been offloading to Excel up to now . . . but I need to do this in SQL.
I guess i can say the Unique value for a BoM is the Concat of BoM Name & BoM Qty.
I don't think i can bake the "6" into the duty by part at a line level, because the "6" is a header value.
I might have three lines of separate parts, say a transmission, but it might take three lines of BoM to add up to the "6". 3 cars get transmission A, 2 get Transmission 2, and one car gets transmission 3.
THoughts?

BOM NAME	B.Bom Qty
AP06A_07DEC17_07DEC17	6
AP06A_07DEC17_07DEC17	6
AP06A_07DEC17_07DEC17	6
AP06A_07DEC17_07DEC17	….
AP06A_07DEC17_07DEC17	23
AP06A_07DEC17_07DEC17	23
AP06A_07DEC17_07DEC17	23
AP06A_07DEC17_07DEC17	….
AP06A_07DEC17_07DEC17	60
AP06A_07DEC17_07DEC17	60
AP06A_07DEC17_07DEC17	60

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jeffshelix SSCrazy Points: 2257 More actions · Answer 1

I am thinking i can concat the BoM name and BoM Qty in a Unique value, then send them to a Tempt Table with the BoM qty in a column, then pull it and divide by it.

A. Would that work?
B. Is there an easier way??

thanks

Thom A SSC Guru Points: 99133 More actions · Answer 2

This is unclear at the moment. can you provide your sample data in a consumable manner (have a look at the link in my signature), but also provide your expected results? I can't tell if the data you have is your intended output, or your current data (either way, we need both).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

Thom A - Wednesday, April 4, 2018 2:28 AM
This is unclear at the moment. can you provide your sample data in a consumable manner (have a look at the link in my signature), but also provide your expected results? I can't tell if the data you have is your intended output, or your current data (either way, we need both).

+1 to this. Your question is difficult to fathom. I am struggling to even make a guess at what you need.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

My guess is that you might need to use the OVER clause. However, it's hard to be sure. Check the articles on my signature for further help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2