CODEWITHSUNDEEP
sqlsqlite
huy
huy

Reputation: 1904

Cannot get the row that contain the last day of each month

I am quite new to SQL and I am trying to find the row that contains the last day of each month.

product_table example:

log_date   | product_id | stock
10/30/2018 | 1001       | 59
10/29/2018 | 1002       | 100
10/28/2018 | 1003       | 2
...
9/30/2018  | 1001       | 1
9/30/2018  | 1002       | 45

This is my code:

SELECT *
FROM product_table
WHERE log_date IN 
(
    SELECT MAX(log_date)
    FROM product_table
    GROUP BY strftime('%m', log_date), strftime('%y', log_date)
)

Output:

9/9/2018    1001    28
9/9/2018    1002    94
9/9/2018    1003    29
9/9/2018    1004    89
9/9/2018    1005    3
9/9/2018    1006    46
...

Expected output:

9/30/2018   1001    28
9/30/2018   1002    94
9/30/2018   1003    29
...
8/31/2018   1001    89
8/31/2018   1002    3
...
7/31/2018   1001    46
...

I am working on a data file that date is a format like this: mm/dd/yyyy.

Should I change the date format to the normal way like yyyy-mm-dd because the code above returns the wrong result?

Do you guys know how to fix this? Thank you.

Upvotes: 1

Views: 56

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

If you would like to get last day in a month based on log_date field, You have to use date function

Compute the last day of the current month.

SELECT date('now','start of month','+1 month','-1 day');

So, to get last day of current month, use:

SELECT T.log_date, date(substr(log_date, 6+T.noofdays, 4) || '-' || substr('00' || substr(log_date, 1, 1+T.noofdays), -2) || '-' || substr(log_date, 3+T.noofdays, 2), 'start of month', '1 month', '-1 day') AS lastdayofmonth
FROM (
  SELECT log_date, instr(log_date, '/')-2 as noofdays
  FROM product_table
) AS T;

DbFiddle

You can also use datetime function in the same way.

[EDIT]

Accorgingly to the discusssion in comment with @forpas (Thank you for your valuable comments)...

If 'last day of month' means 'last day of month existsing in a table', then MAX(log_date) should do the job. But if, you want to get 'last day of month' even if there's no corresponding date in a table, above query shows how to achieve that.

Good luck!

Upvotes: 0

forpas
forpas

Reputation: 164099

Update your table so the dates have the format YYYY-MM-DD which is the only valid date format for SQLite:

update product_table
set log_date = 
  substr(log_date, -4) || '-' ||
  case 
    when log_date like '__/__/____' then
      substr(log_date, 1, 2) || '-' || substr(log_date, 4, 2)
    when log_date like '_/__/____' then 
      '0' || substr(log_date, 1, 1) || '-' || substr(log_date, 3, 2)
    when log_date like '__/_/____' then 
      substr(log_date, 1, 2) || '-0' || substr(log_date, 4, 1)
    when log_date like '_/_/____' then 
      '0' || substr(log_date, 1, 1) || '-0' || substr(log_date, 3, 1)
  end;

Then your query should work.
This is a simplification of the GROUP BY clause:

SELECT *
FROM product_table
WHERE log_date IN 
(
    SELECT MAX(log_date)
    FROM product_table
    GROUP BY strftime('%Y%m', log_date)
)

See the demo.
Results:

| log_date   | product_id | stock |
| ---------- | ---------- | ----- |
| 2018-10-30 | 1001       | 59    |
| 2018-09-30 | 1001       | 1     |
| 2018-09-30 | 1002       | 45    |

Upvotes: 2

Related Questions