AdventureWorks Database: Add two days to each value in the OrderDate column
SQL Query - AdventureWorks: Exercise-113 with Solution
113. From the following table write a query in SQL to add two days to each value in the OrderDate column, to derive a new column named PromisedShipDate. Return salesorderid, orderdate, and promisedshipdate column.
Sample table: sales.salesorderheadersalesorderid|revisionnumber|orderdate |duedate |shipdate |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal |taxamt |freight |totaldue |comment|rowguid |modifieddate | ------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+ 43659| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO522145787 |10-4020-000676| 29825| 279| 5| 985| 985| 5| 16281|105041Vi84182 | | 20565.6206| 1971.5149| 616.0984| 23153.2339| |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000| 43660| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18850127500 |10-4020-000117| 29672| 279| 5| 921| 921| 5| 5618|115213Vi29411 | | 1294.2529| 124.2483| 38.8276| 1457.3288| |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000| 43661| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18473189620 |10-4020-000442| 29734| 282| 6| 517| 517| 5| 1346|85274Vi6854 | 4| 32726.4786| 3153.7696| 985.553| 36865.8012| |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000| 43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4| 28832.5289| 2775.1646| 867.2389| 32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000| 43663| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18009186470 |10-4020-000510| 29565| 276| 4| 1073| 1073| 5| 4322|45303Vi22691 | | 419.4589| 40.2681| 12.5838| 472.3108| |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000| 43664| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16617121983 |10-4020-000397| 29898| 280| 1| 876| 876| 5| 806|95555Vi4081 | | 24432.6088| 2344.9921| 732.81| 27510.4109| |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000| 43665| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16588191572 |10-4020-000146| 29580| 283| 1| 849| 849| 5| 15232|35568Vi78804 | | 14352.7713| 1375.9427| 429.9821| 16158.6961| |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000| -- more --
Sample Solution:
-- Selecting specific columns from the SalesOrderHeader table
SELECT
-- Selecting the SalesOrderID column
SalesOrderID,
-- Selecting the OrderDate column
OrderDate,
-- Adding an interval of 2 days to the OrderDate and aliasing it as PromisedShipDate
OrderDate + INTERVAL '2 day' AS PromisedShipDate
FROM
Sales.SalesOrderHeader;
Explanation:
- This SQL code operates on a table named Sales.SalesOrderHeader.
- It retrieves data related to sales orders.
- The retrieved data includes the SalesOrderID, OrderDate, and a calculated column named PromisedShipDate.
- In the PromisedShipDate column, 2 days are added to the OrderDate using the INTERVAL keyword and the value '2 day'. This syntax is specific to databases like PostgreSQL, where INTERVAL can be used to perform date arithmetic.
- The result set will contain the original OrderDate values along with the corresponding PromisedShipDate values, which are two days later.
Sample Output:
salesorderid|orderdate |promisedshipdate | ------------+-----------------------+-----------------------+ 43659|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43660|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43661|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43662|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43663|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43664|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43665|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43666|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43667|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43668|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| 43669|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Obtain the salary percentile of each employee for a department.
Next: Get a newdate by adding two days with current date.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-113.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics