Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 question on 'lag' funtion

Author  Topic 

jcarrallo
Starting Member

35 Posts

Posted - 2014-10-20 : 16:56:57
Hi,
The select statement below gives me the accumulated sales over a period for a certain code.

Now, I would like to have an additional column where I calculate:

(previous date sales + 1)*(current sales + 1)-1

I thought of using the function 'lag' but I just don´t know how
Can anyone help, please?
Many thanks!!!!
____________________________


DECLARE @sales TABLE
(
custom VARCHAR(10) NOT NULL,
fecha DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL,
profits NUMERIC(10, 2) NOT NULL
);

INSERT INTO @sales(Custom, Fecha, sales, profits)
VALUES ('q', '20140708', 0.51,21),
('q', '20140712', 0.3,33),
('q', '20140710', 0.5,12),
('q', '20140711', 0.6,43),
('q', '20140712', 0.2,66),
('q', '20140713', 0.7,21),
('q', '20140714', 0.24,76),
('q', '20140714', 0.24,12),
('x', '20140709', 0.25,0),
('x', '20140710', 0.16,0),
('x', '20140711', 0.66,31),
('x', '20140712', 0.23,12),
('x', '20140712', 0.35,11),
('x', '20140714', 0.57,1),
('c', '20140712', 0.97,2),
('c', '20140714', 0.71,3);

SELECT custom, convert(varchar, fecha, 104) AS SPH_DATE_FORMATO, cast(SUM(sales) OVER (ORDER BY fecha) as numeric (18,2)) AS SPH_CLOSE
FROM @sales
where fecha >DATEADD(month, -21, GETDATE()) and custom='q'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-20 : 17:35:00
I don't think you can calculate running products using any of the windowing functions. One way to calculate the running product would be this:
exp(sum(log(1.0+sales)) over (order by fecha))-1,
But as you can imagine, it has its drawbacks (e.g. if the sales is less than or equal to -1 on any row, it will blow up.

You can also use a recursive CTE (which does not require windowing functions).

Also, your order by clause may need to be improved. It is not unambiguous for the where clause you have (e.g., 20140712)
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-10-20 : 21:53:08
Errr...plz try

SELECT
custom,
convert(varchar, fecha, 104) AS SPH_DATE_FORMATO,
cast(SUM(sales) OVER (ORDER BY fecha) as numeric (18,2)) AS SPH_CLOSE,
sales,
lag(sales, 1, 0) over (order by fecha) as LAG,
(lag(sales, 1, 0) over (order by fecha) + 1) * (sales + 1) - 1 as Your_Formula
FROM @sales
where fecha >DATEADD(month, -21, GETDATE()) and custom='q'
Go to Top of Page

jcarrallo
Starting Member

35 Posts

Posted - 2014-10-21 : 00:44:35
That is excellent.
Many thanks!
Go to Top of Page
   

- Advertisement -