September 27, 2019 at 7:09 pm
CREATE VIEW [dbo].[vwstudent]
AS
DECLARE @Date datetime2
SELECT @Date = value
FROM dbo.configuraiton
WHERE text = 'PivotValue'
SELECT ID
, StudentId
, CASE WHEN STD.EfDate <= @Date THEN DATEADD(MONTH,-2,STD.EfDate)
ELSE STD.EfDate
END
, CreatedDate
FROM dbo.Student STD
GO
September 27, 2019 at 7:13 pm
You cannot use variables in a view.
Either re-wrtie it as a single statement, or create a stored procedure.
September 27, 2019 at 7:15 pm
How to rewrite as single statement with out variable?
September 27, 2019 at 7:21 pm
How to rewrite as single statement with out variable?
I don't know your table structures or data types. I also do not have any sample data to work with.
So the following 2 options are merely untested guesses
WITH cteDate AS (
SELECT TheDate = cast(value as datetime2)
FROM dbo.configuraiton
WHERE text = 'PivotValue'
)
SELECT ID
, StudentId
, CASE
WHEN STD.EfDate <= cte.TheDate THEN DATEADD(MONTH, -2, STD.EfDate)
ELSE STD.EfDate
END
, CreatedDate
FROM dbo.Student STD
CROSS JOIN cteDate AS cte;
SELECT ID
, StudentId
, CASE
WHEN STD.EfDate <= D.TheDate THEN DATEADD(MONTH, -2, STD.EfDate)
ELSE STD.EfDate
END
, CreatedDate
FROM dbo.Student STD
CROSS APPLY ( SELECT TheDate = cast(value as datetime2)
FROM dbo.configuraiton
WHERE text = 'PivotValue'
) AS D;
September 27, 2019 at 7:33 pm
thank you, it worked
September 27, 2019 at 7:50 pm
CREATE VIEW [dbo].[vwstudent]
AS
SELECT STD.ID,
STD.StudentId,
CASE WHEN STD.EfDate <= c.value THEN DATEADD(MONTH, -2, STD.EfDate)
ELSE STD.EfDate
END,
STD.CreatedDate
FROM dbo.Student STD
INNER JOIN dbo.configuraiton c
ON c.text = 'PivotValue'
GO
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy