CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user9373049
user9373049

Reputation:

How do I correctly order dates in a T-SQL query when combined with grouping?

I am attempting to make a T-SQL query that shows total daily collected amount, ordered by descending date of order.

Here is what I tried:

select (sum(MonthAmount) + sum(LateFine)) as DailyCollection, 
    CONVERT(varchar(11),ApprovedDate,103) as InboxDate 
from [dbo].[SlipDetails] 
where ApprovedByAdmin='A' 
group by CONVERT(varchar(11),ApprovedDate,103) 
order by CONVERT(DATE, CONVERT(varchar(11),ApprovedDate,103),103) desc

Which produces the results:

30/01/2017  36010   
30/11/2017  42780   
29/12/2017  23090   
29/11/2017  66060   
28/12/2017   4700   
28/11/2017  84370

I have to show daily collected amount in admin panel of one e-commerce portal in descending order of date. How do I change this query to order the dates correctly?

Upvotes: 1

Views: 33

Answers (1)

Atharv Joshi
Atharv Joshi

Reputation: 53

Before some days I am facing the same problem but now I have solutions to it. 

select (sum(MonthAmount) + sum(LateFine)) as DailyCollection, 
CONVERT(varchar(11),ApprovedDate,103) as InboxDate  
from [dbo].[SlipDetails] where ApprovedByAdmin='A' 
group by CONVERT(varchar(11),ApprovedDate,103) 
order by CONVERT(DATE,  CONVERT(varchar(11),ApprovedDate,103),103) desc

Use this query as per your need. You will get expected results.

Upvotes: 1

Related Questions