CODEWITHSUNDEEP
sqlsql-server-2008
Abdul Muqeet
Abdul Muqeet

Reputation: 201

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.

Upvotes: 1

Views: 47

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

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 
...

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

Assuming that your column ischeckin is of type bit you should use SUM(CAST(ischeckin as INT)) instead of Count(ischeckin) since otherwise both "false" and "true" values will be counted each time:

SELECT vhfloorno, 
       SUM(CAST(ischeckin as INT)) AS ChecKIn, 
       SUM(CAST(ischeckout as INT)) AS CheckOut 
FROM   visitorhistory t1 
WHERE ...

See here for a minimum example: https://data.stackexchange.com/stackoverflow/query/477199/checkin-and-checkout-counts

Upvotes: 2

Related Questions