Trying to filter between dates before group by

1

select DDate
from dbo.vw_LedgerTransactions
where (DDate >= '2007-05-01') AND (DDate <= '2007-05-1')

union

select refrence,
max(case when AccNumber = '1010000' then amount end) electricity,
max(case when AccNumber = '1045000' then amount end) water,
max(case when AccNumber = '1000000' then amount end) levy
from dbo.vw_LedgerTransactions
group by refrence

2

When you use UNION or UNION ALL construct, the SELECT preceding the UNION and the SELECT after the UNION should return the same number and type of columns.

In your first SELECT, you are trying to get a set of dates from column DDate. what are you trying to do in the SELECT after the UNION?

3

select refrence,
max(case when AccNumber = '1010000' then amount end) electricity,
max(case when AccNumber = '1045000' then amount end) water,
max(case when AccNumber = '1000000' then amount end) levy
from dbo.vw_LedgerTransactions
where (DDate >= '20070501') AND (DDate < '20070502')
group by refrence

4

thank you