CODEWITHSUNDEEP
sqlsql-serverselectstored-procedures
Steinar
Steinar

Reputation: 15

Declaring and using a variable within a SELECT statement

I'm trying to make a Stored Procedure that calculates the number of days between two dates and also gets the double of the number. Currently my code looks like this

SELECT DATEDIFF(day, DateOut, DateIn) As 'TotalDays',
DATEDIFF(day, DateOut, DateIn)*2 As '2TimesDays',
FROM tablename

I was wondering if there was another way of writing this, maybe there is a way of storing the DATEDIFF(day... inside a variable and simply writing SELECT @Days As 'TotalDays', @Days*2 As '2TimesDays' From tablename

I tried to use this method:
Set @Days = (SELECT DATEDIFF(day, DateOut, DateIn) FROM tablename),
but due to having multiple instances inside the table and wanting to print the total and double for all instances this did not work

My table looks somthing like this

+-------------------------+
| DateIn     | DateOut    |
+-------------------------+ 
| 10/11/2018 | 11/12/2018 |
| 11/13/2018 | 11/14/2018 |
| 12/01/2018 | 12/16/2018 |
| 12/30/2018 | 12/30/2018 |

Upvotes: 1

Views: 77

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I like the use of lateral joins -- apply -- for this purpose:

SELECT v.TotalDays, 2 * v.TotalDay as TotalDays_2
FROM tablename t CROSS APPLY
     (VALUES (DATEDIFF(day, DateOut, DateIn)) v(TotalDays);

Some advice. Do not use single quotes for column aliases. This will likely cause you problems in the future. Only use single quotes for string and date constants.

Give your columns (and tables and so on) names that do not need to be escaped -- that means, not starting with a number and not including spaces. This makes queries easier to write and to read.

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

In such case, you can use OUTER APPLY as below

SELECT dateDif.days As 'TotalDays',
dateDif.days*2 As '2TimesDays',
FROM tablename
OUTER APPLY (SELECT DATEDIFF(day, DateOut, DateIn) days 
             FROM tablename 
             WHERE <conditions>) dateDif

Upvotes: 1

MatSnow
MatSnow

Reputation: 7537

You can use a subquery:

SELECT
    t.TotalDays,
    t.TotalDays*2 AS [2TimesDays] FROM
    (
        SELECT DATEDIFF(day, DateOut, DateIn) As 'TotalDays'
        FROM tablename
    ) t

Upvotes: 4

Related Questions