Hi,
I am having a hard time in making case expression,
I have created a Stored Procedure and testing it, but it is not working.
Shows
Msg 156, Level 15, State 1, Line 76
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 76
Incorrect syntax near the keyword 'and'
select ReportingUnit,SUM(UsedQuantity) as SumOfUsedQuantity,
case when ReportingUnit=1
then (select COUNT(Id) from FloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=2
then (select COUNT(Id) from FlatsPerFloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=3
then (select COUNT(Id) from ColumnsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=4
then (select COUNT(Id) from FootingsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=5
then 1 end as TotalFlat,
case when (CAST(SUM(UsedQuantity) as decimal(18,2))/CAST((
case when ReportingUnit=1
then (select COUNT(Id) from FloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=2
then (select COUNT(Id) from FlatsPerFloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=3
then (select COUNT(Id) from ColumnsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=4
then (select COUNT(Id) from FootingsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=5
then 1 end
) as decimal(18,2))) <= 100 then ((CAST(SUM(UsedQuantity) as decimal(18,2))/CAST((
case when ReportingUnit=1
then (select COUNT(Id) from FloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=2
then (select COUNT(Id) from FlatsPerFloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=3
then (select COUNT(Id) from ColumnsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=4
then (select COUNT(Id) from FootingsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=5
then 1 end
) as decimal(18,2)))) ELSE 100 END as WorkCompletion,(select ISNULL(SUM(ISNULL(PercentageWeightage,0)),0) from BuildingWiseSubActivityWeightage where BuildingId=fullData.BuildingID and BuildingWiseSubActivityWeightage.SubActivityId=fullData.SubActivityId) as PercentageWeightage,
CAST((
case when CAST(SUM(UsedQuantity) as decimal(18,2))/CAST((
case when ReportingUnit=1
then (select COUNT(Id) from FloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=2
then (select COUNT(Id) from FlatsPerFloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=3
then (select COUNT(Id) from ColumnsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=4
then (select COUNT(Id) from FootingsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=5
then 1 end
) as decimal(18,2)) <= 100 then CAST(SUM(UsedQuantity) as decimal(18,2))/CAST((
case when ReportingUnit=1
then (select COUNT(Id) from FloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=2
then (select COUNT(Id) from FlatsPerFloorBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=3
then (select COUNT(Id) from ColumnsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=4
then (select COUNT(Id) from FootingsBuildingMaster where BuildingID=fullData.BuildingID)
when ReportingUnit=5
then 1 end
) as decimal(18,2))
ELSE 100 END * (select ISNULL(SUM(ISNULL(PercentageWeightage,0)),0) from BuildingWiseSubActivityWeightage
where BuildingId=fullData.BuildingID and BuildingWiseSubActivityWeightage.SubActivityId=fullData.SubActivityId)/100
)as decimal(18,2)) as Progress,SubActivityId,ActivityId,BuildingId into #temp3 from fullData group by SubActivityId,ActivityId,fullData.BuildingId,fullData.ReportingUnit;
