Problem with not between

[naina]

Hi everyone,

here is my Sp of A2003 Project to calculate the gross amount but does not return the desired result
First I wrote the sp in this way..

CREATE Procedure dbo.GetHDCBillGrs
AS
SET NOCOUNT ON
BEGIN
DECLARE @HGrs float
SET @HGrs=(Select isnull(Sum(HDC_BillChildT.Codeamount),0)
From dbo.HDC_EconomicT Inner JOIN
dbo.HDC_BillChildT ON dbo.HDC_EconomicT.EcoiD=dbo.HDC_BillChildT.Economic
Where [b]dbo.HDC_EconomicT.Economic not between '0001' AND '4399'
AND dbo.HDC_EconomicT.Economic not between '8000' AND '8999'[/b]
AND dbo.HDC_BillChildT.HBP_iD=(Select TOP 1 HBP_iD From dbo.HDC_BillParT  ORDER BY  HBP_iD DESC))
SELECT @HGrs AS Gross
END
GO

Result shows but first two criteria does not meet. Economic Field is a Varchar Datatype that's why I Change the Query in this way

CREATE Procedure dbo.GetHDCBillGrs
AS
SET NOCOUNT ON
BEGIN
DECLARE @HGrs float
SET @HGrs=(Select isnull(Sum(HDC_BillChildT.Codeamount),0)
From dbo.HDC_EconomicT Inner JOIN
dbo.HDC_BillChildT ON dbo.HDC_EconomicT.EcoiD=dbo.HDC_BillChildT.Economic
Where Convert(int, dbo.HDC_EconomicT.Economic) not between 0001 AND 4399
AND Convert(int, dbo.HDC_EconomicT.Economic) not between 8000 AND 8999
AND dbo.HDC_BillChildT.HBP_iD=(Select TOP 1 HBP_iD From dbo.HDC_BillParT  ORDER BY  HBP_iD DESC))
SELECT @HGrs AS Gross
END
GO

Same result these Criteria does not meet
<Where Convert(int, dbo.HDC_EconomicT.Economic) not between 0001 AND 4399
AND Convert(int, dbo.HDC_EconomicT.Economic) not between 8000 AND 8999>


any help would be appreciated.

<<<<Naina>>>

 

[naina]

Hi every one I solved this problem !
Here is the Modified query, But I'm not sure is it a good logic what I'm applying on this Query ? Would you anyone suggest me . . .

CREATE Procedure dbo.GetHDCBillGrs
    AS
    SET NOCOUNT ON
    BEGIN
    DECLARE @HGrs float
            
            SET @HGrs=(Select isnull(Sum(HDC_BillChildT.Codeamount),0)
                From dbo.HDC_EconomicT Inner JOIN
                    dbo.HDC_BillChildT ON dbo.HDC_EconomicT.EcoiD=dbo.HDC_BillChildT.Economic
    Where dbo.HDC_EconomicT.Economic not between  '0100' AND  '4399'
        AND dbo.HDC_EconomicT.Economic NOT LiKE   '8%' --code between 8001 and 8999
AND dbo.HDC_BillChildT.HBP_iD=(Select TOP 1 HBP_iD From dbo.HDC_BillParT  ORDER BY  HBP_iD DESC))
    
            SELECT @HGrs AS Gross
    END
    GO

>>>Naina<<<