[phyrrus9]

So, I wrote this query to run payroll for this period because I haven't finished the software implementation (which has a lot more features) yet for my client. Looking back on it, I realized that 5 years ago I would have had no idea that even half of these SQL features even existed, and wouldn't know how to use any of them beyond the basic query.

This query contains (what I'd consider non-beginner features):
2 variables
5 aliases
2 subqueries
1 case (C equivalent is the switch)
3 joins
2 groupings
2 aggregates

I also formatted this religiously. Let me know what you guys think.
fyi: this query, copuled with my database design, was able to run payroll for the entire company in 0 seconds using an azure database server



@Skullmeat and @mothered I know at least one of you does sql stuff

[mothered]

You've made very good use of the Subqueries, and I don't come across too many people using ALIASES as a temporary holder to denote Table/Column names In a more appropriate manner. Good work on that.

The TIMESTAMP data type will also do the job, however (not that It's relevant to our current UTC), It supports until the year 2038. As for the GROUP BY Clause/Statement with It's aggregate function (SUM), well that speaks for Itself.

A job well done.

[Mr.Kurd]

Thank you my friend, you have done a good job, What does "join" do?

[mothered]

What does "join" do?

The JOIN Clause does just that- joins columns (column rows) from separate tables, but the values need to be relative (matching) to each other.

In this case, the column names are [EmployeeRateId], [EmployeeId] and [RateTypeId].

[Mr.Kurd]

What does "join" do?

The JOIN Clause does just that- joins columns (column rows) from separate tables, but the values need to be relative (matching) to each other.

In this case, the column names are [EmployeeRateId], [EmployeeId] and [RateTypeId].

Is it like adding a new column? or adding a row?

[mothered]

What does "join" do?

The JOIN Clause does just that- joins columns (column rows) from separate tables, but the values need to be relative (matching) to each other.

In this case, the column names are [EmployeeRateId], [EmployeeId] and [RateTypeId].

Is it like adding a new column? or adding a row?

It's not adding a column as with the ADD COLUMN Statement, which you can add a single column (or multiple columns with the same line) to an existing table. You then use the INSERT INTO Statement to add values to that column. Or If you use both the DEFAULT and NOT NULL Constraints during the creation of the column, the default value will be provided hence no need to add a value to It.

With the INNER JOIN Clause, as long as the conditions are met (matching) between all tables, the column rows will be returned. The columns are what I've mentioned above. The tables are [EmployeeRate], [Employee] and [RateType].

[phyrrus9]

What does "join" do?

The JOIN Clause does just that- joins columns (column rows) from separate tables, but the values need to be relative (matching) to each other.

In this case, the column names are [EmployeeRateId], [EmployeeId] and [RateTypeId].

Is it like adding a new column? or adding a row?

We use join because some information is stored in other tables. In the timesheet table, we only store references to these datasets. For example, every timesheet entry has to come back to an employee, so we have to keep track of that employee. Rather than storing all of the employee information within the timesheet table, which would be a massive waste of space, we store the employee information once in the employee table and give it an ID. We then put that ID into the timesheet table and that's our reference.

When we need to get the first name of that employee, we use that ID to look it up. We can do this by just executing another query and manually filling in the blanks if we want, but we can get all that in one query by joining the employee table with the timesheet table, using the employeeid field that exists in both. The SQL server matches up the rows with the same employeeid field and creates a temporary table in memory where they literally just take the entire employee table and put it next to the timesheet table. We can then query for columns that exist in both the timesheet table, and the employee table, thus allowing us to get the person's name.

This sort of design is called relational databases. Here's an example in a graphic