w3resource

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


Click to view Full table

Sample Solution:

SELECT BusinessEntityID,
   SalesYTD,
   cast (SalesYTD as varchar) AS MoneyDisplayStyle1,
   now() AS CurrentDate,
   cast(now() as varchar) AS DateDisplayStyle3
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS VARCHAR(20) ) LIKE '1%';

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv