Our dba is away on long term sick at the moment and this has fallen to me ('the network guy') to sort out as I've used SQL "once or twice". :rolleyes:

I have one further query for this and then I'm done I promise. 😛

Using the tables posted here: http://www.sqlservercentral.com/Forums/FindPost1485573.aspx

I need to get: doctor name, doctor location, drug name, drug packsize. I then need to do a total number of drugs for that doctor.

I started with something like this:

select d.doctorname, d.location, dg.name, dg.packsize,

Count (dg.packsize) as 'Total Drugs'

from Doctor_Prescribed_Drugs dpd

left join Doctor d on dpd.doctorref = d.doctorref

left join drugs dg on dpd.drugref = dg.drugref

where d.Location in ('London', 'Manchester')

and dg.PackSize is not null

group by d.doctorname, d.location, dg.name, dg.PackSize

with rollup

order by [Total Drugs]

This sort of gives me the answer I need, but the results are a mess and not really presentable to the end customer.

Any further suggestions?

Thanks in advance; you guys are great.