[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Year to date sum in SQL

I have the following table (Named PortfolioIntervals).

PortfolioID     IntervalBeginDate              GrossReturnAmount
1                   06/31/2006 12:00:00         23.42
1                   01/02/2007 12:00:00         3.42
1                   02/24/2007 12:00:00         2.32
2                   05/12/2007 12:00:00         9.43
2                   06/17/2007 12:00:00         2.63

I need a query that will sum all GrossReturnAmount where IntervalBeginDate is a year-to-date value (IE for portfolioID 1, only 1/02/2007 and 2/24/2007 will be included for the current year to date).    Also, if possible, if IntervalBeginDate is 12/31/2006, that would be included in YTD.

Thanks
0
davery10
Asked:
davery10
2 Solutions
 
SQL_SERVER_DBACommented:
try this...

SELECT PortfolioID, YEAR(IntervalBeginDate), SUM(GrossReturnAmount) YTD
FROM PortfolioIntervals
WHERE
      YEAR(IntervalBeginDate)=YEAR(GETDATE())
      AND CONVERT(VARCHAR, IntervalBeginDate, 101) = CONVERT(VARCHAR, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)-1, 101)
GROUP BY
      PortfolioID, YEAR(IntervalBeginDate)
0
 
Jinesh KamdarCommented:
Replace the PL/SQL functions TRUNC() & SYSDATE with their SQL-Server counterparts.

SELECT PortfolioID, SUM(GrossReturnAmount)
FROM PortfolioIntervals
WHERE TRUNC(IntervalBeginDate) BETWEEN TRUNC(SYSDATE,'YYYY') - 1 AND TRUNC(SYSDATE)
GROUP BY PortfolioID
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now