I use below code,
the results I get is few lines per client.
I'd like to have it as one line per client.
please sample attached.
highlighted is what I'd like to have.
any help is appreciated,
Select fo.Accountnumber, (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber) as [AccountCode],
st.Description as [Service Type] ,SUM (SubTotalAmount) AS [SubTotalAmount]
FROM finalizedorders fo
LEFT JOIN Clients on fo.accountnumber = clients.accountnumber LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID LEFT JOIN VehicleTypes vt ON fo.VehicleTypeId = vt.VehicleTypeID
WHERE fo.servicetypeid = st.servicetypeid
and orderdate BETWEEN '2013-01-01' AND '2013-12-31'
Group by st.Description,st.servicet
Order by (Select AccountCode from Clients where Clients.AccountNumber = fo.accountnumber)