Forum Discussion
Counting Confusion
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.
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
Continued ContributorIf 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?