Query required in with opening and closing

i have data below

CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate date)
CREATE TABLE #Bigbalprd (BID INT,Bweight int,Bpqty int,EntryDate date)
INSERT INTO #Probale VALUES(10010,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10011,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10012,300,1,'02-06-2020')
INSERT INTO #Probale VALUES(10016,200,1,'02-06-2020')
INSERT INTO #Probale VALUES(10013,110,1,'03-06-2020')
INSERT INTO #Probale VALUES(10014,150,1,'03-06-2020')
INSERT INTO #Probale VALUES(10015,100,1,'04-06-2020')
INSERT INTO #Probale VALUES(10016,150,1,'04-06-2020')
INSERT INTO #Probale VALUES(10017,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10019,900,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,900,1,'06-06-2020')
INSERT INTO #Bigbalprd VALUES(20010,500,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20011,600,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20012,700,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20013,200,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20014,410,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20015,250,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20016,200,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20017,250,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20018,400,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20019,200,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20020,300,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20021,350,1,'06-06-2020')
INSERT INTO #ConIssuance VALUES(1111,1000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1112,2000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1113,800,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1114,600,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1115,400,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1116,100,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1117,300,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1118,110,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1119,100,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1120,800,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1121,900,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1122,1900,1,'06-06-2020')

Output

#Probale table columns Pweight and prdqty Sum date wise.

#Bigbalprd table Columns Bweight and Bpqty sum date wise.

#ConIssuance Columns Iweight and QTY sum date wise

Floor= (Opening + Iweight),First day opening will be 0

Closing = Floor-Bweight-Pweight

Opening = Closing will forward next day

Try this code:

CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate date)
CREATE TABLE #Bigbalprd (BID INT,Bweight int,Bpqty int,EntryDate date)
CREATE TABLE #Probale (PID INT, PWeight int, PQty int, EntryDate date)
INSERT INTO #Probale VALUES(10010,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10011,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10012,300,1,'02-06-2020')
INSERT INTO #Probale VALUES(10016,200,1,'02-06-2020')
INSERT INTO #Probale VALUES(10013,110,1,'03-06-2020')
INSERT INTO #Probale VALUES(10014,150,1,'03-06-2020')
INSERT INTO #Probale VALUES(10015,100,1,'04-06-2020')
INSERT INTO #Probale VALUES(10016,150,1,'04-06-2020')
INSERT INTO #Probale VALUES(10017,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10019,900,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,900,1,'06-06-2020')
INSERT INTO #Bigbalprd VALUES(20010,500,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20011,600,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20012,700,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20013,200,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20014,410,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20015,250,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20016,200,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20017,250,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20018,400,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20019,200,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20020,300,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20021,350,1,'06-06-2020')
INSERT INTO #ConIssuance VALUES(1111,1000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1112,2000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1113,800,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1114,600,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1115,400,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1116,100,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1117,300,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1118,110,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1119,100,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1120,800,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1121,900,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1122,1900,1,'06-06-2020')
;WITH DataRows AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY [Date] ASC) [RN]
, [Date]
, ISNULL(PW, 0) [Pweight]
, ISNULL(PQ, 0) [prdqty]
, ISNULL(BW, 0) [Bweight]
, ISNULL(BQ, 0) [Bpqty]
, ISNULL(CW, 0) [iWeight]
, ISNULL(CQ, 0) [QTY]
FROM (
SELECT SUM(PWeight) [PW], SUM(PQty) [PQ], EntryDate [PE] FROM #Probale P GROUP BY EntryDate
)P
FULL JOIN (
SELECT SUM(Bweight) [BW], SUM(Bpqty) [BQ], EntryDate [BE] FROM #Bigbalprd B GROUP BY EntryDate
)B ON P.PE = B.BE
FULL JOIN (
SELECT SUM(iWeight) [CW], SUM(QTY) [CQ], EntryDate [CE] FROM #ConIssuance C GROUP BY EntryDate
)C ON P.PE = C.CE OR B.BE = C.CE
CROSS APPLY(
SELECT COALESCE(PE, BE, CE) [Date]
)Calc
),
Lagged AS
(
SELECT
[RN]
, [Date]
, [Pweight]
, [prdqty]
, [Bweight]
, [Bpqty]
, [iWeight]
, [QTY]
, [iWeight] [Floor]
, [iWeight] - [Bweight] - [Pweight] [Closing]
, 0 [Opening]
FROM DataRows
WHERE RN = 1
UNION ALL
SELECT
DR.RN
, DR.[Date]
, DR.[Pweight]
, DR.[prdqty]
, DR.[Bweight]
, DR.[Bpqty]
, DR.[iWeight]
, DR.[QTY]
, L.[Closing] + DR.iWeight [Floor]
, L.[Closing] + DR.iWeight - DR.[Bweight] - DR.[Pweight] [Closing]
, L.[Closing] [Opening]
FROM DataRows DR
INNER JOIN Lagged L ON DR.RN = L.RN + 1
)
SELECT * FROM lagged
DROP TABLE #Probale
DROP TABLE #Bigbalprd
DROP TABLE #ConIssuance

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Kamil Krupski6y ago
Try this code:
CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate  date)
CREATE TABLE #Bigbalprd (BID INT,Bweight int,Bpqty int,EntryDate date)
CREATE TABLE #Probale (PID INT, PWeight int, PQty int, EntryDate date)

