How do I compare SQL strings based on values and get the sum?
I am trying to update an old custom e-commerce system. I am trying to make a log of changes to the number of entries made to an item based on rows from a table (call it "old_table") that the old user system creates. I can't seem to hack this issue.
When someone starts modifying an item in the system, they create an entry in "old_table" with the values:
Time | User | Current Qty | Status | Item
-----------------------------------------------------------
2015-07-09 05:30:47 | Admin | 10 | Start | ABC 1
When a person finishes changing the number of elements in the system, he creates an entry in "old_table" with the values:
Time | User | Current Qty | Status | Item
-----------------------------------------------------------
2015-07-09 05:50:47 | Admin | 09 | End | ABC 1
--- Note. Unfortunately, if the quantity is set to "00", there will be no "End" entry. I guessed that I would set a 24 hour limit, then I would think that the user would set it to "00" ---
My goal
In a new table, I am trying to create a row where the user "Admin" has subtracted / added a total of "X" of the item "ABC 1" and if within 24 hours there is no row "End" within the "old_table" for the given item so that make a line indicating that the user "Admin" has set the value to "00".
Anyone have any ideas, suggestions or a solution? I tried for several weeks to get this problem resolved. Isn't that impossible?
source to share
You marked this with mysql and sql server. What system / version are you using?
With SQL-Server (> = 2008) you can use a window function (OVER clause: https://msdn.microsoft.com/en-us/library/ms189461.aspx ) which gives you the grouped count, sum, avg and others aggregated functions.
source to share