1

I have MySQL with a date column and system_week_end date column. The system_week_end column has the situation of one or two week missing values.

Note

  • the system_week_end can be any day of the week. I am not able to the end std end of week function
  • the system_week_end is subset filtered by a column company each company set their the system_week_end date.
  • the date column contains every day, no missing values.

Here is an example of the data:

date            system_week_end
2020-01-05      2020-01-05
2020-01-04      <null>
2020-01-03      <null>
2020-01-02      <null>
2020-01-01      <null>
2019-12-31      <null>
2019-12-30      <null>
2019-12-29      <null>
2019-12-28      <null>
2019-12-27      <null>
2019-12-26      <null>
2019-12-25      <null>
2019-12-24      <null>
2019-12-23      <null>
2019-12-22      2019-12-22
2019-12-21      <null>
2019-12-20      <null>
2019-12-19      <null>
2019-12-18      <null>
2019-12-17      <null>
2019-12-16      <null>
2019-12-15      2019-12-15

How would I structure a MySQL query to infill null for the system_week_end column?

For example:

2020-01-05      2020-01-05
2020-01-04      2020-01-05
2020-01-03      2020-01-05
2020-01-02      2020-01-05
2020-01-01      2020-01-05
2019-12-31      2020-01-05
2019-12-30      2020-01-05
2019-12-29      2019-12-29
2019-12-28      2019-12-29
2019-12-27      2019-12-29
2019-12-26      2019-12-29
2019-12-25      2019-12-29
2019-12-24      2019-12-29
2019-12-23      2019-12-29
2019-12-22      2019-12-22
2019-12-21      2019-12-22
2019-12-20      2019-12-22
2019-12-19      2019-12-22
2019-12-18      2019-12-22
2019-12-17      2019-12-22
2019-12-16      2019-12-22
2019-12-15      2019-12-15
Adam
  • 473
  • 5
  • 21
  • Sorry its not clear what you are asking. Maybe add an example of what you want to happen to these rows after you do whatever it is – RiggsFolly Jan 13 '20 at 22:04
  • It's not that difficult to find the end-of-week date for a given date. Google will help you. – PM 77-1 Jan 13 '20 at 22:04
  • The system_week_end can be any weekday and therefore I am not able to the end std end of week function. – Adam Jan 13 '20 at 22:10
  • So for each and every date which has NULL into corresponding `system_week_end` you want its end of week date? – mitkosoft Jan 13 '20 at 22:10
  • @mitkosoft correct. `system_weekend_date` is not always calendar week end. This data is a subset using `where company = ...` – Adam Jan 13 '20 at 22:13
  • Only this row is unclear for me: `2019-12-29 -> 2020-01-05` - why you want this `system_week_end` value on this row when 12/29 itself is the end of the week? – mitkosoft Jan 13 '20 at 22:13
  • @mitkosoft copy-paste error sorry. – Adam Jan 13 '20 at 22:15
  • My question would be: why you storing all dates and how you use them? I would not do that. I'd store the system_week_end values only. You can [auto generate list of dates](https://stackoverflow.com/questions/2157282/generate-days-from-date-range/45951982) when required. – fifonik Jan 13 '20 at 23:35
  • Also, from your updated question it is not clear how the system supposed to get the 2019-12-29 as the date is not mentioned in original tables and you saying that it would not be calculated based on std end of week functions. – fifonik Jan 13 '20 at 23:37

1 Answers1

2

This should update missing dates with the respective end of the week dates:

UPDATE dateTable 
SET system_week_end = DATE_ADD(`date`, INTERVAL 6 - WEEKDAY(`date`) DAY)
WHERE `system_week_end` is NULL
mitkosoft
  • 5,262
  • 1
  • 13
  • 31
  • 1
    Why '-1'? It returns Saturday then. Also, what is the reason for two DATE_ADD? ```DATE_ADD(@d, INTERVAL 6 - WEEKDAY(@d) DAY)``` should do the trick. – fifonik Jan 13 '20 at 22:38
  • What happens if the `system_week_end` is a Wednesday? I know fill next 6 rows then add one week repeat. – Adam Jan 13 '20 at 22:51
  • @Adam, from your original question it's not clear what should be the behavior depending on `system_week_end` value(s). Could you please update the description and desired result if there is a dependency from `system_week_end` date/day, especially the example part? – mitkosoft Jan 13 '20 at 22:54
  • 2
    @Adam: this answer **[does what you want](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2d82eb81a1de16c305411d8a84794e34)**. – GMB Jan 13 '20 at 23:11