Analytic function 'in Where Clause' not possible!

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

Analytic function 'in Where Clause' not possible!

Author

Topic

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-02-07 : 08:33:12

Hello all!

I have the following code which works very well except the Where Clause. I left some of the malfunctioning whereClause so that you can see what I am looking for: Wherever I get a value for [dateright] I want the value for [pcnextday] in a column. If there is no value for dateright then i dont want a row.
The following is the data returned by the Statement without the whereClause:


dateleft  closeleft   pcleft pcright dateright closeright pcnextday
2000-01-03	6750.76	-3,03	NULL	NULL	   NULL 	-2,37
2000-01-04	6586.95	-2,37	NULL	NULL	   NULL 	-1,27
2000-01-05	6502.07	-1,27	NULL	NULL	   NULL 	-0,4
2000-01-06	6474.92	-0,4	NULL	NULL	   NULL 	 4,48
2000-01-07	6780.96	4,48	4,48	2000-01-07 6780.96	 2,06
2000-01-10	6925.52	2,06	2,06	2000-01-10 6925.52	-0,5
2000-01-11	6891.25	-0,5	NULL	NULL	   NULL 	 0,49
2000-01-12	6912.81	0,49	NULL	NULL	   NULL 	 0,61
2000-01-13	6955.98	0,61	NULL	NULL	   NULL 	 3,05
2000-01-14	7173.22	3,05	3,05	2000-01-14 7173.22	 1,13
2000-01-17	7258.90	1,13	NULL	NULL	   NULL 	-2,61
2000-01-18	7072.12	-2,61	NULL	NULL	   NULL         0,36
2000-01-19	7091.04	0,36	NULL	NULL	   NULL         0,23

Here I add the selectstatement:


with cte as
(
select l.[date] as dateleft,l.[close] as closeleft,l.percentagechange as pcleft,
r.[date] as dateright, r.[close] as closeright, r.percentagechange as pcright from dax2 l left join
(select [date],[close], percentagechange from dax2 where percentagechange >2) r
on l.[date] = r.[date])
select dateleft, closeleft,pcleft,pcright, dateright,closeright, lead(pcleft) over(order by [dateleft]) as pcnextday
from cte
where dateleft = dateright

The where Clause does not help me at all. The value returned from pcnextday is not accurate.

Which is the correct way to get my needed data?

Thank you very much
landau

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-07 : 11:44:02

I'm just guessing, but maybe change the join to an INNER JOIN or change the WHERE clause to WHERE dateright IS NOT NULL?

If that doesn't work, based on your sample data (note it's better if you can put it in a consumable format); what do you want for output?

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-02-08 : 14:08:58

Hi everyone!

I found a solution to my problem but still I dont really understand why it is not working the way I expected it to do. Well, first the solution:


  
with cte as
(
select l.[date] as dateleft,l.[close] as closeleft,l.percentagechange as pcleft,lead(l.percentagechange) over (order by l.[date]) as pcnextday,
r.[date] as dateright, r.[close] as closeright, r.percentagechange as pcright from dax2 l left join
(select [date],[close], percentagechange from dax2 where percentagechange >1.3) r
on l.[date] = r.[date])
select  dateleft, closeleft,pcleft,pcright, dateright,closeright, pcnextday
from cte
where dateright is not null

To solve my problem I had to put the analytic function into the CTE-part of my code. It does not make sense to me - maybe someone can explain to me why it worked not well the following way:


with cte as
(
select l.[date] as dateleft,l.[close] as closeleft,l.percentagechange as pcleft,
r.[date] as dateright, r.[close] as closeright, r.percentagechange as pcright from dax2 l left join
(select [date],[close], percentagechange from dax2 where percentagechange >2) r
on l.[date] = r.[date])
select dateleft, closeleft,pcleft,pcright, dateright,closeright, lead(pcleft) over(order by [dateleft]) as pcnextday
from cte
where dateright is not null

Thankx to the forum!
Landau

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-10 : 08:50:06

reason is your filter (where clause). In first case you're calculating the lead after applying the filter. so what would happen is it will apply lead on the filtered list which would cause it to return unexpected results as the actual row from where you need to take value may get filtered due to the where condition.
In second case you are applying lead on master data and then applying filter on it. So it will do calculation correctly first on the whole data and then when you apply filter you'll get only required rows with correctly calculated values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources