Skip to Content
0
Former Member
May 20, 2016 at 11:37 AM

how to from single parameter to Multiple Parameter

19 Views

ALTER proc [dbo].[@SMS_RPT_Sales_SBC]

(

@FromDate Date,

@ToDate Date,

@Location int

)

as

begin

Select T0.CardName[Customer Name],T0.DocNum,convert(varchar(max),T0.DocDate,103)as date,sum(T1.Quantity)[Quantity],(isnull(T1.ItemCode,''))[Item Code],T1.LineTotal[Sale value],T2.FrgnName[Part No],T2.InvntryUom[UoM Name]

,T3.Chapter [Traiff],T5.Location,T0.TotalExpns[Packing Charges],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('VAT')),0) [VAT],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('CST')),0) [CST],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('EX-DT','BED')),0) [ED 12.5%] ,

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry= T1.DocEntry and OSTT.name in('CESS')),0) [CESS 2%],

isnull((select Sum(taxsum) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.name in('VaT14.5')),0) [VAT 14.5],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('VAT')),0) [VAT Rate],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId =INV4.staType where INV4.DocEntry =T1.DocEntry and OSTT.Name in('CST')),0) [CST Rate],

isnull((select top 1 CONVERT(varchar(10),CONVERT(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.Name in ('EX-DT','BED')),0) [ED 12.5%],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.Name in ('CESS')),0) [CESS 2%],

isnull((select top 1 convert(varchar(10),convert(decimal(10,2),TaxRate)) from INV4 inner join OSTT on OSTT.AbsId=INV4.staType where INV4.DocEntry=T1.DocEntry and OSTT.Name in ('VAT14.5')),0) [VAT 14.5]

from OINV T0

inner join INV1 T1 on T1.DocEntry=T0.DocEntry

left Join OSTC T4 on T4.Code=T1.TaxCode

left join OITM T2 on T2.ItemCode=T1.ItemCode

inner join OLCT T5 on T5.Code=T1.LocCode

left join OCHP T3 on T3.AbsEntry=T2.ChapterID

where T0.DocEntry=T1.DocEntry and T1.LocCode =@Location and TaxDate Between @FromDate And @ToDate

Group By T0.CardName,T0.DocNum,T0.DocDate,T1.Quantity,T0.TotalExpns,T1.ItemCode,T1.LineTotal,T2.FrgnName,T2.InvntryUom ,T3.Chapter,T1.DocEntry,T5.Location

end

This is my query actully i give location parameter if i give 2 it will display one location name but i need to give mulitple location parameters how can suggestions me