SIGN SQL function

1. Usage of SIGN to display stock status.

SQL Server Query 1

            
SELECT  

    ProductID,  

    ProductName,  

    UnitsInStock,  

    ReorderLevel,  

    SIGN(UnitsInStock - ReorderLevel) AS StockStatus,  

    CASE  

        WHEN SIGN(UnitsInStock - ReorderLevel) = 1 THEN 'Above Reorder Level'  

        WHEN SIGN(UnitsInStock - ReorderLevel) = 0 THEN 'At Reorder Level'  

        WHEN SIGN(UnitsInStock - ReorderLevel) = -1 THEN 'Below Reorder Level'  

    END AS StockStatusText  

FROM Products;

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select().Columns("ProductID", "ProductName", "UnitsInStock", "ReorderLevel")  

    .Column(new SIGN(new ColumnArithmatic("UnitsInStock").SUBTRACT("ReorderLevel")), "StockStatus")  

    .Column(new CASE()  

        .When(new SIGN(new ColumnArithmatic("UnitsInStock").SUBTRACT("ReorderLevel")).Equale(1))  

            .Then("Above Reorder Level")  

        .When(new SIGN(new ColumnArithmatic("UnitsInStock").SUBTRACT("ReorderLevel")).Equale(0))  

            .Then("At Reorder Level")  

        .When(new SIGN(new ColumnArithmatic("UnitsInStock").SUBTRACT("ReorderLevel")).Equale(-1))  

            .Then("Below Reorder Level")  

        .End(), "StockStatusText")  

    .From("Products")  

    .Build();

Query build by SqlQueryBuilder 1

            
SELECT ProductID,
       ProductName,
       UnitsInStock,
       ReorderLevel,
       SIGN(UnitsInStock - ReorderLevel) AS StockStatus,
       CASE WHEN SIGN(UnitsInStock - ReorderLevel) = @pMAIN_2507192107352289430 THEN @pMAIN_2507192107352289431 WHEN SIGN(UnitsInStock - ReorderLevel) = @pMAIN_2507192107352289432 THEN @pMAIN_2507192107352289433 WHEN SIGN(UnitsInStock - ReorderLevel) = @pMAIN_2507192107352289434 THEN @pMAIN_2507192107352289435 END AS StockStatusText
FROM Products;

Parameters (If used)

Name Value
@pMAIN_2507192107352289430 1
@pMAIN_2507192107352289431 Above Reorder Level
@pMAIN_2507192107352289432 0
@pMAIN_2507192107352289433 At Reorder Level
@pMAIN_2507192107352289434 -1
@pMAIN_2507192107352289435 Below Reorder Level

Query Results 1:

  ProductID ProductName UnitsInStock ReorderLevel StockStatus StockStatusText
