Q:

I want to show the record from one table

SELECT vhfloorno, 
       Count(ischeckin)  AS ChecKIn, 
       Count(ischeckout) AS CheckOut 
FROM   visitorhistory t1 
WHERE  ( Year(vhcheckinebi) = Year(@From) 
         AND Month(vhcheckinebi) = Month(@From) 
         AND Day(vhcheckinebi) = Day(@From) ) 
        OR ( Year(vhcheckoutebi) = Year(@To) 
             AND Month(vhcheckoutebi) = Month(@To) 
             AND Day(vhcheckoutebi) = Day(@To) ) 
GROUP  BY vhfloorno 

I want to show the count of Check In and Check Out Visitors from one table on the basis of Floor but by this query the check In and check Out values are same.
where vhcheckinebi is the datetime when visitor check In and vhcheckoutebi is datetime when Visitor Check Out.
Please help me.

A:

Probably you want conditional aggregation:
SELECT vhfloorno, 
       sum(case when ischeckin = 1 then 1 else 0 end)  AS ChecKIn, 
       sum(case when ischeckout = 1 then 1 else 0 end) AS CheckOut 
FROM   visitorhistory t1 
...

