I have a data in the form of columns employid, date and amount and I would like to generate the ytdamount for each record which is the sum of amounts in previous dates in that year. How would my sql query look to achive the following result.
coalesce(sum(YourTable2.amount), 0) as ytdamount
left outer join YourTable as YourTable2
on YourTable.employid = YourTable2.employid
and YourTable.date > YourTable2.Date
group by YourTable.employeeid,
If you are dealing with a huge amount of data, though, this is one of the rare instances where it can be faster to use a cursor. And in SQL Server 2005 and up, you should probably use a Common Table Expression instead.
If it's not practically useful, then it's practically useless.