March 3, 2022 at 2:56 pm
I am really confused about what you are asking.... in your expected output, you have 4 columns, yet in your final SELECT you have 6. So right away, I can see your output isn't going to match up with the input.
That being said, I think I am misunderstanding the requirement the more I read your query and your data. I think we need to know more about your tables. You say "on this main.id 81187546(machine id) main. quantiy purchased -1 dtd 06/04", but when I look at that, it looks to me like 4444444 were purchased based on the "quantity" value, unless the "quantity" doesn't indicate the amount purchased, in which case, how do you determine how many were purchased? Is it 1 row per item purchased and you rely on the date to determine the number purchased?
Next potential typo that I see - your ratio table has 5 columns, but your sample insert statement only has 4 columns. Is the 5th column always NULL? And your "CONS" column is defined as an INTEGER, and then you insert 1.65 (for example) which is not an integer into the column.
Next question - why are there so many ratios for a single date? And how do you determine which ratio applies to each row in the main table? Also, there is no ratio for the date 20210406, how are you handling that scenario?
Also, your expected output only seems to tackle 1 of the main.oid values. Are you intentionally filtering the others out or should they all be in the output?
I think you need to correct and verify your sample input data before you start trying to solve the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply