As shown in figure: the same card number, OPERATION type (OPERATION=18), consumption (OPERATION=15), a return (OPERATION=1), I want to check on the balance of a point in time (namely virtulmoney accumulative value add and subtract, balance formula is: giving comprehensive consumer comprehensive + - returns the sum)
Such as I want to check: card number is 6015902 on October 31, how much is the balance, how to write,? thank you

CodePudding user response:

SELECT SUM (DECODE (OPERATION, 18, VIRTUALMONEY, 0)) -
The SUM (DECODE (OPERATION, 15, VIRTUALMONEY, 0)) +
The SUM (DECODE (VIRTUALMONEY OPERATION, 1, 0))
The FROM TABLE_NAME
WHERE CARDNO='6015902'
AND OPDATE BETWEEN TO_DATE (' 20191031000000 ', 'YYYYMMDDHH24MISS') AND
TO_DATE (' 20191031235959 ', 'YYYYMMDDHH24MISS');

CodePudding user response:

Thank you admire, seems to be very practical oh

CodePudding user response:

Can remove the specific card number, screen out all of the card number, card number group, as long as to give a time, you can find out this point in time the balance of all the CARDS

CodePudding user response:

The SELECT CARDNO, SUM (DECODE (OPERATION, 18, VIRTUALMONEY, 0)) -
The SUM (DECODE (OPERATION, 15, VIRTUALMONEY, 0)) +
The SUM (DECODE (VIRTUALMONEY OPERATION, 1, 0))
The FROM TABLE_NAME
WHERE 1=1
AND OPDATE BETWEEN TO_DATE (' 20191031000000 ', 'YYYYMMDDHH24MISS') AND
TO_DATE (' 20191031235959 ', 'YYYYMMDDHH24MISS')
Group by CARDNO.

CodePudding user response:

The SELECT CARDNO,
The SUM (DECODE (OPERATION, 18, VIRTUALMONEY, 0)) -
The SUM (DECODE (OPERATION, 15, VIRTUALMONEY, 0)) +
The SUM (DECODE (VIRTUALMONEY OPERATION, 1, 0))
The FROM TABLE_NAME
WHERE OPDATE BETWEEN TO_DATE (' 20191031000000 ', 'YYYYMMDDHH24MISS') AND
TO_DATE (' 20191031235959 ', 'YYYYMMDDHH24MISS')
GROUP BY CARDNO.