BAQ Totals Not Matching

Kinetic ERP
, ,
1

I have an issue where the counts in my summary BAQ are not matching the details, and I can’t figure out why. I’m hoping some of the SQL experts here can help spot my (probably simple) mistake.

This BAQ will be used in a Dashboard (classic) to show Case counts by Topic over time. It takes in 2 parameters - StartDate & EndDate.

I put the BAQ together, got my results, then went to make sure they matched the count when I queried the case details and added up the count - and they didn’t. The summary query always returns more records than the detail query. I’ve tried adding DISTINCT to the InnerSubqueries and that doesn’t seem to make a difference.

Any help would be greatly appreciated. Here’s the BAQ. The first InnerSubquery is a technique I found on here to generate an on-the-fly date table.

select 
	[Dates].[Calculated_YearVal] as [Calculated_YearVal],
	[Dates].[Calculated_MonthVal] as [Calculated_MonthVal],
	[Dates].[Calculated_MonthName] as [Calculated_MonthName],
	(COUNT(Complaints.HDCase_HDCaseNum)) as [Calculated_ComplaintCount],
	(COUNT(CustomerInduced.HDCase1_HDCaseNum)) as [Calculated_CustomerInducedCount],
	(COUNT(InternalIssues.HDCase2_HDCaseNum)) as [Calculated_InternalIssueCount],
	(COUNT(Kudos.HDCase3_HDCaseNum)) as [Calculated_KudosCount],
	(COUNT(SalesSupport.HDCase4_HDCaseNum)) as [Calculated_SalesSupportCount]
from  (select distinct
	((ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC))) as [Calculated_Row_Num],
	((DATEADD(DAY, Row_Num, '1/1/2020'))) as [Calculated_Date],
	(datepart(year, Date)) as [Calculated_YearVal],
	(datepart(month, Date)) as [Calculated_MonthVal],
	(datename(month, Date)) as [Calculated_MonthName],
	(datepart(wk, Date)) as [Calculated_WeekVal]
from Ice.ZDataField as ZDataField)  as Dates
left outer join  (select 
	[HDCase].[Company] as [HDCase_Company],
	[HDCase].[HDCaseNum] as [HDCase_HDCaseNum],
	[HDCase].[CreatedDate] as [HDCase_CreatedDate],
	(datepart(year, HDCase.CreatedDate)) as [Calculated_YearVal],
	(datepart(month, HDCase.CreatedDate)) as [Calculated_MonthVal],
	(datename(month, HDCase.CreatedDate)) as [Calculated_MonthName]
from Erp.HDCase as HDCase
where (HDCase.TopicID1 = 'Complaint'))  as Complaints on 
	Dates.Calculated_Date = Complaints.HDCase_CreatedDate
left outer join  (select 
	[HDCase1].[Company] as [HDCase1_Company],
	[HDCase1].[HDCaseNum] as [HDCase1_HDCaseNum],
	[HDCase1].[CreatedDate] as [HDCase1_CreatedDate],
	(datepart(year, HDCase1.CreatedDate)) as [Calculated_YearVal],
	(datepart(month, HDCase1.CreatedDate)) as [Calculated_MonthVal],
	(datename(month, HDCase1.CreatedDate)) as [Calculated_MonthName]
from Erp.HDCase as HDCase1
where (HDCase1.TopicID1 = 'Customer Induced'))  as CustomerInduced on 
	Dates.Calculated_Date = CustomerInduced.HDCase1_CreatedDate
left outer join  (select 
	[HDCase2].[Company] as [HDCase2_Company],
	[HDCase2].[HDCaseNum] as [HDCase2_HDCaseNum],
	[HDCase2].[CreatedDate] as [HDCase2_CreatedDate],
	(datepart(year, HDCase2.CreatedDate)) as [Calculated_YearVal],
	(datepart(month, HDCase2.CreatedDate)) as [Calculated_MonthVal],
	(datename(month, HDCase2.CreatedDate)) as [Calculated_MonthName]
from Erp.HDCase as HDCase2
where (HDCase2.TopicID1 = 'Internal Issue'))  as InternalIssues on 
	Dates.Calculated_Date = InternalIssues.HDCase2_CreatedDate
left outer join  (select 
	[HDCase3].[Company] as [HDCase3_Company],
	[HDCase3].[HDCaseNum] as [HDCase3_HDCaseNum],
	[HDCase3].[CreatedDate] as [HDCase3_CreatedDate],
	(datepart(year, HDCase3.CreatedDate)) as [Calculated_YearVal],
	(datepart(month, HDCase3.CreatedDate)) as [Calculated_MonthVal],
	(datename(month, HDCase3.CreatedDate)) as [Calculated_MonthName]
from Erp.HDCase as HDCase3
where (HDCase3.TopicID1 = 'Kudos'))  as Kudos on 
	Dates.Calculated_Date = Kudos.HDCase3_CreatedDate
left outer join  (select 
	[HDCase4].[Company] as [HDCase4_Company],
	[HDCase4].[HDCaseNum] as [HDCase4_HDCaseNum],
	[HDCase4].[CreatedDate] as [HDCase4_CreatedDate],
	(datepart(year, HDCase4.CreatedDate)) as [Calculated_YearVal],
	(datepart(month, HDCase4.CreatedDate)) as [Calculated_MonthVal],
	(datename(month, HDCase4.CreatedDate)) as [Calculated_MonthName]
from Erp.HDCase as HDCase4
where (HDCase4.TopicID1 = 'Sales Support'))  as SalesSupport on 
	Dates.Calculated_Date = SalesSupport.HDCase4_CreatedDate
where (Dates.Calculated_Date >= @StartDate  and Dates.Calculated_Date <= @EndDate)
group by [Dates].[Calculated_YearVal],
	[Dates].[Calculated_MonthVal],
	[Dates].[Calculated_MonthName]

Cases-Summary.baq (95.5 KB)

2

I can’t get any results, because we don’t use those fields, but I bet it has something to do with using that zdata table for the dates. I think you’ll be better off using the case table to get the dates. Just only return the date from the HD records.

I also don’t think you need all of those subqueries. You can just make case statetements on a single table.

I modified your query to be simpler. Give that a try and see if that returns better results.

Cases-Summary-tweaked.baq (62.6 KB)

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[Dates].[Calculated_YearVal] as [Calculated_YearVal],
	[Dates].[Calculated_MonthVal] as [Calculated_MonthVal],
	[Dates].[Calculated_MonthName] as [Calculated_MonthName],
	(sum ( case when HDCase_TopLevel.TopicID1 = 'Complaint' then 1
 else 0
 end)
 
 --COUNT(Complaints.HDCase_HDCaseNum)) as [Calculated_ComplaintCount],
	(sum ( case when HDCase_TopLevel.TopicID1 = 'Customer Induced' then 1
 else 0
 end)
 
 --COUNT(CustomerInduced.HDCase1_HDCaseNum)) as [Calculated_CustomerInducedCount],
	(sum ( case when HDCase_TopLevel.TopicID1 = 'Internal Issue' then 1
 else 0
 end)
 
 --COUNT(InternalIssues.HDCase2_HDCaseNum)) as [Calculated_InternalIssueCount],
	(sum ( case when HDCase_TopLevel.TopicID1 = 'Kudos' then 1
 else 0
 end)
 
 --COUNT(Kudos.HDCase3_HDCaseNum)) as [Calculated_KudosCount],
	(sum ( case when HDCase_TopLevel.TopicID1 = 'Sales Support' then 1
 else 0
 end)
 
 --COUNT(SalesSupport.HDCase4_HDCaseNum)) as [Calculated_SalesSupportCount]
from  (select distinct
	(HDCase_date.CreatedDate) as [Calculated_Date],
	(datepart(year, Date)) as [Calculated_YearVal],
	(datepart(month, Date)) as [Calculated_MonthVal],
	(datename(month, Date)) as [Calculated_MonthName],
	(datepart(wk, Date)) as [Calculated_WeekVal]
from Erp.HDCase as HDCase_date)  as Dates
left outer join Erp.HDCase as HDCase_TopLevel on 
	Dates.Calculated_Date = HDCase_TopLevel.CreatedDate
	and ( HDCase_TopLevel.TopicID1 in ('Complaint', 'Customer Induced', 'Internal Issue', 'Kudos', 'Sales Support')  )

where (Dates.Calculated_Date >= @StartDate  and Dates.Calculated_Date <= @EndDate)
group by [Dates].[Calculated_YearVal],
	[Dates].[Calculated_MonthVal],
	[Dates].[Calculated_MonthName]
3

You were exactly right - your modified query sums up to the correct values. Thank you for your help!

Do you have a recommendation on a date dimension table for reporting, or do you find you don’t need one? We’re hosted, so I can’t create my own.

4

That’s a pretty open ended question, and it’s going to depend on what you need for your report. Most of the time when I need dates, I use one of the tables related to what I’m doing to get the dates out of there. I just pick only the date field then group by, or return only distinct, then go from there. If any of the days are missing then there isn’t any data there anyways. Otherwise I’ve use the labor head, since there should be a record for every working day. But it always depends on what you are doing.

1 Like