Join Problem

Transact-SQL
1

Hello everyone: I am working on a time clock database.

I am trying to get only the records from TimeCardEntries where the dates fall between the current PAYPERIOD. The current payperiod is determined by Select TOP 1 From PayPeriods WHERE Status =0

Tables:

Table1 is PAYPERIODS
Table2 is TimeCardEntries

PAYPERIODS
ID,DateStart,DateEnd,Status
201701,2017-01-01,2017-01-13,1
201702,2017-01-14,2017-01-30,0
201703,2017-01-31,2017-02-14,0
201704,2017-02-15,2017-02-27,0
201705,2017-02-28,2017-03-14,0

TimeCardEntries
UserID,clockYear,WeekNo,hoursTotal,hoursREG,hoursOT
8,2017,3,0.3819,0.3819,NULL
8,2017,4,7.5511,7.5511,NULL
24,2017,4,-838.9997,-838.9997,NULL
1,2017,3,16.6459,16.6459,NULL
4,2017,3,-825.4902,-825.4902,NULL
4,2017,4,-838.9997,-838.9997,NULL
3,2017,3,29.6834,29.6834,NULL
3,2017,4,-838.9997,-838.9997,NULL
9,2017,3,17.1631,17.1631,NULL
5,2017,3,42.4722,40,2.4722
5,2017,4,7.4892,7.4892,NULL
11,2017,5,16,16,NULL

Thank you you for you help!

2

Please post DML and DDL

create table #PAYPERIODS(good int not null, post varchar(50) not null)
create table #TimeCardEntries

insert into #PAYPERIODS
3

My mistake. The TimeCardEntries data did not copy correctly. I have included sample data in .CSV (even though its coming from the database)

PAYPERIODS
ID,DateStart,DateEnd,Status
201702,2017-01-14,2017-01-30,0

TimeCardEntries
UserID,clockYear,startTime,EndTime
8,2017,2017-01-01 11:00,2017-01-01 13:00
8,2017,2017-01-01 11:00,2017-01-01 15:00
24,2017,2017-01-02 11:00,2017-01-02 13:00
1,2017,2017-01-02 11:00,2017-01-02 13:00
4,2017,2017-01-03 11:00,2017-01-03 13:00
4,2017,2017-01-03 11:00,2017-01-03 13:00
3,2017,2017-01-16 11:00,2017-01-16 13:00
3,2017,2017-01-16 11:00,2017-01-16 13:00
9,2017,2017-01-18 11:00,2017-01-18 13:00
5,2017,2017-01-20 11:00,2017-01-20 13:00
5,2017,2017-01-20 11:00,2017-01-20 13:00
11,2017,2017-01-21 11:00,2017-01-21 13:00

Select T1.UserID, T1.clockYear, T1.startTime, T1.EndTime
from TimeCardEntries T1 AND PAYPERIODS
Where T1.StartTime between PAYPERIODS.DateStart AND PAYPERIODS.EndTime

Desired Output:
3,2017,2017-01-16 11:00,2017-01-16 13:00
3,2017,2017-01-16 11:00,2017-01-16 13:00
9,2017,2017-01-18 11:00,2017-01-18 13:00
5,2017,2017-01-20 11:00,2017-01-20 13:00
5,2017,2017-01-20 11:00,2017-01-20 13:00
11,2017,2017-01-21 11:00,2017-01-21 13:00

I hope this helps. Thank you

4

Hopefully you also have an ORDER BY on that, so that the result is "reproducible"?

5

You query is extremely close already:

Select T1.UserID, T1.clockYear, T1.startTime, T1.EndTime 
from TimeCardEntries T1 
inner join PAYPERIODS on 
    T1.StartTime >= PAYPERIODS.DateStart AND 
    T1.StartTime < DATEADD(DAY, 1, PAYPERIODS.DateEnd)
1 Like
6

That worked! Thank you