CREATE VIEW MyView AS
(SELECT COUNT(*) FROM AppsA WHERE Client.Id = AppsA.ClientId) as NrAppsA_Appointments,
(SELECT COUNT(*) FROM AppsB WHERE Client.Id = AppsB.ClientId) as NrAppsB_Appointments,
(SELECT COUNT(*) FROM AppsC WHERE Client.Id = AppsC.ClientId) as NrAppsC_Appointments
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Thanks for this, it has worked a treat! See code below
(SELECT COUNT(*) FROM ework.Consumer_Management WHERE ework.Affiliate_Sales_Management.fld_id = ework.Consumer_Management.cli_id AND ework.Consumer_Management.con_status LIKE 'COMPLETE') AS Consumer_Appointments,
(SELECT COUNT(*) FROM ework.Commercial_Opportunity WHERE ework.Affiliate_Sales_Management.fld_id = ework.Commercial_Opportunity.cli_id AND ework.Commercial_Opportunity.com_status LIKE 'COMPLETE') AS Commercial_Appointments,
(SELECT COUNT(*) FROM ework.Professional_Opportunity WHERE ework.Affiliate_Sales_Management.fld_id = ework.Professional_Opportunity.cli_id AND ework.Professional_Opportunity.pro_status LIKE 'COMPLETE') AS Professional_Appointments
The only improvement I want to carry out is that every appointment has a certain status - i.e. Complete, Re-booked etc. As you may see from the code above, I have tried to implement this by adding the following line of code to each select count statement:
AND ework.Consumer_Management.con_status LIKE 'COMPLETE'
Unfortunately, this brings back incorrect data (I have checked the original tables).
Any ideas? Sorry, quit new to SQL and still getting my head round some of the logic.