SQL samples

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

SQL samples

Post by Nikolay »

Just interesting SQL query:

Code: Select all

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)
SELECT x FROM cnt;
URL
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: SQL samples

Post by Nikolay »

Categories total current month for UDS only excluded transfers

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
astern
New MMEX User
Posts: 6
Joined: Wed Aug 31, 2016 9:37 pm
Are you a spam bot?: No
Location: Austria

Re: SQL samples

Post by astern »

why not avoid all those ugly where clauses, and use the using clause instead of on in joins?

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;

Post Reply