Click here to Skip to main content
14,974,887 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have problem to sum of three table

What I have tried:

i have did on table section sum..but do not have idea how to sum remain two table...
here is my data and query ]

CREATE TABLE #Catagory (CID INT,CName VARCHAR(50))
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT)
CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,QTY INT,Entrydate DATETIME)
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #Employee(EID INT,SecID INT,QTY int, Entrydate DATETIME)
 
INSERT INTO #Catagory VALUES(1,'INDIA')
INSERT INTO #Catagory VALUES(2,'INDIA(Cut)')
INSERT INTO #Catagory VALUES(3,'Pakistan')
INSERT INTO #Catagory VALUES(4,'Pakistan(Cut)')
 
 
INSERT INTO #Sections VALUES(1,'HR')
INSERT INTO #Sections VALUES(2,'Baby')
INSERT INTO #Sections VALUES(3,'Ladies')
INSERT INTO #Sections VALUES(4,'Mix Rammage')
 
INSERT INTO #ItemMasterFile VALUES(1,'A',1,1)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,2)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,3)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,null)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,null)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,null)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2)
INSERT INTO #ItemMasterFile VALUES(10,'L',4,3)
INSERT INTO #ItemMasterFile VALUES(11,'M',2,4)
 
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019')
INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02-06-2019')
INSERT INTO #Bigbalprd VALUES(3,4,null,4,1,'03-06-2019')
INSERT INTO #Bigbalprd VALUES(4,4,null,4,1,'04-06-2019')
INSERT INTO #Bigbalprd VALUES(4,5,null,4,1,'04-06-2019')

INSERT INTO #Probale VALUES(1,1,1,'01-06-2019')
INSERT INTO #Probale VALUES(2,3,1,'02-06-2019')
INSERT INTO #Probale VALUES(3,11,1,'03-06-2019')
INSERT INTO #Probale VALUES(4,10,1,'08-06-2019')
INSERT INTO #Probale VALUES(3,8,1,'03-06-2019')
INSERT INTO #Probale VALUES(4,9,1,'08-06-2019')
INSERT INTO #Probale VALUES(4,9,1,'08-06-2019')

INSERT INTO #Employee VALUES(1,1,4,'01-05-2019')
INSERT INTO #Employee VALUES(2,3,5,'02-05-2019')
INSERT INTO #Employee VALUES(3,3,3,'03-05-2019')
INSERT INTO #Employee VALUES(4,4,7,'04-05-2019')
 
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '09-06-2019'
 
SELECT DISTINCT s.Secnam, ISNULL(SUM(b.prdQTY),0)QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Probale b ON  i.CodeItem = b.CodeItem
FULL OUTER JOIN #Sections s ON i.SecID = s.SecID

WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL
GROUP BY  s.Secnam
 
DROP TABLE #Catagory
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd
Drop Table #Employee
Drop Table #Probale


here is my output which i want
Total — imgbb.com[^]
Posted
Updated 26-Jun-19 23:36pm
Comments
Maciej Los 27-Jun-19 3:10am
   
Can you be more specific and provide more details about you issue, especially about desired result.
akhter86 27-Jun-19 3:38am
   
here is my desired result https://ibb.co/Jz98hwh

1 solution

First of all, a small tip:
You can define variable of type table:
SQL
DECLARE @Catagory TABLE(CID INT,CName VARCHAR(50))

You can use simpler form of INSERT INTO statement:
SQL
INSERT INTO @Catagory VALUES(1,'INDIA'), (2,'INDIA(Cut)'), (3,'Pakistan'), (4,'Pakistan(Cut)')


Now, a solution...
Try this:
SQL
SELECT s.Secnam, ISNULL(SUM(b.QTY),0) BigbalQTY, ISNULL(SUM(p.prdQTY),0) ProbableQTY, ISNULL(SUM(b.QTY),0) + ISNULL(SUM(p.prdQTY),0) Total,
		(SELECT ISNULL(SUM(DISTINCT QTY),0) FROM @Employee WHERE SecID = s.SecID) EmpQTY
FROM @ItemMasterFile i
	LEFT JOIN @Probale p ON  i.CodeItem = p.CodeItem
	LEFT JOIN @Sections s ON i.SecID = s.SecID
	LEFT JOIN @Bigbalprd b  ON  i.CodeItem = b.CodeItem
WHERE (p.Entrydate BETWEEN @StartDate AND @Enddate) OR p.Entrydate IS NULL
GROUP BY  s.Secnam, s.SecID


To get total, you have to "add" new row, which you are able to achieve by using UNION ALL[^] statement:
SQL
UNION ALL
SELECT 'Total', SUM(T.BigbalQTY), SUM(T.ProbableQTY), SUM(T.Total), SUM(T.EmpQTY)
FROM (
   --above query here
) AS T


I'd suggest to drop result of first query into[^] temporary table:
SQL
SELECT ...
INTO #tmpTable
FROM ...

Then you'll be able to simplify your query statement into this form:
SQL
SELECT a.*
FROM #tmpTable a
UNION ALL
SELECT 'Total', SUM(...)
FROM #tmpTable
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900