Other LOG SQL function

1. Usage of LOG to show Days since first order

SQL Server Query 1

            
 SELECT  

     o.OrderID,  

     o.OrderDate,  

     DATEDIFF(DAY, FirstOrderDate, o.OrderDate) AS DaysSinceFirstOrder,  

     LOG(DATEDIFF(DAY, FirstOrderDate, o.OrderDate) + 1) AS LogDaysSinceFirstOrder  

 FROM Orders o  

 JOIN [Order Details] od ON o.OrderID = od.OrderID  

 CROSS APPLY (  

     SELECT MIN(OrderDate) AS FirstOrderDate  

     FROM Orders o2  

     JOIN [Order Details] od2 ON o2.OrderID = od2.OrderID  

     WHERE od2.ProductID = 11  

 ) AS FirstOrder  

 WHERE od.ProductID = 11  

 ORDER BY o.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("o.OrderID", "o.OrderDate")  

     .Column(new DATEDIFF(SqlDateInterval.day, new Column("FirstOrderDate"), new Column("o.OrderDate"))  

         , "DaysSinceFirstOrder")  

     .Column(new LOG(  

             new DATEDIFF(SqlDateInterval.day, new Column("FirstOrderDate"), new Column("o.OrderDate"))  

                 .AddArithmatic(new Arithmatic().Add(1))  

         )  

         , "LogDaysSinceFirstOrder")  

     .From("Orders", "o")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("[Order Details]","od"))  

             .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

     })  

     .CrossApply(new SqlQueryBuilder().Select()  

         .Column(new MIN(new Column("OrderDate")), "FirstOrderDate")  

         .From("Orders", "o2")  

         .Join(new List<IJoin>()  

         {  

             new INNERJOIN().TableName(new Table("[Order Details]","od2"))  

                 .On(new Column("o2.OrderID").Equale(new Column("od2.OrderID")))  

         })  

         .Where(new Where(new Column("od2.ProductID").Equale(11)))  

     , "FirstOrder")  

     .Where(new Where(new Column("od.ProductID").Equale(11)))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.OrderID,
       o.OrderDate,
       DATEDIFF(day, FirstOrderDate, o.OrderDate) AS DaysSinceFirstOrder,
       LOG(DATEDIFF(day, FirstOrderDate, o.OrderDate) + @pMAIN_2507192048120206690) AS LogDaysSinceFirstOrder
FROM Orders AS o
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID CROSS APPLY (SELECT MIN(OrderDate) AS FirstOrderDate
                                            FROM Orders AS o2
                                                 INNER JOIN
                                                 [Order Details] AS od2
                                                 ON o2.OrderID = od2.OrderID
                                            WHERE od2.ProductID = @pMAIN_2507192048120206691) AS FirstOrder
WHERE od.ProductID = @pMAIN_2507192048120206692
ORDER BY o.OrderDate ASC;


            
        

Parameters (If used)

Query Results 1: