Using a variable in a view creation script in SQL Server

Question

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.

Gordon Linoff · Accepted Answer · 2020-06-24 21:58:34Z

4

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?)

answered Jun 24, 2020 at 21:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EastsideDev Over a year ago

I actually have more than two CASE expressions, but listed only two here for brevity and I am only dealing with one table. I till test this out

Gordon Linoff Over a year ago

@coyotepoint . . . The values clause can define multiple columns: (values ( . . . , . . . , . . .)) v(a, b, c).

GMB · Accepted Answer · 2020-06-24 21:41:56Z

3

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

answered Jun 24, 2020 at 21:41

GMB

224k25 gold badges103 silver badges151 bronze badges

Comments

Venkataraman R · Accepted Answer · 2020-06-25 04:40:09Z

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

Collectives™ on Stack Overflow

Using a variable in a view creation script in SQL Server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related