1 1 Chai 39 10 1 Above Reorder Level
2 2 Chang 17 25 -1 Below Reorder Level
3 3 Aniseed Syrup 13 25 -1 Below Reorder Level
4 4 Chef Anton's Cajun Seasoning 53 0 1 Above Reorder Level
5 5 Chef Anton's Gumbo Mix 0 0 0 At Reorder Level
6 6 Grandma's Boysenberry Spread 120 25 1 Above Reorder Level
7 7 Uncle Bob's Organic Dried Pears 15 10 1 Above Reorder Level
8 8 Northwoods Cranberry Sauce 6 0 1 Above Reorder Level
9 9 Mishi Kobe Niku 29 0 1 Above Reorder Level
10 10 Ikura 31 0 1 Above Reorder Level
11 11 Queso Cabrales 22 30 -1 Below Reorder Level
12 12 Queso Manchego La Pastora 86 0 1 Above Reorder Level
13 13 Konbu 24 5 1 Above Reorder Level
14 14 Tofu 35 0 1 Above Reorder Level
15 15 Genen Shouyu 39 5 1 Above Reorder Level
16 16 Pavlova 29 10 1 Above Reorder Level
17 17 Alice Mutton 0 0 0 At Reorder Level
18 18 Carnarvon Tigers 42 0 1 Above Reorder Level
19 19 Teatime Chocolate Biscuits 25 5 1 Above Reorder Level
20 20 Sir Rodney's Marmalade 40 0 1 Above Reorder Level
21 21 Sir Rodney's Scones 3 5 -1 Below Reorder Level
22 22 Gustaf's Knäckebröd 104 25 1 Above Reorder Level
23 23 Tunnbröd 61 25 1 Above Reorder Level
24 24 Guaraná Fantástica 20 0 1 Above Reorder Level
25 25 NuNuCa Nuß-Nougat-Creme 76 30 1 Above Reorder Level
26 26 Gumbär Gummibärchen 15 0 1 Above Reorder Level
27 27 Schoggi Schokolade 49 30 1 Above Reorder Level
28 28 Rössle Sauerkraut 26 0 1 Above Reorder Level
29 29 Thüringer Rostbratwurst 0 0 0 At Reorder Level
30 30 Nord-Ost Matjeshering 10 15 -1 Below Reorder Level
31 31 Gorgonzola Telino 0 20 -1 Below Reorder Level
32 32 Mascarpone Fabioli 9 25 -1 Below Reorder Level
33 33 Geitost 112 20 1 Above Reorder Level
34 34 Sasquatch Ale 111 15 1 Above Reorder Level
35 35 Steeleye Stout 20 15 1 Above Reorder Level
36 36 Inlagd Sill 112 20 1 Above Reorder Level
37 37 Gravad lax 11 25 -1 Below Reorder Level
38 38 Côte de Blaye 17 15 1 Above Reorder Level
39 39 Chartreuse verte 69 5 1 Above Reorder Level
40 40 Boston Crab Meat 123 30 1 Above Reorder Level
41 41 Jack's New England Clam Chowder 85 10 1 Above Reorder Level
42 42 Singaporean Hokkien Fried Mee 26 0 1 Above Reorder Level
43 43 Ipoh Coffee 17 25 -1 Below Reorder Level
44 44 Gula Malacca 27 15 1 Above Reorder Level
45 45 Rogede sild 5 15 -1 Below Reorder Level
46 46 Spegesild 95 0 1 Above Reorder Level
47 47 Zaanse koeken 36 0 1 Above Reorder Level
48 48 Chocolade 15 25 -1 Below Reorder Level
49 49 Maxilaku 10 15 -1 Below Reorder Level
50 50 Valkoinen suklaa 65 30 1 Above Reorder Level
51 51 Manjimup Dried Apples 20 10 1 Above Reorder Level
52 52 Filo Mix 38 25 1 Above Reorder Level
53 53 Perth Pasties 0 0 0 At Reorder Level
54 54 Tourtière 21 10 1 Above Reorder Level
55 55 Pâté chinois 115 20 1 Above Reorder Level
56 56 Gnocchi di nonna Alice 21 30 -1 Below Reorder Level
57 57 Ravioli Angelo 36 20 1 Above Reorder Level
58 58 Escargots de Bourgogne 62 20 1 Above Reorder Level
59 59 Raclette Courdavault 79 0 1 Above Reorder Level
60 60 Camembert Pierrot 19 0 1 Above Reorder Level
61 61 Sirop d'érable 113 25 1 Above Reorder Level
62 62 Tarte au sucre 17 0 1 Above Reorder Level
63 63 Vegie-spread 24 5 1 Above Reorder Level
64 64 Wimmers gute Semmelknödel 22 30 -1 Below Reorder Level
65 65 Louisiana Fiery Hot Pepper Sauce 76 0 1 Above Reorder Level
66 66 Louisiana Hot Spiced Okra 4 20 -1 Below Reorder Level
67 67 Laughing Lumberjack Lager 52 10 1 Above Reorder Level
68 68 Scottish Longbreads 6 15 -1 Below Reorder Level
69 69 Gudbrandsdalsost 26 15 1 Above Reorder Level
70 70 Outback Lager 15 30 -1 Below Reorder Level
71 71 Flotemysost 26 0 1 Above Reorder Level
72 72 Mozzarella di Giovanni 14 0 1 Above Reorder Level
73 73 Röd Kaviar 101 5 1 Above Reorder Level
74 74 Longlife Tofu 4 5 -1 Below Reorder Level
75 75 Rhönbräu Klosterbier 125 25 1 Above Reorder Level
76 76 Lakkalikööri 57 20 1 Above Reorder Level
77 77 Original Frankfurter grüne Soße 32 15 1 Above Reorder Level

2. Usage of SIGN to display price trend status.

SQL Server Query 2

            
WITH PriceHistory AS (  

    SELECT  

        ProductID,  

        ProductName,  

        UnitPrice,  

        LAG(UnitPrice, 1, UnitPrice) OVER (PARTITION BY ProductID ORDER BY ProductID) AS PreviousUnitPrice  

    FROM Products  

)  

SELECT  

    ProductID,  

    ProductName,  

    UnitPrice,  

    PreviousUnitPrice,  

    SIGN(UnitPrice - PreviousUnitPrice) AS PriceTrend,  

    CASE  

        WHEN SIGN(UnitPrice - PreviousUnitPrice) = 1 THEN 'Increasing'  

        WHEN SIGN(UnitPrice - PreviousUnitPrice) = 0 THEN 'No Change'  

        WHEN SIGN(UnitPrice - PreviousUnitPrice) = -1 THEN 'Decreasing'  

    END AS PriceTrendStatus  

FROM PriceHistory  

ORDER BY ProductID;

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

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

        .Select().Columns("ProductID", "ProductName", "UnitPrice")  

        .Column(new LAG(new Column("UnitPrice"), 1, new Column("UnitPrice")).PARTITION_BY(new Column("ProductID"))  

            .ORDER_BY(new OrderBy().Set(new Column("ProductID"))), "PreviousUnitPrice")  

        .From("Products")  

    )  

    .Select().Columns("ProductID", "ProductName", "UnitPrice", "PreviousUnitPrice")  

    .Column(new SIGN(new ColumnArithmatic("UnitPrice").SUBTRACT("PreviousUnitPrice")), "PriceTrend")  

    .Column(new CASE()  

        .When(new SIGN(new ColumnArithmatic("UnitPrice").SUBTRACT("PreviousUnitPrice")).Equale(1))  

            .Then("Increasing")  

        .When(new SIGN(new ColumnArithmatic("UnitPrice").SUBTRACT("PreviousUnitPrice")).Equale(0))  

            .Then("No Change")  

        .When(new SIGN(new ColumnArithmatic("UnitPrice").SUBTRACT("PreviousUnitPrice")).Equale(1))  

            .Then("Decreasing")  

        .End(), "PriceTrendStatus")  

    .From("PriceHistory")  

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

    .Build();

Query build by SqlQueryBuilder 2

            
WITH PriceHistory
AS (SELECT ProductID,
           ProductName,
           UnitPrice,
           LAG(UnitPrice, @pMAIN_2507192107352377370, UnitPrice) OVER (PARTITION BY ProductID ORDER BY ProductID ASC) AS PreviousUnitPrice
    FROM Products)
SELECT ProductID,
       ProductName,
       UnitPrice,
       PreviousUnitPrice,
       SIGN(UnitPrice - PreviousUnitPrice) AS PriceTrend,
       CASE WHEN SIGN(UnitPrice - PreviousUnitPrice) = @pMAIN_2507192107352377371 THEN @pMAIN_2507192107352377372 WHEN SIGN(UnitPrice - PreviousUnitPrice) = @pMAIN_2507192107352377373 THEN @pMAIN_2507192107352377374 WHEN SIGN(UnitPrice - PreviousUnitPrice) = @pMAIN_2507192107352377375 THEN @pMAIN_2507192107352377376 END AS PriceTrendStatus
FROM PriceHistory
ORDER BY ProductID ASC;

Parameters (If used)

Name Value
@pMAIN_2507192107352377370 1
@pMAIN_2507192107352377371 1
@pMAIN_2507192107352377372 Increasing
@pMAIN_2507192107352377373 0
@pMAIN_2507192107352377374 No Change
@pMAIN_2507192107352377375 1
@pMAIN_2507192107352377376 Decreasing

Query Results 2:

  ProductID ProductName UnitPrice PreviousUnitPrice PriceTrend PriceTrendStatus
