SQL count date time

Question

I have to concatenate the date and time portion of two fields, which I have managed to do, then I need to test if the result is < getdate()

select count(cast(cast(DischargeDatenew as date) as datetime) + cast(DischargeTime as time))as Requiredby FROM [dbo].[Main]
 where Location = 'Home' and ScriptTypeID = '1' and Requiredby < GETDATE()

Unfortunately the second Requiredby comes up as an invalid column name. How can I get this query to work? Do I need a subquery?

Answer 1

Yes you will need subquery:

select * from (select someExpression as Requiredby 
               FROM [dbo].[Main] 
               where Location = 'Home' and ScriptTypeID = '1') t 
where Requiredby < GETDATE()

But I really think you want this:

select sum(case when cast(cast(DischargeDatenew as date) as datetime) +
                     cast(DischargeTime as time) < getdate() then 1 
                     else 0 end) as Requiredby 
FROM [dbo].[Main] 
where Location = 'Home' and ScriptTypeID = '1'

Answer 2

You don't need the calculation in COUNT, simply move it to WHERE:

select count(*) 
FROM [dbo].[Main]
where Location = 'Home' and ScriptTypeID = '1'
and cast(cast(DischargeDatenew as date) as datetime)
       + cast(DischargeTime as time) < GETDATE()