question on 'lag' funtion - SQL Server Forums

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)

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'

jcarrallo
Starting Member

35 Posts

Posted - 2014-10-21 : 00:44:35

That is excellent.
Many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources