Calculation with different months by variable - SSIS

Question

I need to do a calculation where I get the sum value from column "B" Having column C = "Y" and divide it by the sum value from column "A" having column C = "X", being 1 month difference from the values.

Example:

Date            A                B            C
2015-02-31     796.92           885.47        X
2015-02-31     932.2900         932.29        Y
2015-02-31     803.29           1147.56       X
2015-02-31     839.1800         839.18        Y
2015-02-31     139.20           143.50        Y
2015-02-31     299.64           308.91        X
2015-02-31     125.47           129.35        X
2015-02-31      117.98         393.27         Y
2015-02-31     0.00             747.68        X
2015-01-31     735.29           758.03        Y
2015-01-31     0.00             683.24        Y
2015-01-31     0.00             734.25        X
2015-01-31     323.64           333.65        Y
2015-01-31     442.36           456.04        X
2015-03-31    52.84             54.47         X
2015-03-31     0.00             549.96        Y
2015-03-31     462.24           476.54        X

So I need to use the values in B(C=Y) divided by the values of A(C=X) having one month earlier...

                  A                B             C
2015-02-31     442.36           456.04           X
2015-03-31     52.84             54.47           Y
2015-03-28     75.90             17.19           Y

In this case above it would be (54.47 + 17.19) / 442.36.

So, I need to do that with a table in SQL to present the results in a dashboard.

Can someone help me with that?

Thanks!

Answer 1

If I understand your question correctly then this should work. This also assumes no gaps between months as in your sample data. If there could be gaps then you will have to overlay a calendar table on top of the result set.

DECLARE @T TABLE(Date DATETIME,A DECIMAL(18,4),B  DECIMAL(18,4),C NVARCHAR(10))

INSERT @T (Date,A,B,C) VALUES 
('02/15/2015',5,5,'X'),('02/15/2015',5,5,'Y'),('02/15/2015',5,5,'X'),('02/15/2015',5,5,'Y'),('02/15/2015',5,5,'Y'),
('02/15/2015',5,5,'X'),('02/15/2015',5,5,'X'),('02/15/2015',5,5,'Y'),('02/15/2015',5,5,'X'),('01/15/2015',5,5,'Y'),
('01/15/2015',5,5,'Y'),('01/15/2015',5,5,'X'),('01/15/2015',5,5,'Y'),('01/15/2015',5,5,'X'),('03/15/2015',5,5,'X'),
('03/15/2015',5,5,'Y'),('03/15/2015',5,5,'X')

;WITH Detail AS
(
    SELECT 
        A=CASE WHEN C='X' THEN A ELSE NULL END,
        B=CASE WHEN C='Y' THEN B ELSE NULL END,
        C,
        Month = DATEPART(MONTH,Date),
        Year = DATEPART(YEAR,Date)
    FROM @T
)
,GroupedByMonth AS
(
    SELECT
        RowNumber = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY Year,Month),
        SumA = SUM(A),
        SumB = SUM(B),
        Month,
        Year 
    FROM 
        Detail
    GROUP BY
        Year,Month
)

SELECT
    This.Year,
    This.Month,
    ThisMonthSumB = This.SumB,
    NextMonthSumA = Next.SumA,
    Result = CASE WHEN Next.SumA = 0 THEN 0 ELSE This.SumB / Next.SumA END
FROM 
    GroupedByMonth This
    LEFT OUTER JOIN GroupedByMonth Next ON Next.RowNumber = This.RowNUmber+1

Or using the Prior Month's SUM(A)

SELECT
    This.Year,
    This.Month,
    ThisMonthSumB = This.SumB,
    PriorMonthSumA = Prior.SumA,
    Result = CASE WHEN Prior.SumA = 0 THEN 0 ELSE This.SumB / Prior.SumA END
FROM 
    GroupedByMonth This
    LEFT OUTER JOIN GroupedByMonth Prior ON Prior.RowNumber = This.RowNUmber-1

Answer 2

Use CASE Expressions or Subqueries and do math on them.

Psuedo example:

SELECT 
  SUM(CASE WHEN Condition for B is true THEN B ELSE 0 END)/SUM(CASE WHEN Condition for A is true THEN A ELSE 0 END)
FROM ...

or

SELECT
  (Subquery that gets the Bs) / (Subquery that gets the As)