Scenario
At our office we track our time in the following format:
DATE | EMPL | HR | COMPANY
-----------|------|-----|--------
09-26-2018 | John | 2 | ABC
09-26-2018 | John | 2 | DEF
09-26-2018 | Jane | 1.5 | ABC
09-25-2018 | John | 1 | ABC
09-25-2018 | Jane | 1.5 | DEF
Etc, etc.
I have a query that figures out everyone's total hours worked for each company, but I have to manually put in the rows that correspond to the pay period, eg. SELECT(A2:E30). A pay period is from the 1–15 or 16–3x (last day of month).
Question
How can I write a query that looks at only the first group of rows that fit into a date of 1–15 or 16–3x?
Best Answer
The key to a solution is to get the day value from the date so that it can be evaluated:
<=15 ( = 1-15) or >15 ( = 16-3x).
This part is pretty easy. Use the
DAYfunction to return an integer value for the "day".That leave two issues:
1 - Do we want the first half of the month, or the 2nd half?
2 - What statement do we use to actually get the rows?
Which half of the month?
I suggest a dropdown list to use in conjunction with an IF statement. If the dropdown = "1st half", then return the data using criterion of day <=15, otherwise day>15.
Getting the rows I suggest either
FILTERorQUERY.FILTER
If the dropdown list (cell B1) = "1st half" then filter the payroll data (columns A to D) using the day component of the date as the criterion (day<=15), otherwise use the same criterion but a different value (day>15 = "2nd half").
QUERY
Query the payroll data (columns A to D); if the dropdown (cell B1) = "1st half, then return data for the 1st to the 15 of the month; otherwise, return the data for the 16-3x of the month.
Apart from syntax, the main difference between the two is that QUERY returns column headings, whereas FILTER requires you do create your own headings.
Here's how the results looks on the screen
Here's a link to a spreadsheet
PS:
I've deliberately sized the payroll data as "infinite" by declaring column D without a row number. That adds greatly to the overheads consumed by these formulae. In addition, my strictly informal observation is that the QUERY updates fairly quickly, but the FILTER takes longer. Of course, I've got both running in the same spreadsheet so maybe that has something to do with it. But you might care toi experiment by specifying an end row (albeit bigger than you might need), and also testing with your own data which of QUERY or FILTER updates more quickly.
Supplementary
You asked why this formula is giving problems.
These comments are offered on the basis that I closer to being an apprentice than an expert when it comes to SQL. Give that proviso...
1 - database range = "A6:D". But this excludes the header row. The range needs to include the header, it should be "A5:D".
2 - There is a missing "space" that separates the segments of the Select statement. In this example, the Select statement has two segments that are joined by "&". The second segment is dependant on the evaluation of the IF statement. When the segments are combined, there needs to be at least one space between them so that the language of the segments doesn't run together. This is probably better resolved by adding &" "& into the formula.
3 - FWIW, by focusing specifically on the value of cell A6, you can never run a report for the first half of the month. Unless of course, the data in the payroll range only ever consists of entries for the first half OR the second half of the month.
4 - SQL syntax - this is an area where you would do well to bone up on SQL. Google provides a useful Query Language Reference
4-1-1 As soon as your Select includes aggregation (Sum, Count, etc) and/or grouping, you can't include the date (A) in the results.
4-1-2 "Group By" should appear at the end of the Select statement. In this example, the WHERE segment follows the initial segment, and this makes the formula invalid.
After editing to reflect these comments, this is the new formula.
This is a screenshot of the output of the formula.
