SQL SERVER - Using DATEDIFF with subquery column

Question

I have a query that looks like:

SELECT
    col1
    ,...
    ,col3
    ,(SELECT col3 FROM table where <clause>) AS MinPickTime
    ,(SELECT col3 FROM table where <clause>) AS MaxPickTime
    ,DATEDIFF(d, MinPickTime, MaxPickTime)
FROM table

However the DATEDIFF line does not like the alias columns.

In short, how do I give DATEDIFF an alias column derived by a subquery?

Mudassir Hasan · Accepted Answer · 2013-10-07 03:48:12Z

1

Use derived tables concept to access alias name.

SELECT  col1
    ,...
    ,col3,
     MinPickTime,
     MaxPickTime ,
DATEDIFF(d, MinPickTime, MaxPickTime)
FROM (
SELECT
    col1
    ,...
    ,col3
    ,(SELECT col3 FROM table where <clause>) AS MinPickTime
    ,(SELECT col3 FROM table where <clause>) AS MaxPickTime

FROM table
)z

answered Oct 7, 2013 at 3:48

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

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

1 Comment

Mudassir Hasan Over a year ago

social.msdn.microsoft.com/Forums/sqlserver/en-US/…

Mudassir Hasan · Accepted Answer · 2013-10-07 03:56:22Z

0

with temp as (SELECT
    col1
    ,...
    ,col3
    ,(SELECT col3 FROM table where <clause>) AS MinPickTime
    ,(SELECT col3 FROM table where <clause>) AS MaxPickTime
FROM table)

select DATEDIFF(d,MaxPickTime,MinPickTime) from temp

edited Oct 7, 2013 at 3:56

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

answered Oct 7, 2013 at 3:55

Mandar Patil

5382 gold badges10 silver badges30 bronze badges

1 Comment

user1131435 Over a year ago

It is always more helpful if you explain how your code works.

Collectives™ on Stack Overflow

SQL SERVER - Using DATEDIFF with subquery column

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related