SQL Server Conditional Comparing

Question

I have two tables:

CREATE TABLE #HOURS
(DAY INTEGER,

HOUR INTEGER)

CREATE TABLE #PERSONS
(DAY INTEGER, HOUR INTEGER,
 Name NVARCHAR(50))
GO

INSERT #HOURS VALUES (1, 5)
INSERT #HOURS VALUES (1, 6)
INSERT #HOURS VALUES (1, 8)
INSERT #HOURS VALUES (1, 10)
INSERT #HOURS VALUES (1, 14)
INSERT #HOURS VALUES (1, 15)
INSERT #HOURS VALUES (1, 16)
INSERT #HOURS VALUES (1, 17)
INSERT #HOURS VALUES (1, 18)


INSERT #PERSONS VALUES (1, 5, 'Steve')
INSERT #PERSONS VALUES (1, 6, 'Steve')
INSERT #PERSONS VALUES (1, 7, 'Steve')
INSERT #PERSONS VALUES (1, 8, 'Steve')
INSERT #PERSONS VALUES (1, 10, 'Steve')
INSERT #PERSONS VALUES (1, 14, 'Steve')
INSERT #PERSONS VALUES (1, 15, 'Steve')
INSERT #PERSONS VALUES (1, 16, 'Steve')
INSERT #PERSONS VALUES (1, 17, 'Steve')

INSERT #PERSONS VALUES (1, 10, 'Jim')
INSERT #PERSONS VALUES (1, 11, 'Jim')
INSERT #PERSONS VALUES (1, 12, 'Jim')
INSERT #PERSONS VALUES (1, 13, 'Jim')

GO

Hours shows the work hours and #Persons shows the persons that entered the system on hourly base. I'd like to find the persons whose work hours matches the hours table. But he or she can skip two work hours.

I've tried this:

select t.Day, sum(t.Nulls)
from  
(select h.Hour, h.Day
      , Case   
            WHEN p.Hour is null Then 1 ELSE 0 END Nulls
from #HOURS h  
left join #PERSONS P on h.Hour = p.Hour AND h.Day = p.Day) t  
group by t.Day
HAVING sum(t.Nulls) < 2

But this only works when there is not different persons on the same day ;)

Any suggestions?

Answer 1

I think you still need to clarify a bit further what you'd expect as a result. In the mean time, would this get you started?

SELECT    p.DAY, p.Name, WORKED = COUNT(*), WORKHOURS = AVG(hc.WORKHOURS)
FROM      #PERSONS p
          INNER JOIN #HOURS h ON h.DAY = p.DAY AND h.HOUR = p.HOUR
          LEFT OUTER JOIN ( 
            SELECT    DAY, WORKHOURS = COUNT(*)
            FROM      #HOURS
            GROUP BY  DAY
          ) hc ON hc.DAY = p.DAY
GROUP BY  p.DAY, p.Name

Answer 2

What I think you mean is that for each combination of day and person, you want to return it if the number of valid hours that that person worked on that day is within 2 hours of the number of valid hours that exist for that day. If so, this should do the trick:

SELECT
    Day
    , Name
    , HoursWorked
    , HoursInDay
FROM (
    SELECT
        p.Day
        , p.Name
        , COUNT(*) HoursWorked
        , (SELECT COUNT(*) FROM #Hours H2 WHERE H2.Day = P.Day) HoursInDay
    FROM
        #Persons P INNER JOIN #Hours H 
            ON P.Day = H.Day And P.Hour = H.Hour
    GROUP BY
        p.Day, p.Name
     ) Data
WHERE
    HoursWorked + 2 >= HoursInDay