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'.
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




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
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
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?
Thank you @yosiasz that's more clear.
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 )