Can someone please point me in the right direction...

I have the following two tables....

tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo

tblWagesWeeks
-weekID
-weekDay
-date

I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.

It's better to add WeekID to tblTimeEntry and use it in the join condition. But, still you can use the follow code to achieve the result:

SELECT *
FROM tblWagesWeeks AS a
LEFT JOIN tblTimeEntry AS b 
ON DATEPART(YEAR, a.[date]) = DATEPART(YEAR, b.entryDate)
AND DATEPART(WEEK, a.[date]) = DATEPART(WEEK, b.entryDate)

Can you provide us with DDL and example data as well as an expected outcome?

I'm assuming that the date in tblWagesWeeks matches tblTimeEntry's entry

SELECT [Date], WeekDay,
       entryWeekNo, entryUser, entryJob, entryTask
FROM tblWagesWeeks AS a
LEFT JOIN tblTimeEntry AS b ON a.[date] = b.entryDate

You should always try to avoid placing any type of function or conversion on predicate columns used in the WHERE or JOINs.

In other words you want to avoid this type of thing: "ON DATEPART(YEAR, a.[date]) = DATEPART(YEAR, b.entryDate) AND DATEPART(WEEK, a.[date]) = DATEPART(WEEK, b.entryDate)" 

It's also best to avoid using "SELECT *"

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

• Edited by Daniel Janik 10 hours 5 minutes ago

You should always try to avoid placing any type of function or conversion on predicate columns used in the WHERE or JOINs. 
In other words you want to avoid this type of thing: 
"ON DATEPART(YEAR, a.[date]) = DATEPART(YEAR, b.entryDate) AND DATEPART(WEEK, a.[date]) = DATEPART(WEEK, b.entryDate)" 

Its also best to avoid using "SELECT *"

Specifically, the reason for avoiding " SELECT * " is because unless you can guarantee that the underlying tables will never change, it is Best Practice to always type each column you need. I've heard it described as the Bus Rule: If you get hit by a bus, you want your code to be easy to understand by another programmer.

As such, it is expedient for programmers if they don't have to guess what data is being extracted from which table. Therefore, it is also Best Practice to include the master database name your data is in located in (USE <databasename>), as well as name the SCHEMA of your tables ensure polymorphism throughout the query

(SELECT DATE FROM dbo.tblWagesWeeks) as opposed to (SELECT DATE FROM tblWagesWeeks).

The great part about doing this is that even if the company you work for has an office full of idiots who ignore normalization rules everywhere by not differentiating tables in different SCHEMAs, another programmer will still be able to execute your queries without fear of unknown problems (and undoubtedly will bless your name, too!).

• Edited by Conquistador0 1 hour 34 minutes ago For clarity

>> I have the following two tables.... <<

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells- That silly tbl- prefix is a design flaw called a tibble. Phil Factor has written humor pieces on this. 

The table name prefix is also version of the same disaster, namely mixing data and meta data. Noobs do this to make the data element names look like COBOL, PL/I or another procedural language they know.  

You have a week_id and a week_nbr, neither of which make any sense in RDBMS. A week is a unit of measurement on a Calendar and it has a definiton in the ISO-8601 Standards. We would have <something in particular>_week in RDBMS.

Want to try again and follow the rules? They are posted at the start of the forum. 

--CELKO-- wrote:

>> I have the following two tables.... <<

"Please follow basic Netiquette and post the DDL we need to answer this. "...

..."The table name prefix is also version of the same disaster, namely mixing data and meta data. Noobs do this to make the data element names look like COBOL, PL/I or another procedural language they know"...

..."Want to try again and follow the rules? They are posted at the start of the forum. "

A) His data sets don't necessarily require temporal data sets to work with and as for DDL, he already had the original data formatted into VARCHAR. In fact, most of your posting is a non-sequitur since conceptually the only thing that matters right now is deciding high-level how he wants to develop the algorithm.

B) There is no official supposed rules (I checked) for how you post something, so Netiquette would tell you to get off your high horse and actually address a core issue you think he has (and there are a few). If you didn't notice, he did ask to be pointed in the right direction not told off for not following whatever forum rules you think exists.

Additionally, ISOs are the collective recognition of similar standards made into one homogeneous standard for utilization across geographical and electronic boundaries. In other words, just because there is an ISO does not necessarily make it Best Practice. Let's remember this forum is for learning, not berating others for their lack of knowledge.

• Edited by Conquistador0 1 hour 29 minutes ago