ช่วยดู Query ข้อมูลหน่อยค่า งมมาเป็นอาทิตย์แล้วยังไม่ได้เลย

DECLARE @minDate DATETIME = ( SELECT MIN( reciept_date ) FROM dbo.tb2); 
DECLARE @newLine CHAR(2) = CHAR(13)+CHAR(10);
DECLARE @sqlSelect NVARCHAR(max) = 
'SELECT
	CASE WHEN( ROW_NUMBER() OVER ( PARTITION BY p_no ORDER BY p_no )) = 1 THEN p_no ELSE '''' end p_no  
	, p_price 
	, convert( varchar , t2.reciept_time , 8 ) reciept_time ' ;

DECLARE @sqlWhere NVARCHAR(max) = 'FROM dbo.tb1
CROSS APPLY(
	SELECT id, reciept_time  FROM dbo.tb2 WHERE p_no_id = dbo.tb1.id
) t2'

DECLARE @cnt INT = 0;
DECLARE @strCnt VARCHAR(10) = '';
DECLARE @strMinDate VARCHAR(20) = '';
WHILE @cnt < 7
BEGIN	
	SET @strCnt = CAST(@cnt AS VARCHAR(10));
	SET @strMinDate = convert(varchar, DATEADD(DAY , @cnt , @minDate), 1) 
	set @sqlSelect += @newLine +' , isnull( day'+ @strCnt+ '.amont ,0) [' + @strMinDate+ '] ' ;
	
	SET @sqlWhere += @newLine
	+ 'outer APPLY (
		SELECT amont FROM dbo.tb2 WHERE id = t2.id AND reciept_time = t2.reciept_time 
		AND reciept_date = ''' +  @strMinDate + ''' 
	) day' + @strCnt;
   SET @cnt +=  1;
END;
DECLARE @sqlAll NVARCHAR(MAX) =  @sqlSelect + @newLine + @sqlWhere;
--PRINT @sqlAll;
EXECUTE sp_executesql @sqlAll;

WITH Report AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY PartNum) ROW_NUM,PartNum,ForcastDate ,ForcastTime,Material,DV,Plant,ForcastCumQty
  FROM Shipping 
  INNER JOIN MasterData ON Shipping.ID=MasterData.ID
)

SELECT * FROM
(
  SELECT (CASE WHEN PartNum=(SELECT PartNum FROM Report TBLSUB WHERE TBLSUB.ROW_NUM+1=Report.ROW_NUM ) THEN ' ' ELSE PartNum END)PartNum,
    ForcastDate,
    ForcastTime,
    Material,
    DV,
    Plant,
    ForcastCumQty,
    ROW_NUM
  FROM Report
  ORDER BY PartNum
)
PIVOT 
(
   SUM(ForcastCumQty)
   FOR ForcastDate IN ('20180109','20180110','20180111','2018012')
)
ORDER BY ROW_NUM

DECLARE @Query AS NVARCHAR (MAX) 
,@ColumnName AS NVARCHAR (MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(ForecastDate)
FROM 
	(
		SELECT DISTINCT ForecastDate
		FROM dbo.Shipping
	) AS ForecastDate
ORDER BY ForecastDate

SET @Query = N'
SELECT DISTINCT
	PartNum,
	Material,
	DV,
	Plant,
	ForecastNetQTY,
	ForecastTime,
	' + @ColumnName + '
FROM (
	SELECT a.PartNum
	, b.Material
	, b.DV
	, b.Plant
	, a.ForecastNetQTY
	, a.ForecastTime
	, a.ForecastDate
	, CAST(a.ForecastCumQty AS INT) AS ForecastCumQty
	FROM [dbo].[Shipping] a
	LEFT JOIN [dbo].[MasterData] b ON (a.PartNum=b.CustomerMaterialNumber)
) AS j PIVOT (
	SUM(ForecastCumQty) FOR ForecastDate IN (' + @ColumnName + ')
) AS ForecastDate'

EXEC sp_executesql @Query

DECLARE @Query AS NVARCHAR (MAX) 
,@ColumnName AS NVARCHAR (MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(ForecastDate)
FROM 
	(
		SELECT DISTINCT ForecastDate
		FROM dbo.Shipping
	) AS ForecastDate
ORDER BY ForecastDate

SET @Query = N'
SELECT DISTINCT
	PartNum,
	Material,
	DV,
	Plant,
	ForecastNetQTY,
	ForecastTime,
	' + @ColumnName + '
FROM (
	SELECT a.PartNum
	, b.Material
	, b.DV
	, b.Plant
	, a.ForecastNetQTY
	, a.ForecastTime
	, a.ForecastDate
	, a.ForecastCumQty
	FROM [dbo].[Shipping] a
	LEFT JOIN [dbo].[MasterData] b ON (a.PartNum=b.CustomerMaterialNumber)
) AS j PIVOT (
	MAX(ForecastCumQty) FOR ForecastDate IN (' + @ColumnName + ')
) AS ForecastDate'

EXEC sp_executesql @Query

select p_no, p_price, reciept_time
, if(ds7=0, '', ds7) as d7
, if(ds6=0, '', ds6) as d6
, if(ds5=0, '', ds5) as d5
, if(ds4=0, '', ds4) as d4
, if(ds3=0, '', ds3) as d3
, if(ds2=0, '', ds2) as d2
, if(ds1=0, '', ds1) as d1
from (
	select p_no_id, reciept_time
	, sum(if(reciept_date=date_sub('2018-01-27', interval 6 day), amount, 0)) as ds7
	, sum(if(reciept_date=date_sub('2018-01-27', interval 5 day), amount, 0)) as ds6
	, sum(if(reciept_date=date_sub('2018-01-27', interval 4 day), amount, 0)) as ds5
	, sum(if(reciept_date=date_sub('2018-01-27', interval 3 day), amount, 0)) as ds4
	, sum(if(reciept_date=date_sub('2018-01-27', interval 2 day), amount, 0)) as ds3
	, sum(if(reciept_date=date_sub('2018-01-27', interval 1 day), amount, 0)) as ds2
	, sum(if(reciept_date='2018-01-27', amount, 0)) as ds1
	from tb2 where reciept_date>date_sub('2018-01-27', interval 7 day)
	group by p_no_id, reciept_time
	) as tmp
left join tb1 on tb1.id = tmp.p_no_id
order by p_no, reciept_time

### จาก
, if(ds7=0, '', ds7) as d7
, sum(if(reciept_date=date_sub('2018-01-27', interval 6 day), amount, 0)) as ds7
### เป็น
, case ds7 when 0 then '' else ds7 end as d7
, sum(case reciept_date when dateadd(day, -6, date('2018-01-27')) then amount else 0 end) as ds7

DECLARE @Query AS NVARCHAR (MAX) 
,@ColumnName AS NVARCHAR (MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(ForecastDate)
FROM 
	(
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 0,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
		union all
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 1,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
		union all
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 2,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
		union all
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 3,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
		union all
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 4,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
		union all
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 5,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
		union all
		SELECT TOP 1 CONVERT(char(10), DATEADD(day, 6,cast(ForecastDate AS date)),112) AS ForecastDate
		FROM dbo.Shipping
	) AS ForecastDate
ORDER BY ForecastDate

SET @Query = N'
SELECT DISTINCT
	PartNum,
	Material,
	DV,
	Plant,
	ForecastNetQTY,
	ForecastTime,
	' + @ColumnName + '
FROM (
	SELECT a.PartNum
	, b.Material
	, b.DV
	, b.Plant
	, a.ForecastNetQTY
	, a.ForecastTime
	, a.ForecastDate
	, CAST(a.ForecastCumQty AS INT) AS ForecastCumQty
	FROM [dbo].[Shipping] a
	LEFT JOIN [dbo].[MasterData] b ON (a.PartNum=b.CustomerMaterialNumber)
) AS j PIVOT (
	SUM(ForecastCumQty) FOR ForecastDate IN (' + @ColumnName + ')
) AS ForecastDate'

EXEC sp_executesql @Query