Other ATN2 SQL function

1. Usage of ATN2

SQL Server Query 1

            
 WITH CustomerCoordinates AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         -- Simulate customer coordinates.  In a real scenario, these would come from your table.  

         --  Using row_number() to generate different coordinates for each customer for the example  

         47.6062 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 0.1) AS Latitude,  

         -122.3321 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 0.15) AS Longitude  

     FROM Customers  

 ),  

 CustomerBearing AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         Latitude,  

         Longitude,  

         -- Calculate the bearing using ATN2  

         DEGREES(ATN2(  

             SIN(RADIANS(Longitude - -122.3321)) * COS(RADIANS(Latitude)),  

             COS(RADIANS(47.6062)) * SIN(RADIANS(Latitude)) -  

             SIN(RADIANS(47.6062)) * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude - -122.3321))  

         )) AS BearingInDegrees  

     FROM CustomerCoordinates  

 )  

 SELECT  

     CustomerID,  

     CompanyName,  

     BearingInDegrees  

 FROM CustomerBearing  

 ORDER BY BearingInDegrees;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerCoordinates"), new SqlQueryBuilder()  

         .Select()  

         .Columns("CustomerID", "CompanyName")  

         .Column(new ColumnArithmatic().Value(47.6062).ADD()  

             .StartBracket()  

                 .SqlFunction(new ROW_NUMBER().ORDER_BY(new OrderBy().SetSelectNull()))  

                 .MULTIPLY().Value(0.1)  

             .EndBracket()  

         , "Latitude")  

         .Column(new ColumnArithmatic().Value(-122.3321).ADD()  

             .StartBracket()  

                 .SqlFunction(new ROW_NUMBER().ORDER_BY(new OrderBy().SetSelectNull()))  

                 .MULTIPLY().Value(0.15)  

             .EndBracket()  

         , "Longitude")  

         .From("Customers")  

     )  

     .WithCTETable(new Table("CustomerBearing"), new SqlQueryBuilder()  

         .Select()  

         .Columns("CustomerID", "CompanyName", "Latitude", "Longitude")  

         .Column(new DEGREES(new ATN2(  

                 new ColumnArithmatic().SqlFunction(new SIN(new RADIANS(new ColumnArithmatic().Column("Longitude").SUBTRACT().Value(-122.3321))))  

                     .MULTIPLY().SqlFunction(new COS(new RADIANS(new Column("Latitude")))),  

                 new ColumnArithmatic().SqlFunction(new COS(new RADIANS(47.6062))).MULTIPLY()  

                     .SqlFunction(new SIN(new RADIANS(new Column("Latitude")))).SUBTRACT()  

                     .SqlFunction(new SIN(new RADIANS(47.6062))).MULTIPLY()  

                     .SqlFunction(new COS(new RADIANS(new Column("Latitude")))).MULTIPLY()  

                     .SqlFunction(new COS(new RADIANS(new ColumnArithmatic().Column("Longitude").SUBTRACT().Value(-122.3321))))  

             ))  

         , "BearingInDegrees")  

         .From("CustomerCoordinates")  

     )  

     .Select()  

     .Columns("CustomerID", "CompanyName", "BearingInDegrees")  

     .From("CustomerBearing")  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CustomerCoordinates
AS (SELECT CustomerID,
           CompanyName,
           @pMAIN_2507192055433499830 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * @pMAIN_2507192055433499831) AS Latitude,
           @pMAIN_2507192055433499832 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * @pMAIN_2507192055433499833) AS Longitude
    FROM Customers),
 CustomerBearing
AS (SELECT CustomerID,
           CompanyName,
           Latitude,
           Longitude,
           DEGREES(ATN2(SIN(RADIANS(Longitude - @pMAIN_2507192055433499834)) * COS(RADIANS(Latitude)), COS(RADIANS(@pMAIN_2507192055433499835)) * SIN(RADIANS(Latitude)) - SIN(RADIANS(@pMAIN_2507192055433499836)) * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude - @pMAIN_2507192055433499837)))) AS BearingInDegrees
    FROM CustomerCoordinates)
SELECT CustomerID,
       CompanyName,
       BearingInDegrees
FROM CustomerBearing
ORDER BY BearingInDegrees ASC;


            
        

Parameters (If used)

Query Results 1: