AdventureWorks Database: List the salesperson whose salesytd begins with 1

SQL Query - AdventureWorks: Exercise-177 with Solution

177. From the following table write a query in SQL to list the salesperson whose salesytd begins with 1. Convert SalesYTD and current date in text format.

Sample table: Sales.SalesPerson

businessentityid|territoryid|salesquota|bonus|commissionpct|salesytd    |saleslastyear|rowguid                             |modifieddate           |
----------------+-----------+----------+-----+-------------+------------+-------------+------------------------------------+-----------------------+
             274|           |          |    0|            0| 559697.5639|            0|48754992-9ee0-4c0e-8c94-9451604e3e02|2010-12-28 00:00:00.000|
             275|          2|    300000| 4100|        0.012|3763178.1787| 1750406.4785|1e0a7274-3064-4f58-88ee-4c6586c87169|2011-05-24 00:00:00.000|
             276|          4|    250000| 2000|        0.015|4251368.5497| 1439156.0291|4dd9eee4-8e81-4f8c-af97-683394c1f7c0|2011-05-24 00:00:00.000|
             277|          3|    250000| 2500|        0.015|3189418.3662| 1997186.2037|39012928-bfec-4242-874d-423162c3f567|2011-05-24 00:00:00.000|
             278|          6|    250000|  500|         0.01|1453719.4653| 1620276.8966|7a0ae1ab-b283-40f9-91d1-167abf06d720|2011-05-24 00:00:00.000|
             279|          5|    300000| 6700|         0.01| 2315185.611| 1849640.9418|52a5179d-3239-4157-ae29-17e868296dc0|2011-05-24 00:00:00.000|
             280|          1|    250000| 5000|         0.01|1352577.1325|  1927059.178|be941a4a-fb50-4947-bda4-bb8972365b08|2011-05-24 00:00:00.000|
             281|          4|    250000| 3550|         0.01|2458535.6169| 2073505.9999|35326ddb-7278-4fef-b3ba-ea137b69094e|2011-05-24 00:00:00.000|
             282|          6|    250000| 5000|        0.015|2604540.7172| 2038234.6549|31fd7fc1-dc84-4f05-b9a0-762519eacacc|2011-05-24 00:00:00.000|
             283|          1|    250000| 3500|        0.012|1573012.9383| 1371635.3158|6bac15b2-8ffb-45a9-b6d5-040e16c2073f|2011-05-24 00:00:00.000|
             284|          1|    300000| 3900|        0.019|1576562.1966|            0|ac94ec04-a2dc-43e3-8654-dd0c546abc17|2012-09-23 00:00:00.000|
             285|           |          |    0|            0| 172524.4512|            0|cfdbef27-b1f7-4a56-a878-0221c73bae67|2013-03-07 00:00:00.000|
             286|          9|    250000| 5650|        0.018|1421810.9242| 2278548.9776|9b968777-75dc-45bd-a8df-9cdaa72839e1|2013-05-23 00:00:00.000|
             287|           |          |    0|            0|  519905.932|            0|1dd1f689-df74-4149-8600-59555eef154b|2012-04-09 00:00:00.000|
             288|          8|    250000|   75|        0.018|1827066.7118| 1307949.7917|224bb25a-62e3-493e-acaf-4f8f5c72396a|2013-05-23 00:00:00.000|
             289|         10|    250000| 5150|         0.02|4116871.2277| 1635823.3967|25f6838d-9db4-4833-9ddc-7a24283af1ba|2012-05-23 00:00:00.000|
             290|          7|    250000|  985|        0.016|3121616.3202| 2396539.7601|f509e3d4-76c8-42aa-b353-90b7b8db08de|2012-05-23 00:00:00.000|

Click to view Full table

Sample Solution:

-- This SQL query retrieves data from the Sales.SalesPerson table and applies various casting operations on the SalesYTD column.
-- Selecting columns: BusinessEntityID, SalesYTD, MoneyDisplayStyle1, CurrentDate, and DateDisplayStyle3.
SELECT BusinessEntityID,
       SalesYTD,
       CAST(SalesYTD AS VARCHAR) AS MoneyDisplayStyle1,
       NOW() AS CurrentDate,
       CAST(NOW() AS VARCHAR) AS DateDisplayStyle3
-- From the Sales.SalesPerson table.
FROM Sales.SalesPerson
-- Filtering the results based on the SalesYTD column.
WHERE CAST(SalesYTD AS VARCHAR(20)) LIKE '1%';

Explanation:

• The query retrieves data from the SalesPerson table in the Sales schema.
• It selects five columns: BusinessEntityID, SalesYTD, MoneyDisplayStyle1, CurrentDate, and DateDisplayStyle3.
• The CAST function is used to convert the SalesYTD column to a VARCHAR data type and assign it an alias MoneyDisplayStyle1.
• The NOW() function is used to retrieve the current date and time, and it is aliased as CurrentDate.
• Another CAST function is applied to the current date to convert it to a VARCHAR data type, and it is assigned an alias DateDisplayStyle3.
• The WHERE clause filters the results based on the SalesYTD column:
• Only rows where the SalesYTD value, when cast to VARCHAR with a length of 20 characters, starts with '1' will be included in the result set.

Sample Output:

businessentityid|salesytd    |moneydisplaystyle1|currentdate                  |datedisplaystyle3               |
----------------+------------+------------------+-----------------------------+--------------------------------+
             278|1453719.4653|1453719.4653      |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|
             280|1352577.1325|1352577.1325      |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|
             283|1573012.9383|1573012.9383      |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|
             284|1576562.1966|1576562.1966      |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|
             285| 172524.4512|172524.4512       |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|
             286|1421810.9242|1421810.9242      |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|
             288|1827066.7118|1827066.7118      |2022-12-01 14:35:09.855 +0530|2022-12-01 14:35:09.855204+05:30|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Fetch all products with a weight of less than 10 pounds.
Next: Return the count of employees by Name and Title, Name, and company total.