Result set is not coming in order by date
-
Hello Everyone,
I am selecting a result set from my sql database. Below is the query.
DECLARE @today datetime
SET @today = DATEADD(MI, 330, SYSUTCDATETIME());
SELECT(CONVERT(VARCHAR,
(
SELECT
(
SELECT MAX(activity_date)
FROM
(
SELECT MAX(date) AS activity_date
FROM tblRecievedQuantity
WHERE location_id = A.location_id
UNION
SELECT MAX(date) activity_date
FROM tblIssuedQuantity
WHERE location_id = A.location_id
) b
)
), 3)) AS last_activity_date,
location_id,
(
SELECT Location
FROM tbl_Projects
WHERE Sno = A.location_id --and status=1
) AS Location,
( ISNULL((SELECT emp.Name + ', ' FROM tbl_Employee as emp where emp.Location = A.location_id AND emp.Role='Supervisor' AND emp.Working_Status='Working' FOR XML PATH('')),'No Supervisor')
) AS Supervisor,
(
SELECT serial_no
FROM tbl_Projects
WHERE Sno = A.location_id --and status=1
) AS serial_no,
SUM(CAST((REPLACE(REPLACE(OrderNumber, ',', ''), ' ', '')) AS INT)) AS OrderNumber,
SUM(CAST((REPLACE(REPLACE(OrderLength, ',', ''), ' ', '')) AS DECIMAL(10, 2))) AS OrderLength,
SUM(FortheDayReceivedTrailors) AS FortheDayReceivedTrailors,
SUM(FortheDayReceivedNumber) AS FortheDayReceivedNumber,
SUM(FortheDayReceivedLength) AS FortheDayReceivedLength,
SUM(FortheDayIssuedTrailors) AS FortheDayIssuedTrailors,
SUM(FortheDayIssuedNumber) AS FortheDayIssuedNumber,
SUM(FortheDayIssuedLength) AS FortheDayIssuedLength,
SUM(ReceivedTrailors) AS ReceivedTrailors,
SUM(ReceivedNumber) AS ReceivedNumber,
SUM(ReceivedLength) AS ReceivedLength,
SUM(IssuedTrailors) AS IssuedTrailors,
SUM(IssuedLength) AS IssuedLength,
SUM(BalanceNumber) AS BalanceNumber,
SUM(CAST(BalanceLength AS DECIMAL(10, 2))) AS BalanceLength,
SUM(CAST(IssuedLength AS DECIMAL(10, 2))) AS IssuedLength,
SUM(IssuedNumber) AS IssuedNumber
FROM
(
SELECT TRQ.location_id,
TOQ.order_qty_no_pipe AS OrderNumber,
TOQ.order_qty_meter AS OrderLength,
ISNULL(SUM(CAST(TRQ.lenght AS DECIMAL(18, 2))), 0) * 100 / CAST(TOQ.order_qty_meter AS DECIMAL(10, 2)) AS Progress,
(
SELECT ISNULL(SUM(trailors), 0)
FROM tblReceivedTrack
WHERE CAST(date_added AS DATE) = CAST(@today AS DATE)
AND order_id = TOQ.order_id
) AS FortheDayReceivedTrailors,
(
SELECT ISNULL(SUM(no_of_pipes), 0)
FROM tblReceivedTrack
WHERE CAST(date_added AS DATE) = CAST(@today AS DATE)
AND order_id = TOQ.order_id
) AS FortheDayReceivedNumber,
(
SELECT ISNULL(SUM(CAST(length AS DECIMAL(18, 2))), 0)
FROM tblReceivedTrack
WHERE CAST(date_added AS DATE) = CAST(@today AS DATE)
AND order_id = TOQ.order_id
) AS FortheDayReceivedLength,
(
SELECT ISNULL(SUM(trailors), 0)
FROM tblIssuedTrack
WHERE CAST(date_added AS DATE) = CAST(@today AS DATE)
AND order_id = TOQ.order_id
) AS FortheDayIssuedTrailors,
(
SELECT ISNULL(SUM(no_of_pipes), 0)
FROM tblIssuedTrack
WHERE CAST(date_added AS DATE) = CAST(@today AS DATE)
AND order_id = TOQ.order_id
) AS FortheDayIssuedNumber,
(
SELECT ISNULL(SUM(CAST(length AS DECIMAL(18, 2))), 0)
FROM tblIssuedTrack
WHERE CAST(date_added AS DATE) = CAST(@today AS DATE)
AND order_id = TOQ.order_id
) AS FortheDayIssuedLength,
ISNULL(SUM(TRQ.trailors), 0) AS ReceivedTrailors,
ISNULL(SUM(TRQ.no_of_pipes), 0) AS ReceivedNumber,
ISNULL(SUM(CAST(TRQ.lenght AS DECIMAL(18, 2))), 0) AS ReceivedLength,
ISNULL(SUM(TIQ.trailors), 0) AS IssuedTrailors,
ISNULL(SUM(TIQ.no_of_pipes), 0) AS IssuedNumber,
ISNULL(SUM(CAST(TIQ.lenght AS DECIMAL(18, 2))), 0) AS IssuedLength,
(ISNULL(SUM(TRQ.no_of_pipes), 0) - ISNULL(SUM(TIQ.no_of_pipes), 0)) AS BalanceNumber,
CAST((ISNULL(SUM(TRQ.lenght), 0) - ISNULL(SUM(TIQ.lenght), 0)) AS DECIMAL(18, 2)) AS BalanceLength
FROM tblRecievedQuantity TRQ
LEFT JOIN tblOrderQuantity AS TOQ ON TOQ.order_id = TRQ.order_id
LEFT JOIN tblIssuedQuantity AS TIQ ON TOQ.order_id = TIQ.order_id
WHERE (SELect status from tbl_Projects where Sno=TRQ.location_id) = 1
GROUP BY TRQ.location_id,
TOQ.dia,
TOQ.order_qty_meter,
TOQ.pipe_thickness,
TOQ.order_qty_no_pipe,
TOQ.order_id
) A
GROUP BY location_id
ORDER BY last_activity_date desc ;I want this result set order by "last_activity_date desc" But it not working., because I am changing the date format to "dd-mm-yyyy" using CONVERT(VARCHAR..). Now last_activity_date a varchar so it unable to sort.
What should I do because I need the date format "dd-mm-yyyy"
Below is the output.
Kindly suggest.
- This topic was modified 2 years, 9 months ago by gaurav.
-
Hi Gaurav
You are converting date into Varchar hence it is ordering how it will order a string. If you want order as date may be do not convert it to varchar
Thanks
-
This was removed by the editor as SPAM
- @Taps wrote:
Hi Gaurav
You are converting date into Varchar hence it is ordering how it will order a string. If you want order as date may be do not convert it to varchar
Thanks
Yes, I know but how can I sort it as the way it remains in "dd-mm-yyyy" format.
-
Let the presentation layer handle the formatting, that should only really ever be done in T-SQL as an absolute last resort.
You also mention format dd/mm/yyyy when you actually only have format dd/mm/yy in your result set as your not using the right format code. If you want YYYY you need 103 not 3 as the format code.
If you want to sort it in T-SQL you will need to keep the date as a date all the way through the script until the final select which is where you would convert it, then you can order it by a date column not a varchar column, or convert the varchar column at the end back to a date and order it again.
ORDER BY CONVERT(DATE,MyVarCharDate) DESC, that is assuming you have enough of the date available to reconvert back to a valid ISO date format.
-
and please please replace all those selects from tblIssuedTrack onto a outer apply and do the aggregations within it so you only access the table once
- frederico_fonseca wrote:
and please please replace all those selects from tblIssuedTrack onto a outer apply and do the aggregations within it so you only access the table once
Sir, could you please elaborate? I didn't get it correctly.
- Ant-Green wrote:
Let the presentation layer handle the formatting, that should only really ever be done in T-SQL as an absolute last resort.
The problem is that I am using this result set into a REST API, and API are integrated in web application and in mobile app both.
So I need to re build both the applications.
I would be happy if it is done within the result set.
Thanks
-
Well the extremely lazy way, since your query is kind of a mess of sub queries, is just to do the conversion back to datetime in the order by.
So ORDER BY CONVERT(datetime, last_activity_date, 3) desc
- gaurav wrote:
I would be happy if it is done within the result set.
Well what if your app or API starts to be used in the US or somewhere which has a MDY or they even want it as YDM or YMD format and then you cannot leverage that and you need to go and rewrite the API.
Dates should be treated as dates they are not strings.
Formatting should be done in the presentation layer. Let the end process worry about how to present it back to a user in the users correct locale
- Ant-Green wrote:gaurav wrote:
I would be happy if it is done within the result set.
Well what if your app or API starts to be used in the US or somewhere which has a MDY or they even want it as YDM or YMD format and then you cannot leverage that and you need to go and rewrite the API.
Dates should be treated as dates they are not strings.
Formatting should be done in the presentation layer. Let the end process worry about how to present it back to a user in the users correct locale
That really depends on what the consumer of the service is doing.
- ZZartin wrote:
So ORDER BY CONVERT(datetime, last_activity_date, 3) desc
Now shows error
Invalid column name 'last_activity_date'.
-
This was removed by the editor as SPAM
- gaurav wrote:ZZartin wrote:
So ORDER BY CONVERT(datetime, last_activity_date, 3) desc
Now shows error
Invalid column name 'last_activity_date'.
This is because you are trying to convert the 'alias' name - you would need to either:
- Move the calculation for the last_activity_date to a CROSS APPLY and return the value as a date - then use that returned value in the ORDER BY and convert it to your external format in the SELECT.
- Include the same calculation used to generate the value in the ORDER BY
The problem here is that there are a lot more issues with this query - for example. You are using a lot of sub-queries where they are not needed. A simple join to the tblReceivedTrack and tblIssuedTrack should be sufficient - and most likely you could get this to a simple query without querying from a derived table from a derived table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply