none
Return 0 count when a Department within a CTE contains records for one set, but not another

    Question

  • This is a bit of a complicated question to explain, but I am hoping not as complicated to resolve. I will try to describe this generically to allow anybody who can provide a solution to provide a generic example that anybody could adapt to their data.

    In my query, I created a CTE which combines two separate sets of data. One set of data lists who is within a given audience (call it audiencelist). The other set lists people who are in that same audience and have completed a given course (call if courselist). This is then all brought into one table by the CTE.

    My resulting report in Visual Studio 2008 is then going to include an expression to count each employee from both lists by department within the same field. (I will achieve this by first grouping by department, and then by the appropriate measure, which in the case of this example, I am calling audiencelist and courselist. Then, I will include an expression that counts the Employees.) The problem is, if a department has somebody in the audience, but NOBODY from that department has completed the course yet, I need the department to still show up in the "courselist" but with a count of 0.

    Example of the desired result:

    Dept  Field             Count

    A       Audiencelist  25
    A       Courselist     15
    B       Audiencelist  20
    B       Courselist     10
    C       Audiencelist  20
    C       Courselist     0


    I was trying as best I can to make this a generic example, but let me know if it would be easier if I share some of my actual code so you can provide a solution. My hope was that somebody could offer a generic example I could adapt to my data.


    Thursday, April 10, 2014 8:31 PM

