create table #Catagory (CID int, CName varchar(max))
Create table #Sections (SecID int, Secnam varchar(50))
create table #ItemMasterFile(CodeItem int, Descriptionitem varchar(max),SecID int,CID int)
create table #Bigbalprd(BID int, CodeItem int, SecID int, CID int, QTY int)
insert into #Catagory values (1, 'INDIA'),(2, 'INDIA(Cut)'),(3, 'Pakistan'),(4, 'Pakistan(Cut)')
insert into #Sections values (1, 'HR'),(2, 'Baby'),(3, 'Ladies'),(4, 'Mix Rammage')
insert into #ItemMasterFile values (1, 'A', '1', '1'),(2, 'B', '2', '2'),(3, 'C', '3', '3'),(4, 'D', '4', null)
insert into #Bigbalprd values (1, '1', '1', '1','1'),(2, '3', '3', '3','1'),(3, '4', '4', null,'1'),(4, '4', '4', null,'1')
i want this output to display all item either exit or not in a Bigbalprd table..
Description |
Section |
Catagory |
QTY |
A |
HR |
India |
1 |
B |
Baby |
INDIA(Cut) |
0 |
C |
Ladies |
Pakistan |
1 |
D |
Null |
Pakistan(Cut) |
2 |
|
|
Total |
4 |
here i tried but it is not retreive all items, it just retreiving those item which are exit in both table here is my query
-- Insert statements for procedure here
SELECT ItemMasterFile.Descriptionitem as Artical,Sections.Secnam as Section,Sum(Bigbalprd.Bpqty) as QTY,Catagory.CName as Catagory
FROM Bigbalprd
right JOIN ItemMasterFile ON Bigbalprd.CodeItem = ItemMasterFile.CodeItem
right JOIN Sections ON Sections.SecID = Bigbalprd.SecID
INNER JOIN Catagory ON Catagory.CID = Bigbalprd.CID
-- where Bigbalprd.Entrydate between @Startdate and @Enddate
group by ItemMasterFile.Descriptionitem,Catagory.CID,Catagory.CName,Sections.Secnam
order by catagory.CName