question

Can someone help to the get the desired result with the required formulas ![alt text][1] [1]: /storage/temp/4113-desired-output.jpg **Below is my sql query:** declare @Fcur nvarchar(5) = '1' DECLARE @columns NVARCHAR(MAX) , @sql NVARCHAR(MAX) DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name +''+c.col) from FTFamStatGroup cross apply ( select 'SalesF' col union all select 'CostF' union all select 'ProfitF' union all select 'ProfitPercF' ) c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select cli_desc,amountTot,inv_trcharge ,RoundDiscountS, fam+case when Amount = ''SalesF'' then ''SalesF'' when Amount = ''CostF'' then ''CostF'' when Amount = ''ProfitF'' then ''ProfitF'' when Amount = ''ProfitPercF'' then ''ProfitPercF'' end NewCol , value from ( Select tblInvoice.cli_code , tblInvoice.Cli_Desc, tblInvoice.Name Fam ,amountTot,inv_trcharge,RoundDiscountS ,tblInvoice.SalesF,tblInvoice.CostF ,ProfitF= round((tblInvoice.SalesF - tblInvoice.CostF),2) ,ProfitPercF =round(case when tblInvoice.SalesF =0 then 0 else (tblInvoice.SalesF - tblInvoice.CostF)/abs(tblInvoice.SalesF) *100 end,2) from ( SELECT i.cli_code , c.Cli_Desc, fmg.Name ,SalesF = round(isnull(sum(id.Det_Total*(1-isnull(Inv_Discount,0)/100) * (case When ''1''=i.Cur_Code then ' + @Fcur+' else dbo.GetcurrencyRate(i.cur_code,' + @Fcur+',convert(nvarchar(20),i.inv_date,101)) end)* (case when isnull(inv_return,0) = 1 then -1 else 1 end )),0),2) ,CostF =round(isnull(sum(id.det_costf * id.det_qty * (case when isnull(inv_return,0) = 1 then -1 else 1 end )),0),2) ,tblall.amountTot,tblall.inv_trcharge ,tblall.RoundDiscountS FROM AccInvoice i INNER JOIN FTCLIENT c ON c.Cli_Code = i.Cli_Code and i.sys_stcode <> ''4'' and Inv_Date between ''01/01/2017'' and ''01/31/2017'' INNER JOIN AccInv_Detail id ON i.inv_nbr=id.inv_nbr inner join ftproduct prd on id.prd_code = prd.prd_code inner join ftFamily fm on prd.fam_code = fm.fam_code inner join FTFamStatGroup fmg on fmg.fam_code = fm.fam_code Left outer join ( SELECT i.cli_code ,amountTot =round(isnull(sum(i.Inv_NetTotal * dbo.GetcurrencyRate(i.cur_code,' + @Fcur+' ,convert(nvarchar(20),i.inv_date,101))* (case when isnull(inv_return,0) = 1 then -1 else 1 end )),0) ,2) ,inv_trcharge = isnull(sum(inv_trcharge * dbo.GetcurrencyRate(i.cur_code,' + @Fcur+' ,convert(nvarchar(20),i.inv_date,101))* (case when isnull(inv_return,0) = 1 then -1 else 1 end )),0) , RoundDiscountS = isnull(sum(case when isnull(Inv_Discount,0) = 0 then Inv_DiscountAmt else 0 end * dbo.GetcurrencyRate(i.cur_code,' + @Fcur+' ,convert(nvarchar(20),i.inv_date,101))* (case when isnull(inv_return,0) = 1 then -1 else 1 end )),0) FROM AccInvoice i inner join ( SELECT distinct i.inv_nbr FROM AccInvoice i INNER JOIN AccInv_Detail id ON i.inv_nbr=id.inv_nbr where i.inv_nbr=id.inv_nbr and Inv_Date between ''01/01/2017'' and ''01/31/2017'' and i.sys_stcode <> ''4'' and case when ''DXB'' =''-1'' then ''-1'' else i.sys_brcode end = ''DXB'' and case when ''-1''=''-1'' then ''-1'' else i.emp_code end =''-1'' and case when ''-1''=''-1'' then ''-1'' else id.wrh_code end =''-1'' )tblTemp on i.Inv_Nbr = tblTemp.Inv_Nbr where Inv_Date between ''01/01/2017'' and ''01/31/2017'' and i.sys_stcode <> ''4'' and case when ''DXB'' =''-1'' then ''-1'' else i.sys_brcode end = ''DXB'' and case when ''-1''=''-1'' then ''-1'' else i.emp_code end =''-1'' and i.cli_code = ''01060'' GROUP BY i.cli_code )tblall on tblall.cli_code = i.cli_code where case when ''DXB'' =''-1'' then ''-1'' else i.sys_brcode end = ''DXB'' and i.cli_code = ''01060'' GROUP BY i.cli_code ,fmg.Name,c.Cli_Desc ,tblall.amountTot,tblall.inv_trcharge ,tblall.RoundDiscountS HAVING isnull(sum(id.Det_Total*(1-isnull(Inv_Discount,0)/100) * dbo.GetcurrencyRate(i.cur_code,1,convert(nvarchar(20),i.inv_date,101))* (case when isnull(inv_return,0) = 1 then -1 else 1 end )),0) <> 0 )tblInvoice )TBLMain UNPIVOT( value for Amount in (SalesF, CostF,ProfitF,ProfitPercF)) unpiv )TBLMain2 PIVOT (max(value) for NewCol in (' + @cols + ')) piv' execute(@query)