DATEDIFF SQL function

1. Usage of DATEDIFF to identify the potential delays.

SQL Server Query 1

            
SELECT  

    OrderID,  

    OrderDate,  

    ShippedDate,  

    ShipName,  

    ShipCity,  

    -- Calculate the number of days between OrderDate and ShippedDate  

    DATEDIFF(DAY, OrderDate, ShippedDate) AS DaysToShip,  

    CASE  

        WHEN DATEDIFF(DAY, OrderDate, ShippedDate) > 10 THEN 'Delayed'  

        WHEN DATEDIFF(DAY, OrderDate, ShippedDate) > 5 AND DATEDIFF(DAY, OrderDate, ShippedDate) <= 10 THEN 'Medium'  

        ELSE 'Fast'  

    END AS ShippingSpeed,  

    AVG(DATEDIFF(DAY, OrderDate, ShippedDate)) OVER (PARTITION BY ShipCity) AS AvgShippingTimeByCity  

FROM Orders  

WHERE ShippedDate IS NOT NULL  -- Only consider orders that have been shipped  

ORDER BY OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Columns("OrderID", "OrderDate", "ShippedDate", "ShipName", "ShipCity")  

    .Column(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("ShippedDate")), "DaysToShip")  

    .Column(new CASE()  

        .When(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("ShippedDate")).GreaterThan(10))  

            .Then("Delayed")  

        .When(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("ShippedDate")).GreaterThan(5)  

            .AND(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("ShippedDate")).LessThanOrEqualeTo(10)))  

        .Then("Medium")  

        .Else("Fast"), "ShippingSpeed")  

    .Column(new AVG(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("ShippedDate")))  

        .OVER(new OVER().PARTITION_BY(new Column("ShipCity")))  

    , "AvgShippingTimeByCity")  

    .From("Orders")  

    .Where(new Where(new IS_NOT_NULL(new Column("ShippedDate"))))  

    .OrderBy(new OrderBy().Set(new Column("OrderDate")))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OrderID,
       OrderDate,
       ShippedDate,
       ShipName,
       ShipCity,
       DATEDIFF(day, OrderDate, ShippedDate) AS DaysToShip,
       CASE WHEN DATEDIFF(day, OrderDate, ShippedDate) > @pMAIN_2507200159531533480 THEN @pMAIN_2507200159531533481 WHEN DATEDIFF(day, OrderDate, ShippedDate) > @pMAIN_2507200159531533482
                                                                                                                         AND DATEDIFF(day, OrderDate, ShippedDate) <= @pMAIN_2507200159531533483 THEN @pMAIN_2507200159531533484 ELSE @pMAIN_2507200159531533485 END AS ShippingSpeed,
       AVG(DATEDIFF(day, OrderDate, ShippedDate)) OVER (PARTITION BY ShipCity) AS AvgShippingTimeByCity
FROM Orders
WHERE ShippedDate IS NOT NULL
ORDER BY OrderDate ASC;


            
        

Parameters (If used)

Query Results 1: