SQL Server, select with group by and date column

Question

I have tbl_Orders:

OrderNumber ProductCode Qty  OrderDate               SentDate
---------------------------------------------------------------
1000        A           100  2018-03-01 00:30:51     2018-03-02
1000        A           150  2018-03-12 04:31:54     2018-03-13
1000        B           150  2018-03-11 03:34:51     2018-03-14
1001        C           200  2018-03-01 00:30:51     2018-04-02
1002        D           200  2018-03-01 00:30:51     2018-04-15

I want to write a query to get result like:

OrderNumber Qty MAXOrderDate         MAXSentDate
------------------------------------------------
1000        400 2018-03-12 04:31:54  2018-03-14
1001        200 2018-03-01 00:30:51  2018-04-02
1002        200 2018-03-01 00:30:51  2018-04-15

From my newbie perspective it looks like I need 'group by OrderNumber'. But I am not sure what else. And the second problematic thing is "MAXOrderDate" or "MAXSentDate". I also don't know how to select max value from Date.

I really appreciate any help.

score 3 · Answer 1 · edited Mar 27 '18 at 00:09

Simple,

It should be - As long as you are aggregating data you need not group by that column (eg.. min, max, avg etc.,.. so your original idea of groupby ordernumber should be sufficient)

Try the sql 


select 
    tbl_Orders.ordernumber as OrderNumber
    , sum(tbl_Orders.qty) as qty
    ,max(tbl_Orders.orderdate) as orderdate
    ,max(tbl_Orders.sentdate) as sentdate

    from tbl_Orders 

    group by
    tbl_Orders.ordernumber

Rodrigo Werlang · Answer 2 · 2018-03-27T11:07:58.963

0

You have to sum Qty, max on MAXOrderDate and max on MAXSentDate and then group by OrderNumber, like:

SELECT OrderNumber, sum(Qty) As Qty, max(MAXOrderDate) As OrderDate, max(MAXSentDate) AS SentDate 
FROM tbl_Orders (NOLOCK) 
GROUP BY OrderNumber

I would use NOLOCK to avoid other queries to be blocked in hight performance environments. Be aware that NOLOCK could bring in the result records that are in transactions that are not yet commited.

edited Mar 27 '18 at 11:07

answered Mar 26 '18 at 22:31

Rodrigo Werlang

2,118
1
17
28

NO - you need not group by sum Qty, max on MAXOrderDate and max on MAXSentDate – Harry Mar 27 '18 at 00:11
@Harry You sad no but your answer is exactly what I wrote. Didn't understand it. – Rodrigo Werlang Mar 27 '18 at 00:18
your answer looks like you need to group by everything when in fact you need to group only by ordernumber. Perhaps you could take a minute to reformat your answer and make it clear.. – Harry Mar 27 '18 at 00:21
Nope. Not what I ment. But reading it again I agree it can be misunderstood. – Rodrigo Werlang Mar 27 '18 at 00:31

SQL Server, select with group by and date column

2 Answers2