How to pull week ending dates and end of month when end of month is mid week

I’m unsure how to proceed with this or what to search for but here goes my attempt at explaining what I’m trying to do! There may be multiple ways to figure this out as well.

I am working on a Timecards project where I want to pull in all of the timecards that have been filled in for the week that haven’t filled in the minimum amount of time. let’s say for a regular week that is 40 hours.

I also have a 2nd table that tells me the week ending dates of all weeks. for the most part this is every saturday however, if the month ends midweek then that day is the end of the week. So january we had a week ending date of Jan 31st and one of Feb 2nd.

where Jan 31st only required 32 hours and Feb 2nd required 8 hours.

Sample Data

Week Ending Date   Minimum Hours
1/26/19             40
1/31/19             32
2/2/19              8
2/9/19              40

Timecard Sample

NAME                WeekEnding           HoursWorked
Billy Joe           1/23/19               42
Homer               1/30/19               32
Marge               1/31/19               40
Homer               2/2/19                8

Ideally I would join these 2 tables by week_ending date but sometimes people enter their dates on the timecards wrong. Say they put in Jan 30th until the 31st. So I can’t do a simple End of week find for TSQL. So If I joined the 2 examples above Homer would go missing on the 31st since he entered the wrong date in his TimeCard.

I have 2 questions :

1. Is there a way to find week ending for a week and if that week is a month end week it grabs the End of Month date instead?

2. If not, is there a way I can look at the week ending table, which has every week end for the year in it. And do some sort of date range by grabbing the rows in that table?

Answer

This would be simpler if you could add a column to store the date when the week started in your calendar table (table timecard). Then we could simply aggregate all samples submitted during each interval.

Given you current database design, one solution would be to emulate this behavior, using a correlated subquery. Hereafter, the NOT EXISTS condition ensures that we are joining the current sample record with the relevant timecard record. The outer query does the aggregation by name and date interval, and the HAVING BY clause filters records where the minimum number of working hour was not reached.

SELECT
    s.name,
    s.WeekEnding DateEntered,
    t.WeekEnding,
    t.MinimumHours,
    SUM(s.HoursWorked) TotalHoursWorked
FROM sample s
INNER JOIN timecard t 
    ON s.WeekEnding <= t.WeekEnding
    AND NOT EXISTS (
        SELECT 1
        FROM timecard t1
        WHERE s.WeekEnding <= t.WeekEnding AND t1.WeekEnding > t.WeekEnding
    )
GROUP BY
    s.name,
    s.WeekEnding,
    t.WeekStart,
    t.WeekEnding,        
    t.MinimumHours
HAVING SUM(s.HoursWorked) < t.MinimumHours

---

Starting with SQLServer 2017, window functions make it easier. We can access the previous record using LAG() :

SELECT
    s.name,
    s.WeekEnding DateEntered,
    t.WeekStart,
    t.WeekEnding,        
    t.MinimumHours,
    SUM(s.HoursWorked) TotalHoursWorked
FROM
    sample s
    INNER JOIN (
        SELECT 
            WeekEnding, 
            MinimumHours, 
            LAG(WeekEnding) OVER(ORDER BY WeekEnding) WeekStart
        FROM timecard
    ) t 
        ON s.WeekEnding > t.WeekStart AND s.WeekEnding <= t.WeekEnding
GROUP BY
    s.name,
    s.WeekEnding,
    t.WeekStart,
    t.WeekEnding,        
    t.MinimumHours