0

I would like to count number of records with week number of 27.

SELECT count(*)
FROM booker.o 
WHERE assigned_to_group 
IN ( 'Writing' ) 
AND create_date > '2020-11-08'
AND (DatePart(week, resolved_date) - 1)  = '27'
AND short_description = %corrective%`

Function: (DatePart(week, resolved_date) - 1) convert given date to week number. Example of short_description columns: SP-ADDITIONAL CORRECTIVE ND - EU -Research

I would like to extract the count of records only with week 27 and with a description that includes words: , corrective".

The number of records does not equal with records from the excel file but they should. So something must be wrong with my syntax. Also column status as a filter: resolved and closed. When I use my syntax all , closed" records disappear. Have you know any tips/solutions because I have no ideas. Thank you in advance.

Excel screen below:

View of table first column in excel

L_J
  • 2,351
  • 10
  • 23
  • 28
nynek
  • 29
  • 4
  • 1
    Are you aware of MySql's [week fucntions](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_weekofyear)? – O. Jones Jul 19 '21 at 09:39
  • And that you need to wrap text data in quotes? – RiggsFolly Jul 19 '21 at 09:41
  • And the use of `%` would normally be used with the `LIKE` clause, not the `=` – RiggsFolly Jul 19 '21 at 09:41
  • Week from fucntion: (DatePart(week, resolved_date) - 1) is extracted correctly and it's compliant with excel filewhere the data is correct. – nynek Jul 19 '21 at 09:41
  • Please share more details, like the table structure, sample data, and the expected result. `AND short_description = %corrective%` does not look like valid SQL – Nico Haase Jul 19 '21 at 09:41
  • @RiggsFolly it does not work with LIKE anstead = also – nynek Jul 19 '21 at 09:43
  • If your Excel file is the ultimate correct source of data, then probably it makes sense to add week number as a separate stored fields and not calculate it? – slkorolev Jul 19 '21 at 09:43
  • HINT: `AND short_description LIKE '%corrective%'` – RiggsFolly Jul 19 '21 at 09:44
  • Does MySql have a DatePart function? Also, if it does, does it return a string, or an integer? Maybe you need to compare to `27` and not `'27'` – Mr Moose Jul 19 '21 at 09:47

0 Answers0