CODEWITHSUNDEEP
sql-serverdatetimejoin
DoIt
DoIt

Reputation: 3438

Sorting by Date of type String in SQL Server 2012

I have a SQL query where I retrieving some data using JOINS which has also a DateTime field Delivery, but while retrieving I am converting it to a String of format MMM YYYY but at the end I would like to sort the entire list by Delivery but as I converted that column into string, I am not exactly sure how to sort the results by the original DateTime field Delivery. My query is as follows

WITH deliveryProducts AS
(
  SELECT DISTINCT FORMAT(a.Delivery,'MMMM yyyy') AS Delivery, b.Product 
  FROM DEALS as a, PRODUCTS as b Where a.TradeDate<=@tradeEndDate
)
, deliveryActuals AS
(
  SELECT
          FORMAT(a.Delivery,'MMMM yyyy') AS Delivery,

          b.Product,COUNT(a.Id) AS Trades, 
          ((6.2898*SUM(a.Volume ))/DAY(EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0,a.Delivery), 0))))*0.001 AS BBLperDay,
          SUM(a.Volume) AS M3,

         SUM(a.Volume*a.Price)/Sum(a.Volume) AS WeightedAverage
FROM     Deals AS a right outer join Products AS b 
           ON a.Product=b.Product AND
           a.TradeDate<=@tradeEndDate
GROUP BY  b.Product,

          DATEADD(MONTH, DATEDIFF(MONTH, 0,a.Delivery),0),  FORMAT(a.Delivery,'MMMM yyyy')

  )

SELECT
  dp.Delivery, dp.Product, trades, BBLperDay, M3, WeightedAverage
FROM
  deliveryProducts dp

  LEFT JOIN deliveryActuals da
    on dp.Delivery = da.Delivery
    and dp.product = da.Product

ORDER BY dp.Delivery,dp.Product

The above query gives me the required data but sorts by Delivery as a string.

May I know a better way to fix this?

Upvotes: 0

Views: 395

Answers (2)

Ben Kean
Ben Kean

Reputation: 306

ORDER BY cast(dp.Delivery as date), dp.Product

SQL Server can handle casting the String 'MMMM yyyy' or 'MMM yyyy' to date or datetime on its own.

Upvotes: 1

DavidG
DavidG

Reputation: 118977

You can group by month and year as integers, then format the date at the end:

WITH deliveryProducts AS
(
  SELECT    MONTH(a.Delivery) AS DeliveryMonth, 
            YEAR(a.Delivery) AS DeliveryYear,
            b.Product 
  FROM DEALS as a, PRODUCTS as b 
  WHERE a.TradeDate <= @tradeEndDate
)
, deliveryActuals AS
(
  SELECT  MONTH(a.Delivery) AS DeliveryMonth, 
          YEAR(a.Delivery) AS DeliveryYear,
          b.Product,COUNT(a.Id) AS Trades, 
          ((6.2898*SUM(a.Volume ))/DAY(EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0,a.Delivery), 0))))*0.001 AS BBLperDay,
          SUM(a.Volume) AS M3,

         SUM(a.Volume*a.Price)/Sum(a.Volume) AS WeightedAverage
FROM     Deals AS a right outer join Products AS b 
           ON a.Product=b.Product AND
           a.TradeDate<=@tradeEndDate
GROUP BY  b.Product,
          MONTH(a.Delivery), 
          YEAR(a.Delivery)

  )

SELECT
  FORMAT(
      CAST(CAST(dp.DeliveryYear AS VARCHAR(4)) +
  RIGHT('0' + CAST(dp.DeliveryMonth AS VARCHAR(2)), 2) +
  RIGHT('0' + CAST(1 AS VARCHAR(2)), 2) 
AS DATETIME), 'MMMM yyyy'),
  dp.Product, trades, BBLperDay, M3, WeightedAverage
FROM
  deliveryProducts dp

  LEFT JOIN deliveryActuals da
    on dp.DeliveryMonth = da.DeliveryMonth
    and dp.DeliveryYear = da.DeliveryYear
    and dp.product = da.Product

ORDER BY dp.DeliveryYear, dp.DeliveryYear, dp.Product

Upvotes: 0

Related Questions