Remove From My Forums

Data is not getting filter as per date

Question

User-367318540 posted

below is my data and query

i am trying to filter data as per date ,but data is not fetching as per date

CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');

INSERT INTO #ItemMasterFile VALUES
  (1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);


INSERT INTO #Probale VALUES 
   (1,1,1,001,100,'01-06-2019',null)
, (2,3,1,001,200,'02-06-2019',null)
, (3,11,1,002,200,'03-06-2019',null)
, (4,10,1,002,200,'08-06-2019',null)
, (4,1,1,003,200,'08-06-2019',null)
, (4,3,1,003,200,'08-06-2019',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
  (1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

declare @fromdate date='2019-01-06'
declare @todate date='2019-01-06'
declare @columns varchar(max)
declare @convert varchar(max)
select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
                    from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
 and P.DelID is null and S.status ='Open'
                  
                    for xml path('')), 1, 2, '') + ']'


set @convert =
('select * from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.DelID is null 

 ) smallbale
    pivot(sum(prdqty) for Order_Ref_No
    in ('+@columns+')) as pivottable')


execute (@convert)


drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale

output

https://ibb.co/JzGzhx6

Thursday, August 13, 2020 8:15 AM

Answers

User-1330468790 posted

Hi akhterr,

I think the reason is pretty simple that you only select "Order_Ref_No" which has records during the period between the "fromdate" and "todate".

However, you should also limit the date period for counting the item for the selected "Order_Ref_No" in the following select "@convert".

For the given sql statement, try to modify the variable "@convert" as below:

set @convert = 

('select * from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.DelID is null 
and P.Entrydate between '''+ Convert(varchar,@fromdate) + ''' and '''+ Convert(varchar,@todate)  +'''
 ) smallbale
    pivot(sum(prdqty) for Order_Ref_No
   in ('+@columns+')) as pivottable')

The @convert will be :

select * from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.DelID is null 
and P.Entrydate between '2019-01-06' and '2019-01-06'
 ) smallbale
    pivot(sum(prdqty) for Order_Ref_No
   in ([A_01])) as pivottable

Result:

codeitem	A_01
1	1

Hope this can help you.

Best regards,

Sean

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Friday, August 14, 2020 9:20 AM

Answered by:

Data is not getting filter as per date

Question

Answers