Field splicing Changer

Use [Ecom]
GO
/****** object:storedprocedure [dbo].    [R_merchant_preorder_chargesdata] Script date:03/15/2016 13:43:10 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo]. [R_merchant_preorder_chargesdata]
@Type Nvarchar (400),
@MerchantID Nvarchar (400),
@EffectiveDate Nvarchar (400),
@NarmalSelling Nvarchar (400)
As
BEGIN
if (@Type = ' without ')
Begin
if (@NarmalSelling = "or @MerchantID =")
Begin
Select ' Please Enter narmalselling '
Select ' Please Enter narmalselling '
End
Else
Begin
SELECT * Into #a from (
Select D.id,
D.merchantid,
D.state,
D.effectivedate,
D.chargestype,
D.chargesname,
D.chargescode,
D.invoicecol,
D.valuetype,
D.value,
@NarmalSelling as Narmalselling,
tax= case
When c.valuetype = "rate" then @NarmalSelling *abs (CAST (C.value as Decimal (18,2))
When c.valuetype = ' number ' then @NarmalSelling-abs (CAST (C.value as Decimal (18,2))
ELSE ' 0 ' END,
Comments = (select REPLACE (Case ValueType = "rate" Then
Cast (CAST (Value *) as float) as nvarchar () + '% ' else ' $ ' +
Cast (Value as Float) as nvarchar () + ' end, '-', ') + Chargescode
From Config_order_charges b where d.id = b.ID FOR XML Path ("))
From Config_order_charges D
Left Join
(SELECT *
From Config_order_charges
where ID in (
Select MAX (ID) as ID
From Config_order_charges A
INNER JOIN
(Select Chargesname,
MAX (effectivedate) effectivedate
From Config_order_charges
GROUP BY Chargesname,merchantid
Having merchantid = @MerchantID) b
On A.chargesname=b.chargesname and A.effectivedate=b.effectivedate
where A.merchantid = @MerchantID
Group by A.chargesname)) C
On c.id = D.id
where D.merchantid = @MerchantID) g
SELECT * from #a ORDER by Chargesname,effectivedate Desc,id desc
SELECT * Into #c from (select Merchantid,invoicecol,
SUM (tax) as Totaltaxvalue
From #a GROUP by Invoicecol,merchantid have [email protected]) H
Select A.invoicecol,
A.totaltaxvalue,
Stuff ((SELECT ' and ' + c.comments from #a as c Where c.merchantid = A.merchantid and A.invoicecol=c.invoicecol and C.tax ! = 0 FOR XML path (")), 1, 5, ') as Comments
From #c a
drop TABLE #a
drop table #c
End
End
else if (@Type = ' indoor ')
Begin
if (@NarmalSelling = "or @MerchantID =")
Begin
Select ' Please Enter narmalselling '
Select ' Please Enter narmalselling '
End
Else
Begin
SELECT * Into #b from (
Select D.id,
D.merchantid,
D.state,
D.effectivedate,
D.chargestype,
D.chargesname,
D.chargescode,
D.invoicecol,
D.valuetype,
D.value,
@NarmalSelling as Narmalselling,
tax= case
When c.valuetype = "rate" then @NarmalSelling *abs (CAST (C.value as Decimal (18,2))
When c.valuetype = ' number ' then @NarmalSelling-abs (CAST (C.value as Decimal (18,2))
ELSE ' 0 ' END,
Comments = (select REPLACE (Case ValueType = "rate" Then
Cast (CAST (Value *) as float) as nvarchar () + '% ' else ' $ ' +
Cast (Value as Float) as nvarchar () + ' end, '-', ') + Chargescode
From Config_order_chargestopreview b where d.id = b.ID FOR XML Path ("))
From Config_order_chargestopreview D
Left Join
(SELECT *
From Config_order_chargestopreview
where ID in (
Select MAX (ID) as ID
From Config_order_chargestopreview A
INNER JOIN
(Select Chargesname,
MAX (effectivedate) effectivedate
From Config_order_chargestopreview
GROUP BY Chargesname,merchantid
Having merchantid = @MerchantID) b
On A.chargesname=b.chargesname and A.effectivedate=b.effectivedate
where A.merchantid = @MerchantID
Group by A.chargesname)) C
On c.id = D.id
where D.merchantid = @MerchantID) H
SELECT * from #b ORDER by Chargesname,effectivedate Desc,id desc
SELECT * Into #d from (select Merchantid,invoicecol,
SUM (tax) as Totaltaxvalue
From #b
GROUP BY Invoicecol,merchantid
Having [email protected]) K
Select A.invoicecol,
A.totaltaxvalue,
Stuff ((SELECT ' and ' + c.comments from #b as c Where c.merchantid = A.merchantid and A.invoicecol=c.invoicecol and C.tax ! = 0 FOR XML path (")), 1, 5, ') as Comments
From #d A
drop table #b
drop table #d
End
End
END

Field splicing Changer