Calculation of Cost of Goods Sold

Question

Calculation of Cost of Goods Sold

levani

Old Hand

Points: 355
More actions
July 28, 2009 at 6:41 am

#99788

Hi. I need help. I am quite new in SQL. I have some basic knowlage but not much and I really need hel.
i am trying to build smal SQL database (I am using SQL expres 2005) where I will be able to calculate Cost of Goods Sold usinf avarage method. To make it easier to understend I have ctreated it in Excel file and I am attaching the screen where you can see all my calculations.
Could you please hel me or give advise how to build a query that will perfom such calculation for me. Or if you have I would be very thankfull to you if anyone can show me example how it is done.
Thanks again, and sorry for my English.

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

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

weitzera Hall of Fame Points: 3442 More actions · Answer 1

So, on a given day, the value per item is the weighted average of the purchase prices of all the purchase entries prior minus the weighted average price of all the sale entries prior?

If that's correct, you will need to do a self join, as in the example below. Note that the case statements are to prevent errors if you're trying to calculate the COGS for a product that hasn't previously been purchased, or has never been sold.

SELECT (SELECT CASE WHEN SUM(i.purchaseQuantity) = 0

THEN 0

ELSE (SUM(i.purchasePrice*i.purchaseQuantity)/SUM(i.purchaseQuantity))

END

- CASE WHEN SUM(i.saleQuantity) = 0

THEN 0

ELSE (SUM(i.salePrice * i.saleQuantity)/SUM(i.saleQuantity))

END

FROM productTable i

WHERE i.date < o.date

) AS valuePerItem

FROM productTable o

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

levani Old Hand Points: 355 More actions · Answer 2

That is exactly right. Thank you a lot for help. I will try it today.

I got the idea how it goes.

You really help me much.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Does the COGS calculation need to be run in realtime or as part of the end of day processing? Depending on the volume of data that needs to be processed this may not be the most efficient way to calculate the COGS.

Could you provide the DDL for your table, some sample data, ans expected results?

For assistance in completing this request, please read the first article referenced in my signature block. By following the instruction you find there you will get assistance and well test code quickly in return.