SQL to add not working

I have the following SQL. It is supposed to add items even when cases.empid or d.billingcaetgoryid are NULL, but it doesnt consider either of those when the value is NULL, how to fix ?



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
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
1 + NULL = NULL
20 * NULL = NULL
Sum(the universe, where anything = NULL) = NULL

ONe of the quickest ways to pull this off is to use the COALESCE function to convert nulls to zeros.
SELECT dbo.Cases.FirmId , SUM(COALESCE(BillingLines.PmtRecd,0) ) - SUM(COALESCE(BillingLines.UnitCostm,0)  * COALESCE(BillingLines.Qty,0)) AS TOTAL

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw I cleaned up your SQL..
SELECT c.FirmId, (SUM(bl.PmtRecd)- SUM(bl.UnitCost)* bl.Qty) AS TOTAL
FROM BillingLines bl         
INNER JOIN Cases c ON c.Id = bl.CaseId          
	LEFT JOIN Users AS u ON u.userid = c.EmpId          
	INNER JOIN BillingItems bi ON bi.itemid = bl.Itemid          
	LEFT JOIN BillingCategories bc ON bc.billingcategoryid =  bi.catalogid 
WHERE c.FirmId = MMColParam          
	AND u.archived <> 1          
	AND ISNULL(c.EmpId, '') LIKE 'MMColParam2'
	AND ISNULL(d.BillingCategoryid, '') LIKE 'MMColParam3'  
GROUP BY c.FirmId  
HAVING  SUM(bl.PmtRecd) - SUM(bl.UnitCost * bl.Qty) < 0 

Open in new window

0
AleksAuthor Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AleksAuthor Commented:
Cleanup didnt work i get some erros saying that some fields do not exist.
0
Brian CroweDatabase AdministratorCommented:
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...
0
Brian CroweDatabase AdministratorCommented:
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'
0
AleksAuthor Commented:
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
0
AleksAuthor Commented:
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
0
Brian CroweDatabase AdministratorCommented:
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.
0
AleksAuthor Commented:
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.
0
Brian CroweDatabase AdministratorCommented:
To Where?  You don't have any parameters in the query you list.  Is this dynamic SQL?
0
PortletPaulfreelancerCommented:
?confused... http:#39528937
is that one query or two? If one query what is missing? (a union/union all)
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

??????????????????????????????????
     what is happening here?
??????????????????????????????????

Open in new window

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

Open in new window

additionally in the lower query, the where clause will cause the effect of an inner join:

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

try this instead:
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
        AND d.BillingCategoryid LIKE 'MMColParam3'
WHERE Cases.FirmId = MMColParam
        AND cases.archived <> 1
        AND cases.EmpId LIKE 'MMColParam2'

Open in new window

0
AleksAuthor Commented:
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
0
PortletPaulfreelancerCommented:
I suggest you move one where condition to a join condition (see line 14)
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

        AND ISNULL(d.BillingCategoryid, '') LIKE 'MMColParam3'   /* THIS ROW */
        /* TO here ****************** */

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

        /* move FROM here ********************* */

GROUP BY Cases.FirmId
HAVING (SUM(BillingLines.PmtRecd)) - (SUM(BillingLines.UnitCost * BillingLines.Qty)) < 0

Open in new window

after making that move and testing,  
you could then also remove the use of ISNULL() in lines 14 and 19
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
The last sql posted doesn't give any errors, but it still does NOT add the items in which 'billingcatelgoryid' or 'empid' are null
0
AleksAuthor Commented:
Thanks between all I found the issue
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.