SQL Server - Need count through Group by of the result provided using CASE

Asked By Sree on 05-Jan-11 12:42 AM

Hi

Please find the query below.

select

Case

when CONVERT(varchar(10),(expirationdate_udt - 2), 111) > CONVERT(varchar(10),(getdate() - 2), 111)

then 'Active'

else 'Inactive' end as sta

from Customer

My requirement is to find the number of active and Inactive records.

Please help me out in writing the query to get the result.

Thanks in advance.


Count thru group by function - Lalitha Kumaran replied to Sree on 05-Jan-11 01:26 AM

select  
case when
Convert(varchar(10),expirationdate_udt-2,111)>convert(varchar(10),getdate()-2,111)
then count(*) else 0 end 'Active',
case when
Convert(varchar(10),expirationdate_udt-2,111)<=convert(varchar(10),getdate()-2,111)
then count(*) else 0 end 'InActive'
from
Customer
group by expirationdate_udt

Sree replied to Lalitha Kumaran on 05-Jan-11 05:58 AM

Thanku It was a great help!


end of post