Here are answers, except for last one… still working on it, can't figure out where to add "unknown" to column "datediff"
:
:
SELECT firstname FROM hr.employees
WHERE firstname IS NULL
--
SELECT orderid, shippeddate
FROM sales.Orders
WHERE shippeddate IS NULL
--
USE TestDB
ALTER TABLE dbo.HELLO
ADD surrogate INT IDENTITY
--
DECLARE @youngest AS DATE
DECLARE @oldest AS DATE
SET @youngest =
(SELECT TOP (1) birthdate FROM hr.employees
ORDER BY birthdate DESC)
SET @oldest =
(SELECT TOP (1) birthdate FROM hr.employees
ORDER BY birthdate ASC)
SELECT CONCAT('The diff in years ', (DATEDIFF(year, @oldest, @youngest))) AS
'Age Gap'
THIS ALSO WORKS WHICH IS WHAT SOMEONE SUGGESTED IN FORUM:
SELECT 'The diff in years ' + DATEDIFF(YEAR,MIN(birthdate), MAX(birthdate)
FROM hr.employees
-- UNSURE ABOUT THIS ONE (READ BELOW, UPDATED 3/23/14)
USE TSQL2012
SELECT orderid, custid, shippeddate, DATEDIFF(day, shippeddate, GETDATE()) as diff,
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN 'unknown'
END
FROM Sales.orders
--UPDATE
WITH CTE AS (SELECT orderid, custid, shippeddate,
DATEDIFF(DAY,shippeddate,GETDATE()) AS diff
FROM sales.orders)
SELECT orderid, custid, shippeddate,
'since shipped' =
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN '-999999'
END
FROM CTE
WHERE firstname IS NULL
--
SELECT orderid, shippeddate
FROM sales.Orders
WHERE shippeddate IS NULL
--
USE TestDB
ALTER TABLE dbo.HELLO
ADD surrogate INT IDENTITY
--
DECLARE @youngest AS DATE
DECLARE @oldest AS DATE
SET @youngest =
(SELECT TOP (1) birthdate FROM hr.employees
ORDER BY birthdate DESC)
SET @oldest =
(SELECT TOP (1) birthdate FROM hr.employees
ORDER BY birthdate ASC)
SELECT CONCAT('The diff in years ', (DATEDIFF(year, @oldest, @youngest))) AS
'Age Gap'
THIS ALSO WORKS WHICH IS WHAT SOMEONE SUGGESTED IN FORUM:
SELECT 'The diff in years ' + DATEDIFF(YEAR,MIN(birthdate), MAX(birthdate)
FROM hr.employees
-- UNSURE ABOUT THIS ONE (READ BELOW, UPDATED 3/23/14)
USE TSQL2012
SELECT orderid, custid, shippeddate, DATEDIFF(day, shippeddate, GETDATE()) as diff,
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN 'unknown'
END
FROM Sales.orders
--UPDATE
WITH CTE AS (SELECT orderid, custid, shippeddate,
DATEDIFF(DAY,shippeddate,GETDATE()) AS diff
FROM sales.orders)
SELECT orderid, custid, shippeddate,
'since shipped' =
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN '-999999'
END
FROM CTE
Well what I see here is that part of the exercise is to change the "NULLS' to Unknown. Which isn't and INT.
ReplyDeleteThis is the error we get:
Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the varchar value 'Unknown' to data type int.
That is why using INT in your case '-999999' runs the query with success.
To accomplish the selected result all we have to do is CONVERT or CAST 'diff' into a String. such as varchar or nvarchar. You can do this by using the above options. I prefer CAST. Like such:
CAST(DATEDIFF(day, shippeddate, GETDATE()) AS nvarchar) as diff
This fixed your problem and you can successfully now run your query.
WITH CTE
AS
(
SELECT orderid, custid, shippeddate, CAST(DATEDIFF(YEAR, shippeddate, GETDATE()) AS nvarchar) as diff
FROM Sales.Orders
)
SELECT
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN 'Unknown'
END AS diff
FROM CTE