Query to display data like below

Hey, check the below query, this may help you.

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1
CREATE TABLE #temp1
(
iid INT,
icode INT,
ddate DATE,
nvalue NUMERIC(16, 2)
)
INSERT INTO #temp1 VALUES(1, 1,'2022-10-01', 5000)
INSERT INTO #temp1 VALUES(2, 1,'2022-10-02', 1000)
INSERT INTO #temp1 VALUES(3, 2,'2022-10-04', 10000)
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2
CREATE TABLE #temp2
(
icode INT,
citemcode NVARCHAR(10),
ddate DATE,
nquantity INT
)
INSERT INTO #temp2 VALUES(1, 'I1','2022-10-01', 4)
INSERT INTO #temp2 VALUES(1, 'I2','2022-10-01', 2)
INSERT INTO #temp2 VALUES(1, 'I1','2022-10-02', 4)
INSERT INTO #temp2 VALUES(2, 'I3','2022-10-04', 20)
IF OBJECT_ID('tempdb..#temp3') IS NOT NULL DROP TABLE #temp3
CREATE TABLE #temp3
(
icode INT,
citemcode NVARCHAR(10),
ddate DATE,
nquantity INT
)
INSERT INTO #temp3 VALUES(1, 'I1','2022-10-01', 1)
IF OBJECT_ID('tempdb..#temp4') IS NOT NULL DROP TABLE #temp4
CREATE TABLE #temp4
(
icode INT,
citemcode NVARCHAR(10),
ddate DATE,
nquantity INT
)
INSERT INTO #temp4
SELECT tmp2.icode, tmp2.citemcode, tmp2.ddate, nquantity = SUM(ISNULL(tmp2.nquantity, 0)) - SUM(ISNULL(tmp3.nquantity, 0)) FROM #temp2 tmp2 LEFT JOIN #temp3 tmp3 ON tmp2.icode = tmp3.icode
AND tmp2.citemcode = tmp3.citemcode AND tmp2.ddate = tmp3.ddate GROUP BY tmp2.icode, tmp2.citemcode, tmp2.ddate
;WITH cte (icode, ddate, citemcode) AS (
SELECT icode, ddate, citemcode = STUFF((SELECT ', ' + citemcode + '-' + CAST(SUM(nquantity) AS VARCHAR) FROM #temp4 t2
WHERE t2.icode = t1.icode AND t2.ddate = t1.ddate GROUP BY citemcode, ddate FOR XML PATH('')), 1, 1, '')
FROM #temp4 t1 GROUP BY icode, ddate )
SELECT c.*, tmp1.nvalue FROM cte c JOIN #temp1 tmp1 ON c.icode = tmp1.icode AND c.ddate = tmp1.ddate

1 Reply

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

Amit Mohanty4y ago
Hey, check the below query, this may help you.
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1
CREATE TABLE #temp1
(
    iid INT,
    icode INT,
    ddate DATE,
    nvalue NUMERIC(16, 2)
)
INSERT INTO #temp1 VALUES(1, 1,'2022-10-01', 5000)
INSERT INTO #temp1 VALUES(2, 1,'2022-10-02', 1000)
INSERT INTO #temp1 VALUES(3, 2,'2022-10-04', 10000)

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2
CREATE TABLE #temp2
(
    icode INT,
    citemcode NVARCHAR(10),
    ddate DATE,
    nquantity INT
)
INSERT INTO #temp2 VALUES(1, 'I1','2022-10-01', 4)
INSERT INTO #temp2 VALUES(1, 'I2','2022-10-01', 2)
INSERT INTO #temp2 VALUES(1, 'I1','2022-10-02', 4)
INSERT INTO #temp2 VALUES(2, 'I3','2022-10-04', 20)

IF OBJECT_ID('tempdb..#temp3') IS NOT NULL DROP TABLE #temp3
CREATE TABLE #temp3
(
    icode INT,
    citemcode NVARCHAR(10),
    ddate DATE,
    nquantity INT
)
INSERT INTO #temp3 VALUES(1, 'I1','2022-10-01', 1)

IF OBJECT_ID('tempdb..#temp4') IS NOT NULL DROP TABLE #temp4
CREATE TABLE #temp4
(
    icode INT,
    citemcode NVARCHAR(10),
    ddate DATE,
    nquantity INT
)
INSERT INTO #temp4
SELECT tmp2.icode, tmp2.citemcode, tmp2.ddate, nquantity = SUM(ISNULL(tmp2.nquantity, 0)) - SUM(ISNULL(tmp3.nquantity, 0)) FROM #temp2 tmp2 LEFT JOIN #temp3 tmp3 ON tmp2.icode = tmp3.icode
AND tmp2.citemcode = tmp3.citemcode AND tmp2.ddate = tmp3.ddate GROUP BY tmp2.icode, tmp2.citemcode, tmp2.ddate


;WITH cte (icode, ddate, citemcode) AS (
SELECT icode, ddate, citemcode = STUFF((SELECT ', ' + citemcode + '-' + CAST(SUM(nquantity) AS VARCHAR)  FROM #temp4 t2
WHERE t2.icode = t1.icode AND t2.ddate = t1.ddate GROUP BY citemcode, ddate FOR XML PATH('')), 1, 1, '')
FROM #temp4 t1 GROUP BY icode, ddate )
SELECT c.*, tmp1.nvalue FROM cte c JOIN #temp1 tmp1 ON c.icode = tmp1.icode AND c.ddate = tmp1.ddate