CODEWITHSUNDEEP
sqlsql-server
Andenus
Andenus

Reputation: 13

Get SQL Server aggregate function to display in various columns with a condition

My problem is as follows:

I'm working on an integration between two databases and I need to move data from one database to the other (both are SQL Server). My issue is that on one db I has 4 columns (annualAmountCourrentYear, annualAmountCourrentYear-1, annualAmountCourrentYear-2, annualAmountCourrentYear-3) and on the other I don't have these values on a specific column but I can calculate them with the following query:

SELECT SUM(Amount)
FROM Disbursements 
WHERE Year = YEAR(GETDATE()) AND EntityId = 2

I could get all the amounts I need by doing:

SELECT 
    Entity.EntityId, annualAmountCY.amount,
    annualAmountCY1.amount, annualAmountCY2.amount, 
    annualAmountCY3.amount
FROM 
    Entity
INNER JOIN
    (SELECT 
         EntityId, SUM(Amount) AS amount 
     FROM Disbursements 
     WHERE Year = YEAR(GETDATE()) 
     GROUP BY EntityId) AS annualAmountCY ON annualAmountCY.EntityId = fe.EntityId
INNER JOIN
    (SELECT 
         EntityId, SUM(Amount) AS amount 
     FROM Disbursements 
     WHERE Year = YEAR(GETDATE())-1 
     GROUP BY EntityId) AS annualAmountCY1 ON annualAmountCY.EntityId = fe.EntityId
INNER JOIN
    (SELECT 
         EntityId, SUM(Amount) AS amount 
     FROM Disbursements 
     WHERE Year = YEAR(GETDATE())-2
     GROUP BY EntityId) AS annualAmountCY2 ON annualAmountCY.EntityId = fe.EntityId
INNER JOIN
    (SELECT 
         EntityId, SUM(Amount) AS amount 
     FROM Disbursements 
     WHERE Year = YEAR(GETDATE())-3
     GROUP BY EntityId) AS annualAmountCY3 ON annualAmountCY.EntityId =  fe.EntityId

This works but it takes an awful amount of time. I also tried using PIVOT and it works great but the problem with this is that I can't use it dynamically. Does anyone know how I can achieve this?

EDIT:

This would be the solution with PIVOT

SELECT 
    amounts.EntityId, cyear, year1, year2, year3, 
FROM 
    Entity
INNER JOIN
    (SELECT 
         EntityId, 
         [2015] AS cyear, [2014] AS year1, [2013] AS year2, [2012] AS year3
     FROM
         (SELECT 
              EntityId, Year, Amount 
          FROM Disbursements) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Year IN ([2015], [2014], [2013], [2012])
) AS PivotTable) AS amounts ON amounts.EntityId = Entity.EntityId;

On this solution I can't change [2015] for YEAR(GETDATE()), I have to set all years manually

EDIT 2:
Thanks to @Hogan I found a better solution to my problem:

WITH dis AS
(
    SELECT EntityID, Year, SUM(Amount) AS ammount
    FROM Disbursements
    WHERE YEAR >= YEAR(GETDATE())-3
    GROUP BY EntityID, Year
),
eyear AS
(
    SELECT e.EntityId, Year
    FROM Disbursements
    JOIN Entity AS e
    ON Year >= YEAR(GETDATE())-3
    GROUP BY e.FundraisingEntityId, Year
)
SELECT distinct E.EntityId, d.amount this_year, d1.amount AS last_year, d2.amount AS two_years_back, d3.amount AS three_years_back
FROM eyear E
LEFT JOIN dis d ON  E.EntityId =  d.EntityId AND  d.Year = YEAR(GETDATE())
LEFT JOIN dis d1 ON E.EntityId = d1.EntityId AND d1.Year = YEAR(GETDATE())-1
LEFT JOIN dis d2 ON E.EntityId = d2.EntityId AND d2.Year = YEAR(GETDATE())-2
LEFT JOIN dis d3 ON E.EntityId = d3.EntityId AND d3.Year = YEAR(GETDATE())-3

Upvotes: 0

Views: 61

Answers (2)

Hogan
Hogan

Reputation: 70523

WITH years AS 
(
    SELECT YEAR(GETDATE()) AS Y
      UNION ALL
    SELECT YEAR(GETDATE())-1 AS Y
      UNION ALL
    SELECT YEAR(GETDATE())-2 AS Y
      UNION ALL
    SELECT YEAR(GETDATE())-3 AS Y
), year_entity as
(
   SELECT years.y, e.EntityID
   FROM years
   CROSS JOIN (SELECT DISINCT EntityID FROM Disbursements) e
), dis AS
(
  SELECT EntityID, Year, SUM(Amount) as ammount
  FROM Disbursements
  WHERE YEAR >= YEAR(GETDATE())-3
  GROUP BY EntityID, Year
)
SELECT  ye.EntityId, d.amount this_year, d1.amount as last_year, d2.amount as two_years_back, d3.amount as three_years_back
FROM year_entity ye
LEFT JOIN dis d ON  ye.EntityID =  d.EntityId and  d.Year = YEAR(GETDATE()) and ye.y = d.Year
LEFT JOIN dis d1 ON ye.EntityID = d1.EntityId and d1.Year = YEAR(GETDATE())-1 and ye.y = d1.Year
LEFT JOIN dis d2 ON ye.EntityID = d2.EntityId and d2.Year = YEAR(GETDATE())-2 and ye.y = d2.Year
LEFT JOIN dis d3 ON ye.EntityID = d3.EntityId and d3.Year = YEAR(GETDATE())-3 and ye.y = d3.Year

This will be faster than a function

WITH dis AS
(
  SELECT EntityID, Year, SUM(Amount) as ammount
  FROM Disbursements
  WHERE YEAR >= YEAR(GETDATE())-3
  GROUP BY EntityID, Year
)
SELECT  E.EntityId, d.amount this_year, d1.amount as last_year, d2.amount as two_years_back, d3.amount as three_years_back
FROM Entity E
JOIN dis d ON  E.EntityID =  d.EntityId and  d.Year = YEAR(GETDATE())
JOIN dis d1 ON E.EntityID = d1.EntityId and d1.Year = YEAR(GETDATE())-1
JOIN dis d2 ON E.EntityID = d2.EntityId and d2.Year = YEAR(GETDATE())-2
JOIN dis d3 ON E.EntityID = d3.EntityId and d3.Year = YEAR(GETDATE())-3

Upvotes: 0

Andenus
Andenus

Reputation: 13

I solved this issue by creating the following function:

FUNCTION getAmountByFundraisingEntityAndYear (@Id INT, @Year int)
RETURNS INT
AS
BEGIN
    RETURN (SELECT SUM(Amount) AS amount 
            FROM Disbursements 
            WHERE Year = @Year and FundraisingEntityId = @Id
            GROUP BY EntityId)
END

Then I just call the function like this:

SELECT EntityId, (SELECT getAmountByFundraisingEntityAndYear( EntityId, YEAR(GETDATE()))), 
(SELECT getAmountByFundraisingEntityAndYear( EntityId, YEAR(GETDATE())-1)), 
(SELECT getAmountByFundraisingEntityAndYear( EntityId, YEAR(GETDATE())-2)), 
(SELECT getAmountByFundraisingEntityAndYear( EntityId, YEAR(GETDATE())-3))
FROM Entity

Upvotes: 1

Related Questions