Click here to Skip to main content
14,300,951 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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

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


Now, a solution...
Try this:
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:
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:
SELECT ...
INTO #tmpTable
FROM ...

Then you'll be able to simplify your query statement into this form:
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100