tag:blogger.com,1999:blog-1378961241059327992.post2578192719632644043..comments2023-04-05T09:35:55.180+02:00Comments on KiBeHa: Analytic FIFO multiplied - part 3Kim Berg Hansenhttp://www.blogger.com/profile/06491635470794828550noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-1378961241059327992.post-50528623581644099412017-06-01T16:09:58.779+02:002017-06-01T16:09:58.779+02:00Nice work, Duke, thanks! Must revisit this theme o...Nice work, Duke, thanks! Must revisit this theme one of these days :-)<br /><br />/KimKim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-45191558621357232532017-06-01T15:42:49.630+02:002017-06-01T15:42:49.630+02:00Hi, Kim! I was recently looking at the same Phil F...Hi, Kim! I was recently looking at the same Phil Factor SQL competition as John C. I was referred to your blog by Iudith Mentzel, and noticed John C's question. My attempt at a COGS query is available here: <br />http://it.toolbox.com/blogs/data-ruminations/wanted-single-successful-sql-for-fifo-cogs-76534<br />--DukeDukeGanotehttps://www.blogger.com/profile/01714681520904502051noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-29455276259798426892013-10-02T09:47:17.033+02:002013-10-02T09:47:17.033+02:00Thanks Kim
Best wishes. I'll keep and eye on ...Thanks Kim<br /><br />Best wishes. I'll keep and eye on your blogs. They very interesting.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-52088526997566611002013-10-01T16:18:20.138+02:002013-10-01T16:18:20.138+02:00Hi, John
Thanks for your comment :-)
If we look ...Hi, John<br /><br />Thanks for your comment :-)<br /><br />If we look at Table 1 at the simple-talk link you gave, I presume what you mean by "getting cost of goods sold" is to calculate, that the 165 in the OUT transaction at line 4 consists of 138 pieces at 245.94 each plus 27 pieces at 199.95 each for a total of 39338.37. That way you can get a precise profit calculation for that OUT transaction.<br /><br />No, I do not have such a piece of SQL, but I probably will have to create it some day. We have exactly this requirement at my work and the present slow-by-slow approach is terrible... The problem for me is mostly that I have to do it location-wise for each article and there are a lot of "move" transactions simply moving stock from one location to another, where those transactions also need to find out by FIFO how much value was moved around, and then when that is moved once again we need to follow the trail to the next location and so on.<br /><br />When I get the time to get this done at work, I'll be sure to add a blog post as well :-)Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-87186066858085079062013-09-30T10:26:01.901+02:002013-09-30T10:26:01.901+02:00Hi Kim, I like your three FIFO posts. Some good id...Hi Kim, I like your three FIFO posts. Some good ideas. You use a cursor based approach which I believe is the best practice way. Others have used a data set approach to get value of stock on hand (see https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/ ). This is indeed a quick method to get value of stock on hand but I can't find a data set solution based on this technique to get cost of goods sold. Have you attempted a data set approach to get get cost of goods sold?<br />Anonymousnoreply@blogger.com