Skip to content

Instantly share code, notes, and snippets.

@stasgm
Last active January 19, 2018 20:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stasgm/d66fdb60c1d59d61cf1dc76a4854d18a to your computer and use it in GitHub Desktop.
Save stasgm/d66fdb60c1d59d61cf1dc76a4854d18a to your computer and use it in GitHub Desktop.
SQL. Get remains on a specified date
SELECT
g.name goodname,
v.name valname,
con.name depname,
sum(m.balance)
FROM
( SELECT
M.CARDKEY,
M.CONTACTKEY,
M.BALANCE
FROM
INV_BALANCE M
WHERE
M.BALANCE <> 0
and M.CONTACTKEY = :depkey
UNION ALL
SELECT
M.CARDKEY,
M.CONTACTKEY,
SUM ( M.CREDIT - M.DEBIT ) AS BALANCE
FROM
INV_MOVEMENT M
WHERE
M.DISABLED = 0
AND
M.MOVEMENTDATE > :REMAINSDATE
and M.CONTACTKEY = :depkey
GROUP BY
1,
2 ) M
JOIN INV_CARD C ON C.ID = M.CARDKEY
JOIN GD_GOOD G ON G.ID = C.GOODKEY
JOIN GD_CONTACT CON ON M.CONTACTKEY = CON.ID
JOIN GD_VALUE V ON V.ID = G.VALUEKEY
WHERE
M.BALANCE <> 0
GROUP BY
1, 2, 3
HAVING
SUM ( M.BALANCE ) > 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment