Code: Select all
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)
SELECT x FROM cnt;
Moderator: Renato
Code: Select all
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)
SELECT x FROM cnt;
Code: Select all
with c AS (
select c.CATEGID, -1 SUBCATEGID, c.CATEGNAME as CATEGORY
from CATEGORY_V1 c
union all
select c.CATEGID, s.SUBCATEGID
, c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
from CATEGORY_V1 c, SUBCATEGORY_V1 s
where c.CATEGID = s.CATEGID
order by CATEGORY
),
a AS (select a.ACCOUNTNAME, a.ACCOUNTID, c.CURRENCYID, c.CURRENCY_SYMBOL
from ACCOUNTLIST_V1 a, CURRENCYFORMATS_V1 c
where a.CURRENCYID=c.CURRENCYID
),
t AS (select t.ACCOUNTID, t.TRANSDATE , t.transcode, t.PAYEEID
, case ifnull(t.categid, -1) when -1 then s.categid else t.categid end categid
, case when ifnull(t.categid, -1) = -1 AND ifnull(t.subcategid, -1) = -1 then s.subcategid else t.subcategid end subcategid
, (case ifnull(s.splittransid, -1) when -1 then t.transamount else s.splittransamount end)
*(case t.transcode when 'Deposit' then 1 else -1 end) as Amount
from checkingaccount_v1 t
LEFT JOIN splittransactions_v1 s ON s.transid=t.transid
)
select c.CATEGORY Category
, total(t.AMOUNT) Amount
, '' Total
, t.CATEGID
from t
inner join a on a.ACCOUNTID=t.ACCOUNTID
left join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
where t.TRANSDATE >=date('now','start of month')
and t.TRANSDATE <date('now','start of month','+1 month')
and a.CURRENCY_SYMBOL in ('USD')
and c.CATEGORY not in ('Transfer')
group by t.CATEGID, t.SUBCATEGID
order by Category
Code: Select all
with c AS (
select c.CATEGID, -1 SUBCATEGID, c.CATEGNAME as CATEGORY
from CATEGORY_V1 c
union all
select c.CATEGID, s.SUBCATEGID
, c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
from CATEGORY_V1 c inner join SUBCATEGORY_V1 s using ( CATEGID )
order by CATEGORY
),
a AS (select a.ACCOUNTNAME, a.ACCOUNTID, c.CURRENCYID, c.CURRENCY_SYMBOL
from ACCOUNTLIST_V1 a inner join CURRENCYFORMATS_V1 c using ( CURRENCYID )
),
t AS (select t.ACCOUNTID, t.TRANSDATE , t.TRANSCODE, t.PAYEEID
, case ifnull(t.CATEGID, -1) when -1 then s.CATEGID else t.CATEGID end Categid
, case when ifnull(t.CATEGID, -1) = -1 AND ifnull(t.SUBCATEGID, -1) = -1 then s.SUBCATEGID else t.SUBCATEGID end Subcategid
, (case ifnull(s.SPLITTRANSID, -1) when -1 then t.TRANSAMOUNT else s.SPLITTRANSAMOUNT end)
*(case t.TRANSCODE when 'Deposit' then 1 else -1 end) as Amount
from CHECKINGACCOUNT_V1 t
left join SPLITTRANSACTIONS_V1 s using ( TRANSID ) -- ON s.TRANSID=t.TRANSID
)
select c.CATEGORY Category
, total(t.AMOUNT) Amount
, '' Total
, min(t.TRANSDATE) von
, max(t.TRANSDATE) bis
, t.CATEGID
from t
inner join a using ( ACCOUNTID ) -- on a.ACCOUNTID=t.ACCOUNTID
left join c using ( CATEGID, SUBCATEGID ) -- on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
where t.TRANSDATE >=date('now','start of month','-2 month')
and t.TRANSDATE <date('now','start of month', '-1 month')
--and a.CURRENCY_SYMBOL in ('USD') -- mainly using EUR
and c.CATEGORY not in ('Transfer')
group by t.CATEGID, t.SUBCATEGID
order by -1*sign(total(t.AMOUNT)),Category;