All replies

  • The generic solution for this kind of problem is to have the resulting list (two rows per each department) and LEFT JOIN from that list of everything into your actual ctes to get count or 0 in case of NULL.

    See http://archive.msdn.microsoft.com/SQLExamples solution called Find the Missing Parts in that list of common solutions to common problems.

    http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FindMissingListItems&referringTitle=Home


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, April 10, 2014 8:45 PM
    Moderator
  • create table Audiences (Dept char(1),  Field  varchar(50),           cnt int)
    insert into Audiences values ('A','Audiencelist',25),('B','Audiencelist',20),('C','Audiencelist',20)
    
    create table Courses (Dept char(1),  Field  varchar(50),           cnt int)
    insert into Courses values ('A','Courselist',15),('B','Courselist',10) 
    
    
    
    ;with mycte1 as (Select distinct Dept from (select dept from Audiences union all select dept from Courses)t )
    ,mycte2 as( Select distinct Field from (select Field from Audiences union all select Field from Courses  ) t)
    
    ,mycteFinal as (Select Dept, Field From mycte1,mycte2)
    
     
    
    Select m.Dept,m.Field,coalesce(a.cnt,c.cnt,0) as cnt from mycteFinal m 
    left join Audiences a  on a.Dept=m.Dept and a.Field=m.Field
    left join Courses c  on c.Dept=m.Dept and c.Field=m.Field
    
    
    drop table Courses, Audiences
    
    If you include DDL of your sample tables and sample data, it will be easy to get your ecpected result. Thanks.
    Thursday, April 10, 2014 8:56 PM
    Moderator
  • I am having a little trouble following this example, or the one provided at the link above. I was hoping to just allow for a generic example, but I guess it may help if I share my actual code. My query is rather long, but I will just share the relevant parts for the sake of the sample.

    gm101measures
    AS
    (
    select
    dimUser.EmpFK MeasureEmpFK,
    RIGHT(OrgCode2, LEN(OrgCode2) - 2) MeasurePC,
    audusersName MeasureAudName,
    dimActivity.ActivityName MeasureActName,
    dimActivity.Code MeasureActCode,
    CASE 
    WHEN OrgCode2 LIKE 'US%' then 'US'
    WHEN OrgCode2 LIKE 'CA%' then 'CA' END 
    CountryCode,
    'ACTUALS_GM101' SOURCESYSTEMID,
    Null CURRENCYCODE,
    'GM101CERT' MEASUREID,
    Null MEASUREDOLLARS,
    CASE
    WHEN GETDATE() Between '20131001 00:00:00' AND '20140930 11:59:59' THEN '2014'
    WHEN GETDATE() Between '20141001 00:00:00' AND '20150930 11:59:59' THEN '2015'
    WHEN GETDATE() Between '20151001 00:00:00' AND '20160930 11:59:59' THEN '2016'
    WHEN GETDATE() Between '20161001 00:00:00' AND '20170930 11:59:59' THEN '2017' END
    FiscalYear,
    CASE
    WHEN MONTH(GETDATE()) = '10' THEN '1'
    WHEN MONTH(GETDATE()) = '11' THEN '2'
    WHEN MONTH(GETDATE()) = '12' THEN '3'
    WHEN MONTH(GETDATE()) = '1' THEN '4'
    WHEN MONTH(GETDATE()) = '2' THEN '5'
    WHEN MONTH(GETDATE()) = '3' THEN '6'
    WHEN MONTH(GETDATE()) = '4' THEN '7'
    WHEN MONTH(GETDATE()) = '5' THEN '8'
    WHEN MONTH(GETDATE()) = '6' THEN '9'
    WHEN MONTH(GETDATE()) = '7' THEN '10'
    WHEN MONTH(GETDATE()) = '8' THEN '11'
    WHEN MONTH(GETDATE()) = '9' THEN '12' END
    FiscalMonthNbr
    
    from
    dimUser INNER JOIN
    audusers ON audusers.DataSetUsers_EmpFK = dimUser.EmpFK INNER JOIN
    Org ON dimUser.PrimaryDomFK = Org.Org_PK INNER JOIN
    factUserRequiredActivity ON factUserRequiredActivity.UserID = dimUser.ID INNER JOIN
    dimActivity  ON dimActivity.ID = factUserRequiredActivity.ActivityID INNER JOIN
    dimRequirementStatus ON factUserRequiredActivity.ReqStatusID = dimRequirementStatus.ID LEFT OUTER JOIN
    UsrOrgs ON dimUser.ID = UsrOrgs.UserID LEFT OUTER JOIN
    UsrDoms ON dimUser.ID = UsrDoms.UserID
    WHERE
    dimActivity.ActivityName = 'GM101 Program Completion'
    AND
    dimRequirementStatus.name = 'Satisfied'
    AND
    (audusersName = @audparam)
    UNION
    select
    dimUser.EmpFK MeasureEmpFK,
    RIGHT(OrgCode2, LEN(OrgCode2) - 2) MeasurePC,
    audusersName MeasureAudName,
    Null MeasureActName,
    Null MeasureActCode,
    CASE 
    WHEN OrgCode2 LIKE 'US%' then 'US'
    WHEN OrgCode2 LIKE 'CA%' then 'CA' END 
    CountryCode,
    'ACTUALS_GM101' SOURCESYSTEMID,
    Null CURRENCYCODE,
    'GM101AVAIL' MEASUREID,
    Null MEASUREDOLLARS,
    CASE
    WHEN GETDATE() Between '20131001 00:00:00' AND '20140930 11:59:59' THEN '2014'
    WHEN GETDATE() Between '20141001 00:00:00' AND '20150930 11:59:59' THEN '2015'
    WHEN GETDATE() Between '20151001 00:00:00' AND '20160930 11:59:59' THEN '2016'
    WHEN GETDATE() Between '20161001 00:00:00' AND '20170930 11:59:59' THEN '2017' END
    FiscalYear,
    CASE
    WHEN MONTH(GETDATE()) = '10' THEN '1'
    WHEN MONTH(GETDATE()) = '11' THEN '2'
    WHEN MONTH(GETDATE()) = '12' THEN '3'
    WHEN MONTH(GETDATE()) = '1' THEN '4'
    WHEN MONTH(GETDATE()) = '2' THEN '5'
    WHEN MONTH(GETDATE()) = '3' THEN '6'
    WHEN MONTH(GETDATE()) = '4' THEN '7'
    WHEN MONTH(GETDATE()) = '5' THEN '8'
    WHEN MONTH(GETDATE()) = '6' THEN '9'
    WHEN MONTH(GETDATE()) = '7' THEN '10'
    WHEN MONTH(GETDATE()) = '8' THEN '11'
    WHEN MONTH(GETDATE()) = '9' THEN '12' END
    FiscalMonthNbr
    
    from
    dimUser INNER JOIN
    audusers ON audusers.DataSetUsers_EmpFK = dimUser.EmpFK INNER JOIN
    Org ON dimUser.PrimaryDomFK = Org.Org_PK LEFT OUTER JOIN
    UsrOrgs ON dimUser.ID = UsrOrgs.UserID LEFT OUTER JOIN
    UsrDoms ON dimUser.ID = UsrDoms.UserID
    WHERE
    audusersName = @audparam
    )

    The final main query actually just pulls in everything from this CTE. My next step was going to be to pull this information all into the report, sort and group by MeasurePC (the Department, referred to in my company as a "Profit Center" and by the MeasureID (GM101Cert for those who completed the course, GM101Avail for those who are part of the audience) and then have an Expression that would count each MeasureEmpFK (the employee codes). However, that is when I realized it wouldn't count 0's for any MeasurePC in the GM101Cert set of data that did not have any people complete the course.

    Here is a mock-up of how the data would currently look from this above query:

    Country   Code SOURCESYSTEMID Fiscal Year Fiscal Month Nbr Org Value CURRENCYCODE MEASUREID MEASUREDOLLARS Measure Emp FK
    US ACTUALS_GM101 2014 7 Org A   GM101AVAIL   170445
    US ACTUALS_GM101 2014 7 Org A   GM101AVAIL   2671
    US ACTUALS_GM101 2014 7 Org A   GM101AVAIL   113
    US ACTUALS_GM101 2014 7 Org A   GM101AVAIL   271
    US ACTUALS_GM101 2014 7 Org B   GM101AVAIL   272
    US ACTUALS_GM101 2014 7 Org B   GM101AVAIL   317
    US ACTUALS_GM101 2014 7 Org B   GM101AVAIL   375
    US ACTUALS_GM101 2014 7 Org A   GM101CERT   170445
    US ACTUALS_GM101 2014 7 Org A   GM101CERT   2671

    From here, I was going to pull the data into a report to count. Using the above example, my desired result would be:

    Country   Code SOURCESYSTEMID Fiscal Year Fiscal Month Nbr Org Value CURRENCYCODE MEASUREID MEASUREDOLLARS Measure Emp FK
    US ACTUALS_GM101 2014 7 Org A   GM101AVAIL   4
    US ACTUALS_GM101 2014 7 Org A GM101CERT   2
    US ACTUALS_GM101 2014 7 Org B   GM101AVAIL   2
    US ACTUALS_GM101 2014 7 Org B   GM101CERT

    0

    I had planned to achieve this just by pulling the data into my report and then, instead of pulling the EmpFK, I would create an expression to count them. However, I realized this wouldn't achieve the 0 counts when some PC's did not have anybody who had completed the course yet.

    Friday, April 11, 2014 1:23 PM
  • In my opinion, both my and Jinguang's replies were absolutely clear, but it may be a bit hard to apply to your rather complex (and I would say with FiscalMonth/FiscalYear calculations needlessly complex) query.

    Once again, to produce the counts you need to have a list of all rows you need to have in your report. Generally such lists are produced by CROSS JOIN.

    Then from that list of everything you will join into your actual data and this way you'll get 0 counts in case you don't have corresponding row in the actual data.

    Run the sample provided and then think how to apply to your situation.

    And also please get rid of this stupid Fiscal year/month calculations - it makes me cringe. Use Calendar table approach I suggested in another thread.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, April 11, 2014 3:38 PM
    Moderator
  • Allow me to apologize if I did not make this clear before.... However I had not meant to imply that your answers were not clear, and/or were not correct. I think the more accurate statement is that they are not clear TO ME. I do not quite understand how these work. I'm confused as to what all of this is doing in order to know how to adapt it as appropriate for my situation.

    Unfortunately, this is another situation where it was requested that I create a brand new report, and we were not given very much advance notice on the need for it, so I am having to rush to figure it out.

    In thinking about it, I may have come up with a solution that will work for me, even if it is not as ideal as the ones you offered here. Perhaps you can share your thoughts on my idea.

    I was thinking, if I have a CTE perform the same counts I wanted to in the final report from the existing CTE (I can again group by Department and Measurement), this will give me the counts, by Department, for each of the two measurements. It will still NOT give me the 0 counts when a department does not yet have anybody who completed the course. However, what I can do is create a "mis-match" query between the list of those who completed the course and the list of those who were assigned to create another table containing just the Departments that do not have a record for the set of people who completed the course. That will allow me to create the table with the same fields, but instead of a count for the "Measure Amount" it will just display 0.

    I can pull all of that data into one table, and that should solve my problem. Again, perhaps not ideal, but with the short timeframe within which I was asked to complete this, I may have to go with what I have already learned how to do, if I can get it to work.

    Friday, April 11, 2014 5:49 PM