CODEWITHSUNDEEP
sqlsql-servert-sqlssis
Bramos
Bramos

Reputation: 53

Calculation by month difference - SSIS

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

Example:

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

So I need to use the values in B divided by the values of A having one month earlier...

                  A                B
2015-02-31     442.36           456.04        
2015-03-31    52.84             54.47

In this case above would be 54.47 / 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!

Upvotes: 2

Views: 85

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want lag() function :

select date, a, b,
       b / coalesce(lag(a) over (order by date), 0) as Result
from(select date, sum(A) as a, sum(B) as b
     from table 
     group by date
    ) t;

However, this assumes the date column has no any day in single month as in o/p stated if so, then use datepart() or month() function and adjust the subquery accordingly.

Upvotes: 1

skp
skp

Reputation: 324

can you try with the Window functions in the sql server https://www.sqlshack.com/use-window-functions-sql-server/

Upvotes: 0

Related Questions