Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
closing and opening balance SQL

I am using oraclexe and also PostgreSQL and require following data set (sql query) (to get inventory closing qty and amount at particular date) when running this query with
- conditions between idate '03-07-2021' to '04-07-2021',
- value for ref with 'OT#' wist to get with negative qty and amt.
- bal qty and amt before condition idate i.e (240,10000 in this example) want to be in the beginning of the result set.
CREATE TABLE myinv ( loc varchar(20) ,item varchar2(50) ,idate date ,ref1 varchar2(20) ,rt number(10,1),qty number(10,2), amt number(10,2)); INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) values ('A', 'IT1', to_Date('2021-07-01','yyyy-mm-dd'), 'IN5', 40, 160, 6400) / INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('A', 'IT1', to_Date('2021-07-02','yyyy-mm-dd'), 'IN6', 45, 80, 3600) / INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('', 'IT1', to_date('2021-07-03','yyyy-mm-dd'), 'OT2', 41.67, 80, 3333.6) / INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('A', 'IT1', to_Date('2021-07-04', 'yyyy-mm-dd'), 'IN7', 35, 40, 1400) / INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('', 'IT1', to_date('2021-07-04','yyyy-mm-dd'), 'OT3', 40.33, 100, 4033) / INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('', 'IT1', to_date('2021-07-04','yyyy-mm-dd'), 'OT4', 40.33, 40, 1613.2) /
i have try following query but unable to get result as shown above in "REQUIRED DATA SET"
SELECT item, idate,ref1, rt, lag(cast(closing_qty as int),1,0) over (partition by item order by idate) opening_qty, qty, closing_qty, lag(cast(closing_amt as int),1,0) over (partition by item order by idate) opening_amt, amt FROM ( select item, idate,ref1, rt,qty,amt, sum(qty) over (partition by item order by idate) closing_qty, sum(amt) over (partition by item order by idate) closing_amt from myinv where idate > to_date('20210702', 'yyyymmdd') ) t
Best Answer
-
Hi,
Assuming you want these results:
IDATE REF1 RT QTY AMT BAL_QTY BAL_AMT ---------- ------- -------- -------- -------- -------- -------- 240 10000 2021-07-03 OT2 41.67 -80 -3333.6 160 6666.4 2021-07-04 IN7 35 40 1400 200 8066.4 2021-07-04 OT4 40.33 -40 -1613.2 160 6453.2 2021-07-04 OT3 40.33 -100 -4033 60 2420.2
and that the combination (item, idate, eff_qty) is unique (where eff_qty is either qty or -qty, depending on ref1), you can do something like this:
WITH got_mplr AS ( SELECT TO_DATE ('2021-07-03', 'YYYY-MM-DD') AS start_date -- HARD-CODE starting date here , item, idate, ref1, rt, qty,amt , CASE WHEN SUBSTR (ref1, 1, 2) = 'OT' THEN -1 ELSE +1 END AS mplr FROM myinv WHERE idate < TO_DATE ('2021-07-04', 'YYYY-MM-DD') + 1 -- HARD-CODE ending date here ) , got_bal AS ( SELECT start_date, item, idate, ref1, rt , qty * mplr AS eff_qty , amt * mplr AS eff_amt , SUM (qty * mplr) OVER (PARTITION BY item ORDER BY idate, qty * mplr DESC) AS bal_qty , SUM (amt * mplr) OVER (PARTITION BY item ORDER BY idate, qty * mplr DESC) AS bal_amt , LEAD (idate, 1, idate) OVER (PARTITION BY item ORDER BY idate, qty * mplr DESC) AS next_idate , CASE WHEN idate >= start_date THEN idate END AS in_range FROM got_mplr ) SELECT in_range AS idate , NVL2 (in_range, ref1, NULL) AS ref1 , NVL2 (in_range, rt, NULL) AS rt , NVL2 (in_range, eff_qty, NULL) AS qty , NVL2 (in_range, eff_amt, NULL) AS amt , bal_qty , bal_amt FROM got_bal WHERE next_idate >= start_date ORDER BY item, idate NULLS FIRST, eff_qty DESC -- or whatever you want ;
If you want the results in the order that you posted them (rather than the order you described), then just change the analytic ORDER BY clauses.
Since the multiplier +1 or -1 (depending on whether rt1 starts with 'OT' or not) is needed in several places, the query above starts by computing it in a separate sub-query.
In order to get the correct cumulative SUM, we need to get results for all rows before the ending date. However, in the result set, we only want to display rows where idate is on or after start_date, plus the last row where idate is before start_date (if there is such a row). To find that extra row, the query above uses LEAD. Furthermore, on that extra row, most of the columns will be NULL. We could use CASE expressions for each of them, like this
CASE WHEN idate1 >= start_date THEN ref1 END AS ref1
but the query above uses NVL2 because it's more concise, which makes it easier to read, understand and debug.
Answers
-
Hi, @AQH
Would you like to get answers that work? Make sure the CREATE TABLE and INSERT statements you post work, too. Test (and, if necessary, fix) them before you post them.
Don't try to insert VARCHAR2s (such as'2021-07-01') into DATE columns. Use DATE literals or TO_DATE.
Avoid column names that require double-quotes. Since REF is an Oracle keyword, call your column something like REF_VAL or MY_REF.
-
Hi, @AQH
value for ref with 'OT#' wist to get with negative qty and amt.
Do you meant that when ref_val is three characters ('O', 'T' and a digit, in that order) then the values for qty and amt should be multiplied by -1?
When two (or more) rows with the same value for item have the same value for idate, how do you determine the order? In the example above, there are three rows with item='IT1' and idate as July 4, 2021. Which of those rows comes first, and which comes next? Do you want them in order by ref_val? What if there is still a tie (that is, two or more rows also have have the same ref_val)?
-
@Frank Kulash corrected as advice.
qty and amt will be negative when ref1 like 'OT%' mean when ref initial two character will be 'OT'.
when tow or more rows with same value for item have same value for idate, higher value qty will be come first.
-
Hi, @AQH
corrected as advice.
Thanks, but please don't ever change your messages after you post them: it makes the thread hard to read, and it makes your changes easy to miss. Post any corrections or additions in a new reply at the end of the thread.
when tow or more rows with same value for item have same value for idate, higher value qty will be come first.
Are you saying that the desired results you posted are wrong, and that the row with qty = -40 should come before the row with qty = -100, since -40 is grater than -100?
-
Hi,
Assuming you want these results:
IDATE REF1 RT QTY AMT BAL_QTY BAL_AMT ---------- ------- -------- -------- -------- -------- -------- 240 10000 2021-07-03 OT2 41.67 -80 -3333.6 160 6666.4 2021-07-04 IN7 35 40 1400 200 8066.4 2021-07-04 OT4 40.33 -40 -1613.2 160 6453.2 2021-07-04 OT3 40.33 -100 -4033 60 2420.2
and that the combination (item, idate, eff_qty) is unique (where eff_qty is either qty or -qty, depending on ref1), you can do something like this:
WITH got_mplr AS ( SELECT TO_DATE ('2021-07-03', 'YYYY-MM-DD') AS start_date -- HARD-CODE starting date here , item, idate, ref1, rt, qty,amt , CASE WHEN SUBSTR (ref1, 1, 2) = 'OT' THEN -1 ELSE +1 END AS mplr FROM myinv WHERE idate < TO_DATE ('2021-07-04', 'YYYY-MM-DD') + 1 -- HARD-CODE ending date here ) , got_bal AS ( SELECT start_date, item, idate, ref1, rt , qty * mplr AS eff_qty , amt * mplr AS eff_amt , SUM (qty * mplr) OVER (PARTITION BY item ORDER BY idate, qty * mplr DESC) AS bal_qty , SUM (amt * mplr) OVER (PARTITION BY item ORDER BY idate, qty * mplr DESC) AS bal_amt , LEAD (idate, 1, idate) OVER (PARTITION BY item ORDER BY idate, qty * mplr DESC) AS next_idate , CASE WHEN idate >= start_date THEN idate END AS in_range FROM got_mplr ) SELECT in_range AS idate , NVL2 (in_range, ref1, NULL) AS ref1 , NVL2 (in_range, rt, NULL) AS rt , NVL2 (in_range, eff_qty, NULL) AS qty , NVL2 (in_range, eff_amt, NULL) AS amt , bal_qty , bal_amt FROM got_bal WHERE next_idate >= start_date ORDER BY item, idate NULLS FIRST, eff_qty DESC -- or whatever you want ;
If you want the results in the order that you posted them (rather than the order you described), then just change the analytic ORDER BY clauses.
Since the multiplier +1 or -1 (depending on whether rt1 starts with 'OT' or not) is needed in several places, the query above starts by computing it in a separate sub-query.
In order to get the correct cumulative SUM, we need to get results for all rows before the ending date. However, in the result set, we only want to display rows where idate is on or after start_date, plus the last row where idate is before start_date (if there is such a row). To find that extra row, the query above uses LEAD. Furthermore, on that extra row, most of the columns will be NULL. We could use CASE expressions for each of them, like this
CASE WHEN idate1 >= start_date THEN ref1 END AS ref1
but the query above uses NVL2 because it's more concise, which makes it easier to read, understand and debug.
-
@Frank Kulash for change in messages your instruction been noted! with thanks.
for order in values i mean to say if two/more rows comes in same date i.e +40 and +100 than greater first i.e +100 and than +40 if values are -40 and -100 than it will be -100 first and than -40.
-
Hi, @AQH
for order in values i mean to say if two/more rows comes in same date i.e +40 and +100 than greater first i.e +100 and than +40 if values are -40 and -100 than it will be -100 first and than -40.
You're contradicting yourself. -40 is greater than -100, so if you want the greater quantity first, then you want the row with -40 before the row with -100.
The query above will work no matter how you want to order the rows. If, for some reason, you want the row with -100 to come before the row with -40, then all you need to change are the analytic ORDER BY clauses. Remember to change all three of them. They are all together in the sub-query got_bal, so if you keep the code formatted, it will be easy to see if they are all the same.
-
@Frank Kulash i have no word but admire and respect. thank for reply!