Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: SQL for YTD Calculations

    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.

    employid date amount ytdamount
    racbsl 2010-01-13 09:30:30 20 20
    racbsl 2010-01-13 12:30:30 30 50
    racbsl 2010-02-18 15:30:30 60 110
    racbsl 2010-03-23 11:30:30 10 120
    racbsl 2010-03-23 16:30:30 40 160
    racbsl 2010-04-10 10:30:30 90 250
    Last edited by racbsl; 10-19-10 at 15:04. Reason: columns were not readable

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is one simple method:
    Code:
    select	YourTable.employeeid,
    	YourTable.date,
    	YourTable.amount,
    	coalesce(sum(YourTable2.amount), 0) as ytdamount
    from	YourTable
    	left outer join YourTable as YourTable2
    		on YourTable.employid = YourTable2.employid
    		and YourTable.date > YourTable2.Date
    group by YourTable.employeeid,
    	YourTable.date,
    	YourTable.amount
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2010
    Posts
    2

    Smile Thanks a lot.

    Thanks a lot. This was helpful.
    I tried with the following link and it worked for me.

    QTD and YTD in TSQL

    Thanks a lot

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •