GetSerialNoByOrders

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go


--  =============================================
--  Author:        <Author,,Name>
--  Create date: <Create Date, ,>
--  Description:    <Description, ,>
--  =============================================
ALTER   FUNCTION   [ dbo ] . [ GetSerialNoByOrders ]  
(
@NowDate   as   datetime
)
RETURNS   char ( 12 )
AS
BEGIN
     --  Declare the return variable here
     DECLARE   @Result   char ( 12 )
     DECLARE   @Prefix   as   char ( 8 )
     DECLARE   @Suffix   as   char ( 4 )
     DECLARE   @MaxNum   as   int
    
     
     set   @Prefix   =  dbo.FormatDate2String( @NowDate )
    
     --  Add the T-SQL statements to compute the return value here
     select   @MaxNum   =   count (id)  +   1
     from  dbo.Orders
     where    datediff (d,CheckinTime, @NowDate )  =   0
     and  Status  <>   0

     if   @MaxNum   =   0
         begin
             set   @Suffix   =   ' 0001 '
         end
     else
         begin
             DECLARE   @Length   as   int
             DECLARE   @PrefixAddition   as   varchar ( 4 )
             set   @Length   =   4   -   len ( ( @MaxNum ) )
             set   @PrefixAddition   =   ''
             while ( @Length   >   0 )
                 begin
                     set   @PrefixAddition   =   ' 0 '   +   @PrefixAddition
                     set   @Length   =   @Length   -   1
                 end
             set   @Suffix   =   @PrefixAddition   +   cast  (  @MaxNum   as   varchar )

         end
     set   @Result   =   @Prefix   +   @Suffix
     --  Return the result of the function
     RETURN   @Result

END