CODEWITHSUNDEEP
sqlsql-servert-sqlselectsql-view
EastsideDev
EastsideDev

Reputation: 6639

Using a variable in a view creation script in SQL Server

I now have an intermediate view in SQL Server, and a final view, as follows:

Intermediate view:

SELECT
    ....
    ElapsedDays = DATEDIFF(d, ri.DateReceived, GETDATE()),
    .....
FROM 
    RegionalInventory AS ri

Final view:

SELECT
    ....
    PenaltyBucket = COALESCE(CASE WHEN inv.ElapsedDays <= 30 THEN 'Not Late' END,
                             CASE WHEN inv.ElapsedDays > 30 THEN 'Late' END)
    ....
FROM 
    Inventory AS inv

I am wondering if there's a way for me to combine both views into one, but I am not sure how to declare a variable to hold the value of ElapsedDays, and then use it to set the value for the ElapsedDays column and to do the logical test, to both create the PenaltyBucket column and fill it with the right value.

Upvotes: 0

Views: 301

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12959

You can define inner view as a CTE and then refer it again.

CREATE VIEW [ schema_name . ] view_name [ ( column_name [ ,...n ] ) ] AS <select_statement> [;] <select_statement> ::=
[ WITH <common_table_expression> [ ,...n ] ]
SELECT <select_criteria>

Below is demo code for your reference.

create view vw_test
as 
WITH cte_1 as
(
select 1 as a
)
SELECT t.testname
from (values (1,'test1'),(2,'test2')) as t(a,testname)
join cte_1 as c
on c.a = t.a

select * from vw_test

What you can do is. Sample code for your reference.

CREATE VIEW outerView
AS
WITH cte_innerview
as
(
SELECT
    ....
    ElapsedDays = DATEDIFF(d, ri.DateReceived, GETDATE()),
    .....
FROM 
    RegionalInventory AS ri
)
SELECT
    ....
    PenaltyBucket = COALESCE(CASE WHEN inv.ElapsedDays <= 30 THEN 'Not Late' END,
                             CASE WHEN inv.ElapsedDays > 30 THEN 'Late' END)
    ....
FROM 
    Inventory AS inv
INNER JOIN cte_innerview as c
on c.InventoryId = inv.InventoryId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Assuming that you really have only one table (called RegionalInventory, this is a convenient place for a lateral join:

SELECT . . ., v.ElapsedDays,
       (CASE WHEN v.ElapsedDays <= 30 THEN 'Not Late'
             WHEN v.ElapsedDays > 30 THEN 'Late'
        END) as PenaltyBucket
FROM RegionalInventory ri CROSS APPLY
     (VALUES (DATEDIFF(day, ri.DateReceived, GETDATE()))
     ) v(ElapsedDays);

Note:

  • There is no need to use COALESCE() with two CASE expressions. One CASE can handle multiple conditions.
  • Spell out day when using DATEDIFF(). That is just a good habit (consider: does m mean minutes or months?)

Upvotes: 4

GMB
GMB

Reputation: 222412

You can just repeat the expression in the SELECT clause:

SELECT
    ...,
    DATEDIFF(d, ri.DateReceived, GETDATE()) AS ElapsedDays,
    CASE 
        WHEN DATEDIFF(d, ri.DateReceived, GETDATE()) <= 30 then 'Not Late'
        ELSE 'Late' 
    END AS PenaltyBucket 
    ...
FROM RegionalInventory as ri

Alternatively, you can use a subquery:

SELECT
    t.*,
    CASE 
        WHEN ElapsedDays  <= 30 then 'Not Late'
        ELSE 'Late' 
    END AS PenaltyBucket 
FROM (
    SELECT
        ...,
        DATEDIFF(d, ri.DateReceived, GETDATE()) AS ElapsedDays,
        ...
    FROM RegionalInventory as ri
) t

Upvotes: 3

Related Questions