Asked by:

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.

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.

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.

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.

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.

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

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.