SQLTeam.com | Weblogs | Forums

New to SQL, need a select in a select


#1

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


#2

Can you please post some sample data for the tables involved and the expected output from the proposed query, using the sample data?


#3

Thanks for replying...

Do you want an upload or just for me to type the column headings and some sample data? Not sure I'll be able to upload because of company firewall...

Thanks


#4

Create some test data showing your problem and post it this way:

  1. CREATE TABLE statements for all tables involved
  2. INSERT INTO statements to insert the test data the tables from step 1.

Only post just enough rows to show the problem and desired solution. Usually that is less than 10 rows per table.


#5

SELECT *
FROM @table
where
isnull(Desc1,'') = ''
OR isnull(Value1,'') = ''
OR isnull(Desc2,'') = ''
OR isnull(Value2,'') = ''
OR isnull(Desc3,'') = ''
OR isnull(Value3,'') = ''
OR isnull(Desc4,'') = ''
OR isnull(Value4,'') = ''