Forum Discussion
Counting Confusion
I created a table to show records that have a count of 2 or more:
But when looking at my tables from the editor, I see these records only once:
What on my table could be causing these records to show a count of 2? I have no filters set on the top table except for companies with an ID that begin with the number 2.
I can see it returning more than one, based on how the count was created. E.g. if you took the service out, it could produce more than one row.
Try creating a table with all the columns in it (cs.co,cs.service, cs.servicelevel,cs.startdate,cs.endDate,einfo.id,empstatus), my guess is you have two different SeviceLevels, einfo.id or empStatus, or something.
6 Replies
What does the code look like for creating that table?
It's all SQL:
USE MillenniumLive
SELECT distinct cs.co,cs.service, cs.servicelevel,cs.startdate,cs.endDate,einfo.id,empstatus
FROM MillenniumLive.dbo.cService as cs
INNER JOIN MillenniumLive.dbo.sCompanySetDetail as csd ON csd.co = cs.co
AND csd.companySet IN ('CBIZ', 'TEAM 1', 'TEAM 2', 'TEAM 3', 'TEAM 4', 'TEAM 5', 'AES', 'CLIENT CONVERSION', 'ACCTMGT','EMS ONLY','TERM')
CROSS APPLY (SELECT top 1 co FROM MillenniumLive.dbo.CCalendar WHERE co = cs.co AND status = 'PROCESSED') as cc
WHERE ISNUMERIC(cs.co) = 1
AND cs.service in ('Payroll')
AND serviceLevel != 'Setup'
UNION ALL
SELECT distinct cs.co,cs.service, cs.servicelevel,cs.startdate,cs.endDate,einfo.id,empstatus
FROM MillenniumLive.dbo.cService as cs
INNER JOIN MillenniumLive.dbo.sCompanySetDetail as csd ON csd.co = cs.co
AND csd.companySet IN ('CBIZ', 'TEAM 1', 'TEAM 2', 'TEAM 3', 'TEAM 4', 'TEAM 5', 'AES', 'CLIENT CONVERSION', 'ACCTMGT','EMS ONLY', 'TERM')
AND cs.service in ('CBIZ EMS')
AND serviceLevel != 'Setup'
order by co
If you create a table visual and just put co=2049 and Service=Payroll in it. Is the count of rows equal to 1 or 2?