CODEWITHSUNDEEP
sqlsql-servert-sql
User1899289003
User1899289003

Reputation: 872

Group by when table contains datetime columns

I have the following table:

Id | OrderNO | QTY | Station |  DateSt             |  DateEd             | Status |  CreatedDt
1  |   200   |  30 |   ABC   |  10/19/2019 1:00pm  |  NULL               |  NULL  |  10/19/2019 1:00pm
2  |   200   |  30 |   ABC   |  NULL               |  NULL               |  NULL  |  10/19/2019 2:00pm
3  |   200   |  0  |   ABC   |  NULL               |  10/19/2019 3:00pm  |  End   |  10/19/2019 3:00pm

What I want to do is to get a query to show the sum of Qty column and only the rows with initial date and end date. Something like this:

OrderNO |  QTY  | Station |  DateSt            | DateEd            |  Status   |  CreatedDt
   200  |   60  |   ABC   |  10/19/2019 1:00pm | NULL              |   NULL    |  10/19/2019 1:00pm
   200  |   0   |   ABC   |  NULL              | 10/19/2019 3:00pm |   End     |  10/19/2019 3:00pm

At the moment I have the query to show the result as I expect but I'm not using Dates columns, if I add those columns query shows more rows than I want. Here is my query:

SELECT OrderNO, SUM(QTY), Station, Status
FROM TableName WHERE Status IS NULL
GROUP BY OrderNO, Station, Status
UNION ALL 
SELECT OrderNO, QTY, Station, Status
FROM TableName WHERE Status='End'
GROUP BY OrderNO, Station, Status

Pd. The row that contains Status='End' always is going to have Qty = 0 and DateEd datetime. The first row inserted in my table must contains DateSt datetime.

What can I do to add those datetimes columns keeping the final result as I expect?

Upvotes: 1

Views: 79

Answers (2)

VBoka
VBoka

Reputation: 9083

Can you use max or min functions on date columns in your select clause? Like this: 

select OrderNO, SUM(QTY), Station, Status, max(DateSt)

And you do not have to put it in a group by clause.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think your first query query should be something like this:

SELECT OrderNO,
       MAX(CASE WHEN Status IS NULL THEN QTY END) as QTY,
       Station, Status,
       MAX(CASE WHEN Status IS NULL THEN Date END) as DateSt,
       MAX(CASE WHEN Status = 'End' THEN Date END) as DateEn
FROM TableName WHERE Status IS NULL
GROUP BY OrderNO, Station;

Upvotes: 0

Related Questions