INSERT INTO #Probale VALUES(10010,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10011,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10012,300,1,'02-06-2020')
INSERT INTO #Probale VALUES(10016,200,1,'02-06-2020')
INSERT INTO #Probale VALUES(10013,110,1,'03-06-2020')
INSERT INTO #Probale VALUES(10014,150,1,'03-06-2020')
INSERT INTO #Probale VALUES(10015,100,1,'04-06-2020')
INSERT INTO #Probale VALUES(10016,150,1,'04-06-2020')
INSERT INTO #Probale VALUES(10017,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10019,900,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,900,1,'06-06-2020')

INSERT INTO #Bigbalprd VALUES(20010,500,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20011,600,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20012,700,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20013,200,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20014,410,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20015,250,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20016,200,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20017,250,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20018,400,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20019,200,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20020,300,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20021,350,1,'06-06-2020')


INSERT INTO #ConIssuance VALUES(1111,1000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1112,2000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1113,800,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1114,600,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1115,400,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1116,100,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1117,300,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1118,110,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1119,100,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1120,800,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1121,900,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1122,1900,1,'06-06-2020')

;WITH DataRows AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY [Date] ASC) [RN]
        , [Date]
        , ISNULL(PW, 0) [Pweight]
        , ISNULL(PQ, 0) [prdqty]
        , ISNULL(BW, 0) [Bweight]
        , ISNULL(BQ, 0) [Bpqty]
        , ISNULL(CW, 0) [iWeight]
        , ISNULL(CQ, 0) [QTY]
    FROM (
        SELECT SUM(PWeight) [PW], SUM(PQty) [PQ], EntryDate [PE] FROM #Probale P GROUP BY EntryDate
    )P
    FULL JOIN (
        SELECT SUM(Bweight) [BW], SUM(Bpqty) [BQ], EntryDate [BE] FROM #Bigbalprd B GROUP BY EntryDate
    )B ON P.PE = B.BE
    FULL JOIN (
        SELECT SUM(iWeight) [CW], SUM(QTY) [CQ], EntryDate [CE] FROM #ConIssuance C GROUP BY EntryDate
    )C ON P.PE = C.CE OR B.BE = C.CE
    CROSS APPLY(
        SELECT COALESCE(PE, BE, CE) [Date]
    )Calc
),
Lagged AS
(
    SELECT
        [RN]
        , [Date]
        , [Pweight]
        , [prdqty]
        , [Bweight]
        , [Bpqty]
        , [iWeight]
        , [QTY]
        , [iWeight] [Floor]
        , [iWeight] - [Bweight] - [Pweight] [Closing]
        , 0 [Opening]
    FROM DataRows
    WHERE RN = 1

    UNION ALL

    SELECT
        DR.RN
        , DR.[Date]
        , DR.[Pweight]
        , DR.[prdqty]
        , DR.[Bweight]
        , DR.[Bpqty]
        , DR.[iWeight]
        , DR.[QTY]
        , L.[Closing] + DR.iWeight [Floor]
        , L.[Closing] + DR.iWeight - DR.[Bweight] - DR.[Pweight] [Closing]
        , L.[Closing] [Opening]
    FROM DataRows DR
    INNER JOIN Lagged L ON DR.RN = L.RN + 1
)

SELECT * FROM lagged

DROP TABLE #Probale
DROP TABLE #Bigbalprd
DROP TABLE #ConIssuance