Query is not giving me expected output.
-
Below is query with data,which is not giving me expected output.
DECLARE @StartDate date = '03-06-2020';
DECLARE @enddate date = '06-06-2020';
Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50))
Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,delid int)
Create table #DispatchBM (DID int,Name varchar(50),Date date,Del int)
Create table #Dispatch_BD (ID int ,BID int,DID int,Codeitem int,QTY int,BWeight int,Delidd int)
INSERT INTO #ItemMasterFile VALUES
(1,'A','Bigbale')
, (2,'B','Bigbale')
, (3,'C','Bigbale')
, (4,'D','Bigbale')
, (5,'e','Bigbale')
, (6,'f','Bigbale')
, (7,'g','Bigbale')
, (8,'h','Bigbale')
, (9,'K','Bigbale')
, (10,'L','Bigbale')
, (11,'M','Bigbale');
INSERT INTO #Bigbalprd VALUES
(111,1,1,500,'03-06-2020',null)
,(112,2,1,200,'03-06-2020',null)
,(113,1,1,300,'03-06-2020',null)
,(114,6,1,100,'04-06-2020',null)
,(115,1,1,200,'04-06-2020',null)
,(116,1,1,300,'04-06-2020',null)
,(117,7,1,100,'05-06-2020',null)
,(118,5,1,200,'05-06-2020',null)
,(119,8,1,300,'06-06-2020',null)
Insert into #DispatchBM Values
(1001,'Akhter','03-06-2020',null)
,(1002,'Irfan','06-06-2020',null)
Insert into #Dispatch_BD Values
(11,111,1001,1,1,500,null)
,(12,112,1001,2,1,200,null)
,(13,113,1001,1,1,300,null)
,(14,117,1002,7,1,100,null)
,(15,118,1002,5,1,200,null)
;with cte as (
select a.CodeItem ,upper(a.Descriptionitem) item_Name,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight
from #ItemMasterFile a
left join #Bigbalprd b
on a.CodeItem=b.CodeItem
where convert(date,b.EntryDate,105) between @startdate and @enddate and b.delID is null
group by a.CodeItem,a.Descriptionitem,b.EntryDate
)
,cte1 as (
select a.CodeItem,upper(a.Descriptionitem) item_Name, Date,sum(qty) D_QTY,sum(Bweight) D_Weight
from #ItemMasterFile a
left join #Dispatch_BD c
on c.codeitem=a.codeitem
left join #DispatchBM d
on d.DID=c.DID
where convert(date,date,105) between @startdate and @enddate and c.Delidd is null and d.Del is null
group by a.codeitem,a.Descriptionitem,d.date
),cte2 as (
select f.CodeItem,f.item_Name,(f.Bigbale_QTY-f.Dispatch_QTY) as Balance_Qty,(f.Bigbale_weight-f.Dispatch_Weight) as Balance_Weight from (
select e.CodeItem,e.item_Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],
isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
select upper(a.Descriptionitem) item_Name,(a.CodeItem) CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
from #ItemMasterFile a
--inner join Catagory ca on ca.CID=a.CID
left join #Bigbalprd b on a.CodeItem=b.CodeItem
where a.Packsize ='bigbale' and b.delID is null
group by a.Descriptionitem,a.CodeItem) e
left join #Dispatch_BD c on e.CodeItem=c.CodeItem
where c.Delidd is null
group by e.item_Name,e.CodeItem
)f)
select c.codeitem,upper(c.Descriptionitem) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
,isnull(Balance_Qty,0) Balance_Qty,isnull(Balance_Weight,0) Balance_Weight
into #t
from #ItemMasterFile c
left join cte a on a.codeitem=c.codeitem
left join cte1 b on a.codeitem=b.codeitem
left join cte2 d on a.CodeItem=d.CodeItem
and a.B_Date=b.date
where c.Packsize='Bigbale'
DECLARE @cols NVARCHAR (MAX)
SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
from #t where ISNULL(B_date,'')<>'' for xml path(''))
set @cols=SUBSTRING(@cols,2,len(@cols)-1)
DECLARE @cols1 NVARCHAR (MAX)
SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
from #t where ISNULL(B_date,'')<>'' for xml path(''))
set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)
DECLARE @cols2 NVARCHAR (MAX)
SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'
from #t where ISNULL(B_date,'')<>'' for xml path(''))
DECLARE @cols3 NVARCHAR (MAX)
SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23) +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'
+',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '
from #t where ISNULL(B_date,'')<>'' for xml path(''))
set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)
DECLARE @query NVARCHAR(MAX)
SET @query = '
select '''' codeitem,'''' item_Name,'''' Balance_QTY,'''' Balance_Weight,'+@cols3+'
UNION ALL
SELECT cast(codeitem as varchar(10)) codeitem,item_Name,Balance_QTY,Balance_Weight,' + @cols1 + '
FROM (
SELECT codeitem,item_Name,Balance_QTY,Balance_Weight, CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE
FROM (
select * from #t
)s
UNPIVOT
(VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p
) src
PIVOT
(
MAX(VALUE) FOR Name IN (' + @cols + ')
) pvt
'
EXEC SP_EXECUTESQL @query
Expected Output
-
This seems to return output similar to the picture. Is it just missing a total row? What's not working?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hi Steve Collins,
Below image , in which i mentioned in red ,that Data must display ,Item_Name A is repeating two time,which should not be repeated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply