T-SQL Query Through ODBC--Not All Data is "There"

Hello:

Attached is an Excel spreadsheet whose data I access within a separate app and through an ODBC connection to the spreadsheet.

I need to build T-SQL syntax (i.e. select * from) to accommodate the fact that the Employee and Employee ID columns have blanks.  For instance, for Frank Balsamo, you can tell that he has seven courses.  But, how do I get the ODBC connection to "understand" that?

In other words, I guess I need to use PARTITION BY or something like that within the T-SQL syntax in order to "fill in" the Employee and Employee ID fields.  That way, the ODBC connection will know not to leave blanks in those columns.

I hope that makes sense.

Anyway, can you all please help me with the T-SQL syntax on this?

Please, also, let me know if you have any questions.

Thanks!  Much appreciated!

TBSupport
Book2.xlsx
LVL 1
TBSupportAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Try this..
JOINing on the ID's and not the names
Prefixed every column in the SELECT clause
SELECT a.Employee, a.EmployeeID, b.ClassCourseID, b.CompletedDate, b.ExpirationDate, b.Completed
FROM [EmployeeTrainingCourses$] b
   JOIN (SELECT DISTINCT Employee, EmployeeID FROM [EmployeeTrainingCourses$]) a 
ON b.EmployeeID = a.EmployeeID

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>through an ODBC connection to the spreadsheet.
Sounds like the spreadsheet is eliminating these values when they duplicate with what's above.  
Since the connection is only a connection, and does not alter data, that's what you get.

I propose you build a view based on this table, that goes something like this..
SELECT n.Employee, n.EmployeeID, ClassCourse ID, CompletedDate, ExpirationDate, Completed
FROM YourTable yt
   JOIN (SELECT DISTINCT Employee, EmployeeID FROM YourTable) n ON yt.Employee = n.Employee

Open in new window

0
 
TBSupportAuthor Commented:
Thanks!

I slightly modified your query, and the app is saying that there is a syntax error in the "FROM" clause.  I don't see any issues, though, do you?  Incidentally, EmployeeTrainingCourses$ is the tab in the Excel workbook that I'm connecting to.

Below is the revised query.

TBSupport

SELECT a.Employee, a.EmployeeID, ClassCourseID, CompletedDate, ExpirationDate, Completed
FROM [EmployeeTrainingCourses$] b
   JOIN (SELECT DISTINCT Employee, EmployeeID FROM [EmployeeTrainingCourses$]) a
ON b.Employee = a.Employee
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, the spreadsheet had a lot of spaces in the column names, so you'll want to eyeball these and make sure it's correct.  T-SQL can't handle spaces in object names, so whenever you see then you'll need square brackets surrounging them.

e.g. EmployeeName is okay, Employee{space}Name is not, [Employee Name] is ok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.