Morning,
I'm not sure if this is the right place so please move if not. I am relatively new to SQL and I have the following issue.
I have 4 tables: 3 have invoice data and the other has site, account and meter details data. I need to bring back the number of invoices paid between two dates for meter type 1,2,3,4, the cost of these, the number of active sites across the whole portfolio and number of active accounts.
So far I have:
PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Type] nvarchar;
SELECT count () as 'Number Of Invoices', sum(d.cost) as 'total'
FROM dataelectricity as d inner join points as p on d.point_id = p.id
WHERE ((D.Date_paid)>=[Start Date] And (D.Date_paid)<=[End Date])
and p.other_number = '[Type]'
UNION ALL
SELECT count () as 'Number Of Invoices', sum(d.cost) as 'total'
FROM DATAGENERAL as d inner join points as p on d.point_id = p.id
WHERE ((D.Date_paid)>=[Start Date] And (D.Date_paid)<=[End Date])
and p.other_number = '[Type]'
UNION ALL
SELECT count (*) as 'Number Of Invoices', sum(d.cost) as 'total'
FROM DATAWATER as d inner join points as p on d.point_id = p.id
WHERE ((D.Date_paid)>=[Start Date] And (D.Date_paid)<=[End Date])
and p.other_number = '[Type]'
And a separate one for the active sites:
PARAMETERS [End Date] DateTime;
select count (distinct c.id) as 'active sites', count (distinct p.id) as 'active datasets'
from contacts as c
inner join points as p on c.id = p.contacts_id
where ((p.closed_date) is null or (p.closed_date) >[End Date])
I also need to bring back the account numbers but if I put this into either of the above, the numbers are changed due to the date parameters. The accounts need to be split per meter type which is causing the issue.
Is it possible to put all of these in 1 piece of SQL using select in selects to avoid the data parameters on some criteria?
Thanks
I'm on SQL management studio 2008