June 13, 2013 at 3:29 am
I have a table which contains order information. I need to SUM up the quantities ordered on an account every day. For example i could have the following records in a table
Account Number QTY Name ROWID
123456 4 Example 1
123456 6 Example 2
Now I have the following query which gets me the total of the quantities for the accountnumber
SELECT AccountNo, SUM(CONVERT(INT,QTY))AS UOM
FROM tfrOrderTransmissionDetailLog
WHERE DateDiff(dd, [TIMESTAMP],GetDate())=0
GROUP BY AccountNo
ORDER BY UOM DESC
What I need is all the information in that the above table ordered by which account has ordered the most. With a field at the end giving the total qty ordered. Something like this.
Account Number QTY Name ROWID UOM
123456 4 Example 1 10
123456 6 Example 2 10
654321 8 Example 1 17
654321 9 Example 2 17
Can anyone help me with this or tell me that it isn't possible.
Many Thanks in advance
Mark D
June 13, 2013 at 3:37 am
SELECT Account Number,QTY,Name,ROWID, SUM(CONVERT(INT,QTY)) OVER(PARTITION BY AccountNo) AS UOM
FROM tfrOrderTransmissionDetailLog
WHERE DateDiff(dd, [TIMESTAMP],GetDate())=0
ORDER BY UOM DESC
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537June 13, 2013 at 3:47 am
That works perfectly. Thanks very much
June 13, 2013 at 4:02 am
One small observation..
Its not a good idea to use a function in your WHERE clause if you can avoid it as it makes the query non-SARGable
You can modify Mark's code to something like this to make it SARGable..
DECLARE@start_date DATETIME,
@end_date DATETIME
SET@start_date = DATEADD( DAY, DATEDIFF( DAY, 0, CURRENT_TIMESTAMP ), 0 )
SET@end_date = DATEADD( DAY, 1, @start_date )
SELECTAccount Number,QTY,Name,ROWID, SUM(CONVERT(INT,QTY)) OVER(PARTITION BY AccountNo) AS UOM
FROMtfrOrderTransmissionDetailLog
WHERE[TIMESTAMP] >= @start_date AND [TIMESTAMP] < @end_date -- This is a better way to do it
ORDER BY UOM DESC
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 13, 2013 at 4:19 am
Thanks, I haven't come across that term before, Glad you mentioned it.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply