Date order wise in Pivot

Question

below is my query their ouput is not coming date order wise

Declare @fromdate date = '2020-01-01'  
Declare @todate date ='2020-01-31'  
  
declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);  
  
  
  
SELECT p.prdqty,p.entrydate, i.Descriptionitem   
into #TempResults  
from Probale P   
inner join salesorder S on S.OrderNo=P.orderno   
inner join itemmasterfile i on i.codeitem=p.codeitem  
where P.Entrydate between @fromdate and  @todate  
 and P.DelID is null;  
  
select @pvtColumns = stuff (( select distinct ', ' +  quotename(CONVERT(varchar(10),EntryDate,120) )  
from #TempResults  
 for xml path('')), 1, 2, '');   
  
select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(CONVERT(varchar(10),EntryDate,120) ) +', 0) AS ' +   
quotename(CONVERT(varchar(10),EntryDate,120) )  
 from #TempResults   
  for xml path('')), 1, 2, '');   
  
select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(CONVERT(varchar(10),EntryDate,120) ) +', 0)'   
 from #TempResults   
  for xml path('')), 1, 3, '');  
  
  
--select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults  
--GROUP BY order_Ref_No, ROLLUP(CodeItem)  
  
set @sqlQuery =  
'select ISNULL(CAST(Descriptionitem as varchar(100)), ''Total:'')  as Descriptionitem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from   
  
(select Descriptionitem, sum(prdQty) as Qty, EntryDate from #TempResults  
GROUP BY EntryDate  
  
  
, ROLLUP(Descriptionitem)) sm  
pivot(sum(Qty) for EntryDate  
    in (' + @pvtColumns + ')) as pivottable  
order by case when PivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem';  
  
--print @sqlQuery;  
  
execute (@sqlQuery)  
drop table #TempResults  

Ouput

Answer 1

Hi @Analyst_SQL ,

You could alter your code by removing the DISTINCT and adding a GROUP BY to use with the ORDER BY:

Please have a try to replace your query with below part and check whether it is working.

 select @qryColumns = STUFF((SELECT ', ISNULL(' + quotename(CONVERT(varchar(10),EntryDate,120) ) +', 0) AS ' +   
 quotename(CONVERT(varchar(10),EntryDate,120) )  
  from #TempResults   
  group by EntryDate  
  order by EntryDate  
   for xml path(''),TYPE  
            ).value('.', 'NVARCHAR(MAX)')  
        ,1,2,'')   

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 2

Hi @Analyst_SQL ,

I reused Probale DDL in your previous thread and modified some sample data.

I could get an output with date order wise as below:

So please provide more sample data for Probale,salesorder and itemmasterfile tables mentioned in your query so that we could check in further.

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa