Hello All,
I have two tables as shown in the attachement.
I have written a query as shown below
`select sum(income) income,sum(expenditure) expenditure,(sum(income)-sum(expenditure)) netincome,vehicleid,incomedate,expdate
from
(
select vehicleid,income income,0 expenditure,convert(date,convert(varchar,transdate,1)) incomedate, convert(date,convert(varchar,transdate,1)) expdate from dbo.VehicleIncome
union all
select vehicleid,0 income,expenditure expenditure,convert(date,convert(varchar,transdate,1)) incomedate,convert(date,convert(varchar,transdate,1)) expdate from dbo.VehicleExpenditure
) t
group by vehicleid,incomedate,expdate`
My aim is to get only one record, since only one Vehicle details are available, but the result shows three. How do I modify my query to sum both the income and expenditure of a Vehicle, whiles including the transaction dates.
Any help would be appreciated.
Thanks
Tables: