Forum Stats

  • 3,826,757 Users
  • 2,260,705 Discussions
  • 7,897,072 Comments

Discussions

Categories

closing and opening balance SQL

AQH
AQH Member Posts: 298 Blue Ribbon
edited Jul 6, 2021 3:47PM in SQL & PL/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.
image.png 


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


Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond
    edited Jul 6, 2021 5:13PM 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.

    AQH
    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond

    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.

    AQH
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond

    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?



    image.png

    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)?

    AQH
    · Share on TwitterShare on Facebook
  • AQH
    AQH Member Posts: 298 Blue Ribbon

    @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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond

    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?

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond
    edited Jul 6, 2021 5:13PM 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.

    AQH
    · Share on TwitterShare on Facebook
  • AQH
    AQH Member Posts: 298 Blue Ribbon

    @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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond

    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.

    · Share on TwitterShare on Facebook
  • AQH
    AQH Member Posts: 298 Blue Ribbon
    edited Jul 6, 2021 5:27PM

    @Frank Kulash i have no word but admire and respect. thank for reply!

    · Share on TwitterShare on Facebook