1 1 Chai 18.0000 18.0000 0.0000 No Change
2 2 Chang 19.0000 19.0000 0.0000 No Change
3 3 Aniseed Syrup 10.0000 10.0000 0.0000 No Change
4 4 Chef Anton's Cajun Seasoning 22.0000 22.0000 0.0000 No Change
5 5 Chef Anton's Gumbo Mix 21.3500 21.3500 0.0000 No Change
6 6 Grandma's Boysenberry Spread 25.0000 25.0000 0.0000 No Change
7 7 Uncle Bob's Organic Dried Pears 30.0000 30.0000 0.0000 No Change
8 8 Northwoods Cranberry Sauce 40.0000 40.0000 0.0000 No Change
9 9 Mishi Kobe Niku 97.0000 97.0000 0.0000 No Change
10 10 Ikura 31.0000 31.0000 0.0000 No Change
11 11 Queso Cabrales 21.0000 21.0000 0.0000 No Change
12 12 Queso Manchego La Pastora 38.0000 38.0000 0.0000 No Change
13 13 Konbu 6.0000 6.0000 0.0000 No Change
14 14 Tofu 23.2500 23.2500 0.0000 No Change
15 15 Genen Shouyu 15.5000 15.5000 0.0000 No Change
16 16 Pavlova 17.4500 17.4500 0.0000 No Change
17 17 Alice Mutton 39.0000 39.0000 0.0000 No Change
18 18 Carnarvon Tigers 62.5000 62.5000 0.0000 No Change
19 19 Teatime Chocolate Biscuits 9.2000 9.2000 0.0000 No Change
20 20 Sir Rodney's Marmalade 81.0000 81.0000 0.0000 No Change
21 21 Sir Rodney's Scones 10.0000 10.0000 0.0000 No Change
22 22 Gustaf's Knäckebröd 21.0000 21.0000 0.0000 No Change
23 23 Tunnbröd 9.0000 9.0000 0.0000 No Change
24 24 Guaraná Fantástica 4.5000 4.5000 0.0000 No Change
25 25 NuNuCa Nuß-Nougat-Creme 14.0000 14.0000 0.0000 No Change
26 26 Gumbär Gummibärchen 31.2300 31.2300 0.0000 No Change
27 27 Schoggi Schokolade 43.9000 43.9000 0.0000 No Change
28 28 Rössle Sauerkraut 45.6000 45.6000 0.0000 No Change
29 29 Thüringer Rostbratwurst 123.7900 123.7900 0.0000 No Change
30 30 Nord-Ost Matjeshering 25.8900 25.8900 0.0000 No Change
31 31 Gorgonzola Telino 12.5000 12.5000 0.0000 No Change
32 32 Mascarpone Fabioli 32.0000 32.0000 0.0000 No Change
33 33 Geitost 2.5000 2.5000 0.0000 No Change
34 34 Sasquatch Ale 14.0000 14.0000 0.0000 No Change
35 35 Steeleye Stout 18.0000 18.0000 0.0000 No Change
36 36 Inlagd Sill 19.0000 19.0000 0.0000 No Change
37 37 Gravad lax 26.0000 26.0000 0.0000 No Change
38 38 Côte de Blaye 263.5000 263.5000 0.0000 No Change
39 39 Chartreuse verte 18.0000 18.0000 0.0000 No Change
40 40 Boston Crab Meat 18.4000 18.4000 0.0000 No Change
41 41 Jack's New England Clam Chowder 9.6500 9.6500 0.0000 No Change
42 42 Singaporean Hokkien Fried Mee 14.0000 14.0000 0.0000 No Change
43 43 Ipoh Coffee 46.0000 46.0000 0.0000 No Change
44 44 Gula Malacca 19.4500 19.4500 0.0000 No Change
45 45 Rogede sild 9.5000 9.5000 0.0000 No Change
46 46 Spegesild 12.0000 12.0000 0.0000 No Change
47 47 Zaanse koeken 9.5000 9.5000 0.0000 No Change
48 48 Chocolade 12.7500 12.7500 0.0000 No Change
49 49 Maxilaku 20.0000 20.0000 0.0000 No Change
50 50 Valkoinen suklaa 16.2500 16.2500 0.0000 No Change
51 51 Manjimup Dried Apples 53.0000 53.0000 0.0000 No Change
52 52 Filo Mix 7.0000 7.0000 0.0000 No Change
53 53 Perth Pasties 32.8000 32.8000 0.0000 No Change
54 54 Tourtière 7.4500 7.4500 0.0000 No Change
55 55 Pâté chinois 24.0000 24.0000 0.0000 No Change
56 56 Gnocchi di nonna Alice 38.0000 38.0000 0.0000 No Change
57 57 Ravioli Angelo 19.5000 19.5000 0.0000 No Change
58 58 Escargots de Bourgogne 13.2500 13.2500 0.0000 No Change
59 59 Raclette Courdavault 55.0000 55.0000 0.0000 No Change
60 60 Camembert Pierrot 34.0000 34.0000 0.0000 No Change
61 61 Sirop d'érable 28.5000 28.5000 0.0000 No Change
62 62 Tarte au sucre 49.3000 49.3000 0.0000 No Change
63 63 Vegie-spread 43.9000 43.9000 0.0000 No Change
64 64 Wimmers gute Semmelknödel 33.2500 33.2500 0.0000 No Change
65 65 Louisiana Fiery Hot Pepper Sauce 21.0500 21.0500 0.0000 No Change
66 66 Louisiana Hot Spiced Okra 17.0000 17.0000 0.0000 No Change
67 67 Laughing Lumberjack Lager 14.0000 14.0000 0.0000 No Change
68 68 Scottish Longbreads 12.5000 12.5000 0.0000 No Change
69 69 Gudbrandsdalsost 36.0000 36.0000 0.0000 No Change
70 70 Outback Lager 15.0000 15.0000 0.0000 No Change
71 71 Flotemysost 21.5000 21.5000 0.0000 No Change
72 72 Mozzarella di Giovanni 34.8000 34.8000 0.0000 No Change
73 73 Röd Kaviar 15.0000 15.0000 0.0000 No Change
74 74 Longlife Tofu 10.0000 10.0000 0.0000 No Change
75 75 Rhönbräu Klosterbier 7.7500 7.7500 0.0000 No Change
76 76 Lakkalikööri 18.0000 18.0000 0.0000 No Change
77 77 Original Frankfurter grüne Soße 13.0000 13.0000 0.0000 No Change