Create FUNCTION [dbo].[get_SerialNo]()

RETURNS VARCHAR(20)

AS

BEGIN 

declare

@x1 int,

@y1 char(8),

@z1 varchar(30)

select @x1= COUNT(SaleID) from SalesActivity where CAST(CreateDt as date)=CAST(GETDATE() as date)

if(@x1=0)

  begin

  select @y1=convert(char(8),getdate(),112)

  select @z1=

  end

else

 begin 

 select @z1='CX'+cast((select MAX(CAST( SUBSTRING(SaleID,3,12) as bigint))+1 from SalesActivity where CAST(CreateDt as date)=CAST(GETDATE() as date))as varchar(50))

 

  --insert TestNo(CharNo,DT)

  --values( 'WT'+cast((select MAX(CAST( SUBSTRING(charno,3,12) as bigint))+1 from TestNo where CAST(DT as date)=CAST(GETDATE() as date))as varchar(50)) ,GETDATE())

 end

 return @z1

end