CODEWITHSUNDEEP
sqlsql-server
Kratocus
Kratocus

Reputation: 73

Subtracting date using DATEDIFF function and getting NULL as results

So I'm tasked with finding the delay in weeks for each order. I've used the DATEDIFF function and I'd like to believe I'm on the right track but when I use it I get NULL as the result. The data type for each column are both date.

SELECT DISTINCT Sales.Orders.custid, Sales.Customers.companyname,
CASE
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 7 AND  DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 14 THEN '1 Week'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 14 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 21 THEN '2 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 21 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 28 THEN '3 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 28 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 35 THEN '4 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 35 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 42 THEN '5 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 42 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 49 THEN '6 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) > 49 THEN '7+ Weeks'
    ELSE 'Unknown'
END AS Order_Delay
FROM Sales.Orders, Sales.Customers
ORDER BY 
Order_Delay ASC;

I'm using MS SQL Server Management Studio 2016.

Upvotes: 1

Views: 922

Answers (4)

IVNSTN
IVNSTN

Reputation: 9299

https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

DATEDIFF ( datepart , startdate , enddate )

My guess is that in your system shippeddate is mostly later or equal to orderdate, so instead of

FLOOR(DATEDIFF(DAY, o.shippeddate, o.orderdate) / 7)

you might want

FLOOR(DATEDIFF(DAY, o.orderdate, o.shippeddate) / 7)

to check this assumption you might want to add ca.Order_Delay (as per code suggested by @Used_by_already) to list of selected columns and see what values are there. My bet is they are all negative.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37377

Try rewriting your query like this:

SELECT DISTINCT Sales.Orders.custid, Sales.Customers.companyname,
CASE
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) >= 7 AND  DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 14 THEN '1 Week'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 21 THEN '2 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 28 THEN '3 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 35 THEN '4 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 42 THEN '5 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 49 THEN '6 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) >= 49 THEN '7+ Weeks'
    ELSE 'Unknown'
END AS Order_Delay
FROM Sales.Orders, Sales.Customers
ORDER BY 
Order_Delay ASC;

I think you want to check whether difference is in particular range (from 7 to 14, etc.).

So I corrected first cndition:

WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) >= 7 AND  DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 14 THEN '1 Week'

You could not use BETWEEN here, as its range includes also broders of a set.

For other cases, you don't need to check whether the difference is greater than, eg. in second WHEN you know that the difference is >=14, since it failed first condition, etc.

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35593

FROM Sales.Orders, Sales.Customers is an old-fashioned way of forming a cross join. This is probably accidental but the impact of this can be awful both in terms of performance but also the results can be plain wrong - and they are in your example. It is for this reason that I always recommend you use explicit join syntax such as inner join and cease using commas as a way to define the from clause.

You simply have to properly join the 2 tables, otherwise every order is applied to every customer and the results would be quite wrong. I have guessed that join, but it should look something like the one seen below:

SELECT /* DISTINCT ?? */
    Sales.Orders.custid
  , Sales.Customers.companyname
  , CASE
        WHEN ca.Order_Delay >= 7 THEN '7+ Weeks'
        WHEN ca.Order_Delay >= 1 AND ca.Order_Delay < 7 THEN CAST(ca.Order_Delay AS varchar) + ' Weeks'
        ELSE 'Unknown'
    END AS order_delay
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON o.custid = c.id
CROSS APPLY (
    SELECT
        FLOOR(DATEDIFF(DAY, o.shippeddate, o.orderdate) / 7)
    ) ca (order_delay)
ORDER BY
    order_delay ASC
;

In SQL Server is possible to use cross apply as a way to perform a calculation, and give that calculation an alias that you can then use in the select clause. This can have the effect of making your code somewhat easier to read, but this is optional.

Above I have suggested a way to use floor() which you should read about here: https://learn.microsoft.com/en-us/sql/t-sql/functions/floor-transact-sql?view=sql-server-2017

nb: If you want to show data for unshipped orders then you may need to change to an outer apply, and if an order is unshipped the the datediff() function would return NULL and your case expression would need to explicitly cater for NULLs

SELECT /* DISTINCT ?? */
    Sales.Orders.custid
  , Sales.Customers.companyname
  , CASE
        WHEN ca.Order_Delay >= 7 THEN '7+ Weeks'
        WHEN ca.Order_Delay >= 1 AND ca.Order_Delay < 7 THEN CAST(ca.Order_Delay AS varchar) + ' Weeks'
        WHEN ca.Order_Delay IS NULL then 'Unshipped'
        ELSE 'Unknown'
    END AS order_delay
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON o.custid = c.id
OUTER APPLY (
    SELECT
        FLOOR(DATEDIFF(DAY, o.shippeddate, o.orderdate) / 7)
    ) ca (order_delay)
ORDER BY
    order_delay ASC
;

Upvotes: 2

PKCS12
PKCS12

Reputation: 454

You are looking for exactly 7, 14, 21 etc. You need >= 7 instead (and repeat for the rest...).

Upvotes: 0

Related Questions