DATEDIFF from computed column. What am I missing? It's been too long!

Transact-SQL
#1

CREATE TABLE RES2018 ([ResidentID] INT, [DateOfPlacement] DATETIME,[DateOfTermination] DATETIME
INSERT INTO RES2018([ResidentID],[DateOfPlacement], [DateOfTermination])
VALUES
(1, '2017-12-25','2018-02-25'),
(2,'2018-01-01','2018-02-01'),
(3,'2018-01-01','2019-02-01')

SELECT
[ResidentID]
,[DateOfPlacement]
,[DateOfTermination]
,CASE WHEN [DateOfPlacement] < CONVERT(DATETIME, '2018-01-01') THEN CONVERT(DATETIME, '2018-01-01') ELSE [DateOfPlacement] END AS [2018Start]
,CASE WHEN ([DateOfTermination] > CONVERT(DATETIME, '2019-01-01') OR [DateOfTermination] is null) THEN CONVERT(DATETIME, '2019-01-01') ELSE [DateOfTermination] END AS [2018End]
,DATEDIFF(DD,[2018Start],[2018End]) AS [2018LOS]

FROM RES2018

I'm getting this:
Msg 207, Level 16, State 1, Line 9
Invalid column name '2018Start'.
Msg 207, Level 16, State 1, Line 9
Invalid column name '2018End'.

#2

You have to use CASE statement
For datediff part

I mean
Case statement is derived column
You can not use column name
In same select

Use cte to get derived column
In select cte you can use datediff
Using direct column names
2018start
2018end

Hopefully I am clear
:slightly_smiling_face::slightly_smiling_face::+1::+1:

#3

Select
Case a then b
Datediff case a then b

OR

; With cte
(
Case when a then b as okfine
)
Select datediff ( okfine
From cte

#4 
use sqlteam
go


CREATE TABLE RES2018 ([ResidentID] INT, [DateOfPlacement] DATETIME,[DateOfTermination] DATETIME)

INSERT INTO RES2018([ResidentID],[DateOfPlacement], [DateOfTermination])
VALUES
(1, '2017-12-25','2018-02-25'),
(2,'2018-01-01','2018-02-01'),
(3,'2018-01-01','2019-02-01')

;with src
as
(
SELECT
[ResidentID]
,[DateOfPlacement]
,[DateOfTermination]
,CASE WHEN [DateOfPlacement] < CONVERT(DATETIME, '2018-01-01') 
THEN CONVERT(DATETIME, '2018-01-01') ELSE [DateOfPlacement] END AS [2018Start]
,CASE WHEN ([DateOfTermination] > CONVERT(DATETIME, '2019-01-01') 
OR [DateOfTermination] is null) THEN CONVERT(DATETIME, '2019-01-01') ELSE [DateOfTermination] END AS [2018End]
FROM RES2018
)
select *,DATEDIFF(DD,[2018Start],[2018End]) AS [2018LOS]
from  src
1 Like
#5

Thank you, but no, that is not clear. What is cte? Are you saying I have to select to a table then select from that table?

#6

Thank you @yosiasz that's more clear.

#7

What i mean is

+++++++++++++++(

You can not do this

In the

SAME select statement

Case when a then b as ok ,

You are using case formula

And naming column as ok

And then you are using ok in same select

Datediff (ok , cool )