Declare @PeriodType varchar(10)='1'
Declare @month varchar(10)='03'
Declare @year varchar(10)='2018'
SELECT Shrimp_Name,Grades,Japan,EU,US
FROM (
SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end)) as 'Price' from
Tbl_Shrimp_Price TSP
inner join Tbl_Mast_Shrimp_Region TMR On TMR.ID=TSP.Region
inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade
inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType
WHERE TSP.ShrimpType=1 and TSP.PeriodType=@PeriodType AND
DATEPART(year, CONVERT(date, PostMonth, 105)) = @year
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month
) as s
PIVOT
(
SUM(Cast(Price) as Decimal(18,5)
FOR [Region_Name] IN (Japan, EU, US)
)AS pvt
ORDER BY Shrimp_Name,Grades
SELECT Shrimp_Name,Grades,Japan,EU,US
FROM (
SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end) as 'Price' from
Tbl_Shrimp_Price TSP
inner join Tbl_Mast_Shrimp_Region TMR On TMR.ID=TSP.Region
inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade
inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType
WHERE TSP.ShrimpType=2 and TSP.PeriodType=@PeriodType
AND
DATEPART(year, CONVERT(date, PostMonth, 105)) = @year
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month
) as s
PIVOT
(
SUM(Cast(Price) as Decimal(18,5)
FOR [Region_Name] IN (Japan, EU, US)
)AS pvt
ORDER BY Shrimp_Name,Grades
What I have tried:
Above is my Query and i want to Replace instead of 0.00 to '-'
and i am getting error above is my query so please help me-
error after execution-
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'Price'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '('.