Hi akhter,
Your query is wrong.
1. GROUP BY clause should be after the WHERE statement.
2. Your WHERE statement is missing BETWEEN clause.
If you want to display the record having date as null, you need to add the IS NULL checking with OR condition for Date column.
Check this sample query. Now please take its reference and correct your query as per your table structure.
SQL
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)
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 #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,4,null,1,'03-06-2019')
INSERT INTO #Bigbalprd VALUES(4,4,4,null,1,'04-06-2019')
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '03-06-2019'
SELECT DISTINCT i.Descriptionitem,s.Secnam,c.CName, ISNULL(SUM(b.QTY),0)QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Bigbalprd b ON i.CodeItem = b.CodeItem
FULL OUTER JOIN #Sections s ON i.SecID = s.SecID
FULL OUTER JOIN #Catagory c ON c.CID = i.CodeItem
WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL
GROUP BY i.Descriptionitem, s.Secnam,c.CName
DROP TABLE #Catagory
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd
Output
| Descriptionitem |
Secnam |
CName |
QTY |
| A |
HR |
INDIA |
1 |
| B |
Baby |
INDIA(Cut) |
0 |
| C |
Ladies |
Pakistan |
1 |
| D |
Mix Rammage |
Pakistan(Cut) |
1 |
| e |
NULL |
NULL |
0 |
| f |
NULL |
NULL |
0 |
| g |
Mix Rammage |
NULL |
0 |
| h |
Mix Rammage |
NULL |
0 |
| K |
Baby |
NULL |
0 |
