SQL to add not working

NUll is the value of one of the fields that does NOT contain any monetary value, it is not being added, its a simple condition

Cleanup didnt work i get some erros saying that some fields do not exist.

What do you mean by "it doesn't consider".  Do you want to include or exclude records where empid and/or billingcategoryid = NULL?

ignore that...working...

If you want to include records where EmpId = NULL OR BillingCategorid = NULL then change your WHERE clause to:

WHERE Cases.FirmId = MMColParam
      AND cases.archived <> 1
      AND ISNULL(cases.EmpId, 'MMColParam2') LIKE 'MMColParam2'
      AND ISNULL(d.BillingCategoryid, 'MMColParam3') LIKE 'MMColParam3'

Doesnt work because if the value is NULL I pass on value %

I get this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value '%' to data type int.

/bluedot/Intranet/reports/standard/Billingowemoney.asp, line 128

SELECT  dbo.Cases.Id ,
        dbo.Cases.CaseId ,
        dbo.Cases.FirmId ,
        dbo.Cases.AlienId ,
        dbo.Users.UserId ,
        dbo.Users.MailCareOf ,
        dbo.Users.MailStr ,
        dbo.Users.MailApt ,
        dbo.Users.MailCity ,
        dbo.Users.MailState ,
        dbo.Users.MailZip ,
        dbo.Users.MailCntry ,
        dbo.Users.LastNm ,
        dbo.Users.FirstNm ,
        BillingLines.CaseId AS BCaseId ,
        SUM(BillingLines.UnitCost * Qty) AS SUM ,
        SUM(BillingLines.PmtRecd) AS RECTOT ,
        ( SUM(BillingLines.PmtRecd) ) - ( SUM(BillingLines.UnitCost
                                              * BillingLines.Qty) ) AS GRANDTOT ,
        c.MaidenNm AS EmployerName ,
        d.Category AS Category
FROM    ( ( dbo.Cases
          INNER JOIN dbo.Users ON dbo.Cases.AlienId = dbo.Users.UserId)
        )
        INNER JOIN dbo.BillingLines ON dbo.Cases.Id = dbo.BillingLines.CaseId
        LEFT JOIN Users AS c ON c.userid = cases.EmpId
        LEFT JOIN dbo.BillingItems ON dbo.Billingitems.itemid = dbo.BillingLines.Itemid
        LEFT JOIN BillingCategories AS d ON BillingItems.catalogid = d.billingcategoryid
SELECT  dbo.Cases.Id ,
        dbo.Cases.CaseId ,
        dbo.Cases.FirmId ,
        dbo.Cases.AlienId ,
        dbo.Users.UserId ,
        dbo.Users.MailCareOf ,
        dbo.Users.MailStr ,
        dbo.Users.MailApt ,
        dbo.Users.MailCity ,
        dbo.Users.MailState ,
        dbo.Users.MailZip ,
        dbo.Users.MailCntry ,
        dbo.Users.LastNm ,
        dbo.Users.FirstNm ,
        BillingLines.CaseId AS BCaseId ,
        SUM(BillingLines.UnitCost * Qty) AS SUM ,
        SUM(BillingLines.PmtRecd) AS RECTOT ,
        ( SUM(BillingLines.PmtRecd) ) - ( SUM(BillingLines.UnitCost
                                              * BillingLines.Qty) ) AS GRANDTOT ,
        c.MaidenNm AS EmployerName ,
        d.Category AS Category
FROM    ( ( dbo.Cases
          INNER JOIN dbo.Users ON dbo.Cases.AlienId = dbo.Users.UserId)
        )
        INNER JOIN dbo.BillingLines ON dbo.Cases.Id = dbo.BillingLines.CaseId
        LEFT JOIN Users AS c ON c.userid = cases.EmpId
        LEFT JOIN dbo.BillingItems ON dbo.Billingitems.itemid = dbo.BillingLines.Itemid
        LEFT JOIN BillingCategories AS d ON BillingItems.catalogid = d.billingcategoryid
WHERE   Cases.FirmId = MMColParam
        AND cases.archived <> 1
        AND ISNULL(cases.EmpId, '') LIKE 'MMColParam2'
        AND ISNULL(d.BillingCategoryid, '') LIKE 'MMColParam3'
GROUP BY dbo.Cases.Id ,
        dbo.Cases.CaseId ,
        dbo.Cases.FirmId ,
        dbo.Cases.AlienId ,
        dbo.Users.UserId ,
        dbo.Users.LastNm ,
        dbo.Users.FirstNm ,
        BillingLines.CaseId ,
        dbo.Users.MailCareOf ,
        dbo.Users.MailStr ,
        dbo.Users.MailApt ,
        dbo.Users.MailCity ,
        dbo.Users.MailState ,
        dbo.Users.MailZip ,
        c.MaidenNm ,
        d.Category ,
        dbo.Users.MailCntry
HAVING  ( SUM(BillingLines.PmtRecd) ) - ( SUM(BillingLines.UnitCost
                                              * BillingLines.Qty) ) <> 0
ORDER BY cases.caseid

How are you populating the parameters?  You show them as strings but you seem to be using them as parameters.  You're leaving something pertinent out which is confusing the issue.

I pass either a number of the % value from a form. I want % when I want to consider ALL or the number for the specific id selected.

To Where?  You don't have any parameters in the query you list.  Is this dynamic SQL?

I think I am not explaining myself well, the above was another example of an SQL that works just fine. The one that is not including records in which should be included when Empid and BillingCategoryid are NULL.

-----

SELECT dbo.Cases.FirmId ,          ( SUM(BillingLines.PmtRecd) ) - SUM(BillingLines.UnitCost                                              * BillingLines.Qty) AS TOTAL
FROM BillingLines          INNER JOIN Cases ON dbo.Cases.Id = dbo.BillingLines.CaseId          LEFT JOIN Users AS c ON c.userid = cases.EmpId          INNER JOIN dbo.BillingItems ON dbo.Billingitems.itemid = dbo.BillingLines.Itemid          LEFT JOIN BillingCategories AS d ON BillingItems.catalogid = d.billingcategoryid
WHERE Cases.FirmId = MMColParam          AND cases.archived <> 1          AND ISNULL(cases.EmpId, '') LIKE 'MMColParam2'          AND ISNULL(d.BillingCategoryid, '') LIKE 'MMColParam3'  GROUP BY Cases.FirmId  HAVING  ( SUM(BillingLines.PmtRecd) ) - ( SUM(BillingLines.UnitCost                                                * BillingLines.Qty) ) < 0

The last sql posted doesn't give any errors, but it still does NOT add the items in which 'billingcatelgoryid' or 'empid' are null

Thanks between all I found the issue