query optimization

i didn't get your point if you can solve below is the query

 

Select * from

(

select a.StockLocationName,

a.Grade+'a' As Grade_a,

a.Grade+'b' As Grade_b,

a.Grade+'c' As Grade_c,

a.Grade+'d' As Grade_d,

sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') > 0) THEN(a.Q1 - a.Q2 + a.Q3) / 20 ELSE 0 END) as OpStkMTS,

sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') <= 0 and datediff(day, DocDate, '08-01-2018 00:00:00') >= 0) THEN(a.Q1 +a.Q3) / 20 ELSE 0 END) as ReceiptStkMTS,

sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') <= 0 and datediff(day, DocDate, '08-01-2018 00:00:00') >= 0) THEN a.Q2 / 20 ELSE 0 END) as DispatchStkMTS,

sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') >= 0) THEN(a.Q1 - a.Q2 + a.Q3) / 20 ELSE 0 END) as ClosingStkMTS

from( select StockLocationName, Grade,InvoiceDt as DocDate, Sum(CONVERT(float,GoodsReceivedBy))*20 as Q1, 0 AS Q2, 0 as Q3, 0 as Q0 from StockReceiptsNow where StockLocationName = 'CRWC' group by StockLocationName, Grade, InvoiceDt UNION ALL Select StockLocationName, Grade, DocDate, 0 AS Q1, Sum(QtyBags) as Q2, 0 as Q3, 0 as Q0 from Dispatches where StockLocationName = 'CRWC' Group by StockLocationName, Grade, DocDate UNION ALL Select StockLocationName, Grade, DocDate, 0 AS Q1, 0 as Q2, Sum(QtyBags) as Q3, 0 as Q0 from GodownTransfer where StockLocationName ='CRWC' Group by StockLocationName, Grade, DocDate) a group by a.StockLocationName, a.Grade)

As b

pivot

(

Sum(OpStkMTS) for Grade_a in([OPC 43-Gr NRSa],[OPC 53-Gr MRPa],[OPC 53-Gr NRSa],[PPC Gr MRPa],[PPC Gr-NRSa])

) as pivotTable1

pivot

(

Sum(ReceiptStkMTS) for Grade_b in([OPC 43-Gr NRSb],[OPC 53-Gr MRPb],[OPC 53-Gr NRSb],[PPC Gr MRPb],[PPC Gr-NRSb])

) as pivotTable1

pivot

(

Sum(DispatchStkMTS) for Grade_c in([OPC 43-Gr NRSc],[OPC 53-Gr MRPc],[OPC 53-Gr NRSc],[PPC Gr MRPc],[PPC Gr-NRSc])

) as pivotTable1

pivot

(

Sum(ClosingStkMTS) for Grade_d in([OPC 43-Gr NRSd],[OPC 53-Gr MRPd],[OPC 53-Gr NRSd],[PPC Gr MRPd],[PPC Gr-NRSd])

) as pivotTable1

Remove extra column from inner select statement OR make sure those fields have same values for all of the rows for which you want to have single row output.

 

Just select "Grade_a" and "opStkMTS" from inner statement to test it and than add other columns one by one.