September 18, 2020 at 7:24 pm
Can you post some DDL? We have no sample data to go off of and your query that you have there doesn't have an end to it or a FROM or an ORDER BY or anything.
But if you want a running total, SUM(value) OVER (ORDER BY order) will do that for you. I think that works in SQL 2012 anyways.
Alternately, this may be a better task for the application layer. Excel, for example, can do running totals like what you are showing with ease.
Something that will be tricky (in my mind) is are you wanting your running total to be an ACTUAL running total OR is it combining things into one huge string?
Lastly, why is this tagged with C#? I see nothing in there related to C#.
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.
September 19, 2020 at 3:20 pm
This was removed by the editor as SPAM
September 19, 2020 at 5:33 pm
Running totals are easy if you use a windowing function. These have been around since 2012.
SELECT CustomerID,
running_total = SUM([InvoiceAmount]) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM InvoiceHeader h INNER JOIN InvoiceDetail d ON h.InvoiceNo = d.InvoiceNo
September 19, 2020 at 9:34 pm
without sample data, there's no way for me to tell. Screenshots show me the results of your query, but it doesn't give me any data to work with.
And what does "wrong result" mean? I expected X and got Y? Again, without some sample data, there isn't much I can do to figure out what's wrong.
... there's no SUM() OVER (PARTITION BY...) anywhere. ???
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply