Querying multitable data

this is the query i tried to execute but it says couldn't find stored procedure all table.

alltable (SELECT convert(datetime, convert(char(10),[Date/Time], 101)) [Date/Time], [Through], [开发者_开发知识库Amount Received] FROM Transactions UNION ALL

SELECT convert(datetime, convert(char(10),[Date/Time], 101)) [Date/Time], [Through], [Amount Received] FROM DTH UNION ALL

SELECT convert(datetime, convert(char(10),[Date/Time], 101)) [Date/Time], [Through], [Amount Received] FROM Utilities UNION ALL

SELECT convert(datetime, convert(char(10),[Date/Time], 101)) [Date/Time], [Through], [Amount Received] FROM Mobile UNION ALL

SELECT convert(datetime, convert(char(10),[Date/Time], 101)) [Date/Time], [Through], [Amount Received] FROM ISP) SELECT convert(datetime, convert(char(10),[Date/Time], 101)) [Date/Time], [Through], SUM([Amount Received]) FROM alltable GROUP BY convert(datetime, convert(char(10),[Date/Time], 101)), [Through]

You could try something like this:

with AllTables as (
    select dateadd(day, 0, datediff(day, 0, [Date/Time])) as Date, Service, AmountReceived from Table1 union all
    select dateadd(day, 0, datediff(day, 0, [Date/Time])) as Date, Service, AmountReceived from Table2 union all
    select dateadd(day, 0, datediff(day, 0, [Date/Time])) as Date, Service, AmountReceived from Table3 union all
    select dateadd(day, 0, datediff(day, 0, [Date/Time])) as Date, Service, AmountReceived from Table4 union all
    select dateadd(day, 0, datediff(day, 0, [Date/Time])) as Date, Service, AmountReceived from Table5
)
select Date, Service, sum(AmountReceived) as AmountReceived
from AllTables
group by Date, Service

Just be sure, you use union all and not union.

This might not be efficient but it will work... first...

select the 3 fields you want from each table and union them together... as a view... then do you sum / group by on the view...

ex.... create view combined as Select DateField, Service, AmountRecieved from table1 union Select DateField, Service, AmountRecieved from table2 union Select DateField, Service, AmountRecieved from table3 ...

select sum(amountREcieved), DAteField, Service group by DAteField, Service

GL