• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

SQL Query

I can't figure out a SQL query that I'm sure will be very simple

I have a Company Table and a Financial Year Table

Company Table contains Company Name and ID

The Financial Year table contains  a company ID column and a Financial year column and a finance column for each company for that year

I want to display columns for

Company Name | Finance Figure (This Year) | Finance Figure (Last Year)

1 Solution
Scott PletcherSenior DBACommented:

SELECT ct.[Company Name], fy.[Financial Year],
    SUM(CASE WHEN [Financial Year] = YEAR(GETDATE) THEN fy.col1 + fy.col2 + ... ELSE 0 END) AS [Finance Total, Curr Year],
    SUM(CASE WHEN [Financial Year] = YEAR(GETDATE) - 1 THEN fy.col1 + fy.col2 + ... ELSE 0 END) AS [Finance Total, Prior Year]
FROM [Company Table] ct
INNER JOIN [Financial Year] fy ON fy.[Company ID] = ct.[Company ID]
WHERE fy.[Financial Year] IN (YEAR(GETDATE()) - 1, YEAR(GETDATE()))
GROUP BY ct.[Company Name], fy.[Financial Year]
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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