11 Replies Latest reply on Jan 21, 2019 4:41 PM by Shinichiro Murakami

# find beginning/start of week by any given date (Use in Join)

Hello,

Table1: I have a range of Daily Dates.(work date in Image)Example, 01/06/2019, 01/07/2019, 01/08/2019, 01/09/2019,01/10/2019,01/11/2019,01/12/2019,

Table 2: I need to join it to another table that has only Week Starting dates 01/06/2019.For week (01/06/2019-01/12/2019)

I need to join these two tables at the connection Level.

Can I create a calculated field for Table1 that would help me join them????

Thank you.

• ###### 1. Re: Date Calculated field at Join

if date is between 1.6.2019-1.12.2019 it should display 1.6.2019 in the calculated field.

### find beginning/start of week by any given date

• ###### 3. Re: find beginning/start of week by any given date (Use in Join)

Found a thread that helped with the same.

This Worked: DATETRUNC('week', [workdate],'Sunday' )

Thank you!

• ###### 4. Re: Date Calculated field at Join

Not quite sure, but I guess you don't need this join provided you use Sunday(or any specific day of week) as week starts.

Thanks,

Shin

• ###### 5. Re: find beginning/start of week by any given date (Use in Join)

Hi Shinichiro,

But I a joining my date on 2 conditions (if the Employee_ID is same AND Week Ending Date is same.

How Can I join these tables then?

• ###### 6. Re: find beginning/start of week by any given date (Use in Join)

It's way easier if you could attach two tables, anyways.

Thanks,

Shin

• ###### 7. Re: find beginning/start of week by any given date (Use in Join)

Hi Shinichiro,

I have 2 tables to join:

Table 1:

Table 2:

(Converting Date to Week Start Date - to match Table1) - DATETRUNC('week', [workdate],'Sunday' )

The Table 1 is the primary table.

After Join I want results for all the entries Where Wk Ending Date=Date and EmpId=EmpID.

So the results for week

1/6/2019 -1/12/2019 should be null. As the emp is different.

I have attached Workbook in the Original Post.

Thank you for helping in advance.

• ###### 8. Re: find beginning/start of week by any given date (Use in Join)

Thank you very much!!!!!!! Sending you lots of God Blessings. Hope you have a great day. You;re the best.

• ###### 9. Re: find beginning/start of week by any given date (Use in Join)

You are welcome.

Shin

• ###### 10. Re: find beginning/start of week by any given date (Use in Join)

Any Idea, how can I change Week End Date as Week Start date?

• ###### 11. Re: find beginning/start of week by any given date (Use in Join)

Remove "  +6,'saturday'   "

Assuming week Starts Sunday.

Shin

1 of 1 people found this helpful