1

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?

1

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 DAY function 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 FILTER or QUERY.

FILTER

=if(B1="1st half",filter(A6:D,day(A6:A)<=15),filter(A6:D,day(A6:A)>15))

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(A5:D,"Select A,B,C,D " & IF(B1="1st half","WHERE day(A) <=15","WHERE day(A) >15"),1)

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 enter image description here

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.

=query(A6:D,"Select A,B,C,D, sum(C) group by B,D" & IF(day(A6)<=15,"WHERE day(A) <=15","WHERE day(A) >15"),1)

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.

=query(A5:D,"Select B,D, Sum(C) "&" "& IF(day(A6)<=15,"WHERE day(A) <=15"&" "&"Group By B,D ","WHERE day(A) >15"&" "&"Group By B,D "),1)

This is a screenshot of the output of the formula.
groupby screenshot

  • Clever, and very thorough! Thank you for that. I've accepted this as the answer and I have a followup question. Why am I have problems with =query(A6:D,"Select A,B,C,D, sum(C) group by B,D" & IF(day(A6)<=15,"WHERE day(A) <=15","WHERE day(A) >15"),1) ? – Jacksonkr Oct 8 '18 at 22:28
  • @Jacksonr I've edited my response to this followup. In the first instance, I inadvertently captured the date using a lookup. I realised my mistake and have updated the supplementary to show the evaluation of cell A6, just as you have it in your own formula. – Tedinoz Oct 9 '18 at 19:04
  • I applied this to my work sheet and I found that it doesn't account for day inside the same month, meaning that it will take <15 from ALL months rather than the most recent. Is it possible to do query logic for the using the same month from the first cell? I recreated my work scenario using your original sheet docs.google.com/spreadsheets/d/… – Jacksonkr Oct 10 '18 at 14:29
  • @Jackson5 Hmm. Quick test. =query(A5:D,"Select A,B,C,D " & IF(day(A6)<=15,"WHERE day(A) <=15 and Month(A)=7","WHERE day(A) >15 and Month(A)=7"),1) delivers August!. Change the month to 8 and you get September. Can't explain it at this stage. – Tedinoz Oct 10 '18 at 15:50
  • 1
    I think for the sake of having an extra field at the top of the sheet (where you enter the month you want and point your MONTH statement at that), you could spend a lot of time trying to split the atom. – Tedinoz Oct 13 '18 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.