Database

   ( CASE WHEN ( SELECT TOP 1
                      ATTRIB_CODE
            FROM      SDE.[NOTIFICATION] WITH ( NOLOCK )
            WHERE     ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = LP.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                        AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      )
                      AND ARCHIVE_DATE IS NULL
          ) LIKE '00090009%'
     THEN ( SELECT TOP 1
                      ATTRIB_CODE COLLATE SQL_Latin1_General_CP1_CI_AS
            FROM      SDE.[NOTIFICATION] N WITH ( NOLOCK )
            WHERE     N.ARCHIVE_DATE IS NULL
                      AND N.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      AND N.LIS_KEY  COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      AND N.ATTRIB_CODE LIKE '00090009%'
                      AND N.ARCHIVE_DATE IS NULL
                      AND V.ARCHIVE_DATE IS NULL
          )
     ELSE ISNULL(( SELECT TOP 1
                          ATTRIBUTE_CODE
                   FROM   SDE.VALUATION WITH ( NOLOCK )
                   WHERE  ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                            AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                          )
                          AND VAL_STATUS_ID = 2
                          AND ARCHIVE_DATE IS NULL
                 ), '')
END ) AS ATTRIBUTE_CODE ,

Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
"   h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "

queryString +=
" UNION ALL "

queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
"   v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO "

SELECT COUNT(FITEMNO) AS hCount
 FROM
   ( 
     SELECT
     FITEMNO
     FROM ARTRS01H.dbf
     WHERE
     FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
     AND
     FCUSTNO = @FCUSTNO
     AND
     FITEMNO = @FITEMNO
   	GROUP BY FITEMNO
   	UNION ALL
    SELECT
	FITEMNO
    FROM ARTRS01H.dbf
    WHERE
    FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
    AND
    FCUSTNO = @FCUSTNO
    AND
    FITEMNO = @FITEMNO
    GROUP BY FITEMNO
 ) 

USE [Staging]
GO

/****** Object:  UserDefinedFunction [dbo].[Devide_Shipping_Unit]    Script Date: 21/10/2014 11:46:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Devide_Shipping_Unit]

(
		@ValueString nvarchar(8),
		@ArticleString nvarchar(8) 
)
RETURNS Int

AS
BEGIN

DECLARE @result  int	

	IF ISNUMERIC(@ValueString)<> 1 OR ISNUMERIC(@ArticleString)<> 1
		BEGIN 
			set @result  = 0
		END 
	ELSE
		BEGIN 
						
			if  Cast(@ArticleString AS int) <= 99999  or @ArticleString = '4859696' 
				set @result  = 1 --CAST(@ValueString AS  int) / 1000
			Else 
				set @result  =CAST(@ValueString AS  int) / 100
				
		END 
	return @result

	END
GO

ALTER FUNCTION [dbo].[CONVERT_DATE_TO_NUMERIC]
(
		@dateValueString nvarchar(10)
)
RETURNS Numeric(8, 0)
AS
BEGIN

DECLARE @result  Numeric(8, 0)

		
	--DECLARE @dateValueString nvarchar(10)  = '03/12/2013'
	DECLARE @startdate nvarchar(10)  = ''

--		set @dateValueString = '2013-07-23'
	set @dateValueString = REPLACE(@dateValueString,'/','')
	set @dateValueString = REPLACE(@dateValueString,'-','')

	declare  @_year nvarchar(4) =  LEFT(@dateValueString,4)
	declare  @_month nvarchar(2) =  SUBSTRING(@dateValueString,5,2)
	declare  @_day nvarchar(2) =  RIGHT(@dateValueString,2)


	set @startdate = @_year + @_month + @_day

	IF ISNUMERIC(@startdate)<> 1
		BEGIN 
			set @result  = 0
		END 
	ELSE
		BEGIN 
			set @result  = @startdate 		END 


	return @result

	END


GO