I am new to sqlite3.I am currently using mysql. But I will be migrating it to sqlite3.

I am calculating month end balance

SELECT c.country,
       Date_format(Last_day(Str_to_date(dt.date, '%m/%d/%Y')), '%Y/%m/%d')
                                          AS Month_End_Balance,
       Sum(dt.amount) AS in_Euro
FROM   deposit_transactions AS dt
       LEFT JOIN customers AS c
              ON c.customer_id = dt.customer_id
GROUP  BY c.country,
          Month_End_Balance 
order by Month_End_Balance desc

Need help in converting it to sqlite

Question

1. Need help in Last_Day function alternative in sqlite

Db-Fiddle-

CodePudding user response：

Refer below query for SQLITE - Please make adjustment as per needed date format.

with new_dep_trx as (
select deposit_id,customer_id,
  transaction_type,amount,currency,
case when (length(dt.date)=8 or length(dt.date)=9) and instr(substr(dt.date,1,2),'/')>0
then 
date(substr(dt.date,length(dt.date)-3,4)||'-0'||substr(dt.date,1,1)||'-0'||substr(dt.date,3,1))
when length(dt.date)=9 and instr(substr(dt.date,1,2),'/')=0
then
date(substr(dt.date,length(dt.date)-3,4)||'-'||substr(dt.date,1,2)||'-0'||substr(dt.date,4,1))
else
date(substr(dt.date,length(dt.date)-3,4)||'-'||substr(dt.date,1,2)||'-'||substr(dt.date,4,2)) end date_col
from deposit_transactions dt
  )
 select 
 c.country,
 strftime('%Y-%m',dt.date_col) as month,
strftime('%m/%d/%Y',date(dt.date_col,'start of month',' 1 month','-1 day')) as last_day_of_month,
strftime('%Y/%m/%d',date(dt.date_col,'start of month',' 1 month','-1 day')) as last_day_of_month_your_format,
    SUM(
        dt.amount *
        (CASE WHEN dt.currency = 'GBP' THEN .85 ELSE 1 END) *
        (CASE WHEN dt.transaction_type = 'pay_in' THEN 1 ELSE -1 END)
    ) amount_eur
FROM new_dep_trx dt
LEFT JOIN customers c ON c.customer_id = dt.customer_id
GROUP BY c.country, last_day_of_month_your_format;

Modified DB fiddle.

CodePudding user response：

First, you must update the column date of the table deposit_transactions so that it has the format YYYY-mm-dd the only text date format that you can use with SQLite's datetime functions:

UPDATE deposit_transactions
SET date = SUBSTR(date, -4) || '-' ||
           printf('d', date   0) || '-' ||
           printf('d', SUBSTR(date, INSTR(date, '/')   1, 2)   0);

Now, you can use the function date() to get the last day of each month with:

date(date, 'start of month', ' 1 month', '-1 day')

So, your query should be:

SELECT c.country,
       date(dt.date, 'start of month', ' 1 month', '-1 day') AS Month_End_Balance,
       SUM(dt.amount *
           CASE WHEN dt.currency = 'GBP' THEN .85 ELSE 1 END *
           CASE WHEN dt.transaction_type = 'pay_in' THEN 1 ELSE -1 END
       ) AS amount_eur
FROM deposit_transactions AS dt LEFT JOIN customers AS c
ON c.customer_id = dt.customer_id
GROUP BY c.country, Month_End_Balance
ORDER BY Month_End_Balance DESC;

If you want to format the dates of the resultset to mm/dd/YYYY and sort properly by the date and not the formatted date which would sort incorrectly:

SELECT c.country,
       strftime('%m/%d/%Y', date(dt.date, 'start of month', ' 1 month', '-1 day')) AS Month_End_Balance,
       SUM(dt.amount *
       CASE WHEN dt.currency = 'GBP' THEN .85 ELSE 1 END *
       CASE WHEN dt.transaction_type = 'pay_in' THEN 1 ELSE -1 END
       ) AS amount_eur
FROM deposit_transactions AS dt LEFT JOIN customers AS c
ON c.customer_id = dt.customer_id
GROUP BY c.country, date(dt.date, 'start of month', ' 1 month', '-1 day')
ORDER BY date(dt.date, 'start of month', ' 1 month', '-1 day') DESC;

See the demo.