Posted by kalikoi on 11/03/05 14:55

Hi jens

I got a problem by using u r query

i generated a temp table using the following query as


select companyid,latestclosingprice,dailydate into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc

if i took records for 2 companies these r the results

3 2.0500 2005-01-05 00:00:00.000
3 2.1800 2005-01-04 00:00:00.000
3 2.2600 2005-01-03 00:00:00.000
3 2.1500 2004-12-31 00:00:00.000
3 2.1400 2004-12-30 00:00:00.000
3 2.1400 2004-12-29 00:00:00.000
3 2.1800 2004-12-28 00:00:00.000
3 2.1900 2004-12-27 00:00:00.000
3 2.1700 2004-12-23 00:00:00.000
3 2.0900 2004-12-22 00:00:00.000
3 2.0700 2004-12-21 00:00:00.000
3 2.0700 2004-12-20 00:00:00.000
3 2.1000 2004-12-17 00:00:00.000
3 2.3400 2004-12-16 00:00:00.000
3 1.7300 2004-12-15 00:00:00.000
3 1.7300 2004-12-14 00:00:00.000
3 1.8200 2004-12-13 00:00:00.000
3 1.7800 2004-12-10 00:00:00.000
3 1.7000 2004-12-09 00:00:00.000
3 1.6300 2004-12-08 00:00:00.000
3 1.6500 2004-12-07 00:00:00.000
3 1.6700 2004-12-06 00:00:00.000
3 1.7000 2004-12-03 00:00:00.000

16 112.1800 2005-01-05 00:00:00.000
16 112.8900 2005-01-04 00:00:00.000
16 114.5500 2005-01-03 00:00:00.000
16 115.1200 2004-12-31 00:00:00.000
16 115.5000 2004-12-30 00:00:00.000
16 115.0600 2004-12-29 00:00:00.000
16 114.8000 2004-12-28 00:00:00.000
16 114.7100 2004-12-27 00:00:00.000
16 114.2200 2004-12-23 00:00:00.000
16 113.1400 2004-12-22 00:00:00.000
16 111.6700 2004-12-21 00:00:00.000
16 112.3900 2004-12-20 00:00:00.000
16 109.4700 2004-12-17 00:00:00.000
16 108.4900 2004-12-16 00:00:00.000
16 108.9600 2004-12-15 00:00:00.000
16 108.1900 2004-12-14 00:00:00.000
16 107.0900 2004-12-13 00:00:00.000
16 106.1200 2004-12-10 00:00:00.000
16 106.0200 2004-12-09 00:00:00.000
16 105.4100 2004-12-08 00:00:00.000
16 107.8800 2004-12-07 00:00:00.000
16 106.7700 2004-12-06 00:00:00.000
16 107.6800 2004-12-03 00:00:00.000


so by using u r query

i got the following

3 2.0500 .9756097560975610 2005-01-05 00:00:00.000
3 2.1800 .4587155963302752 2005-01-04 00:00:00.000
3 2.2600 3.5398230088495575 2005-01-03 00:00:00.000
3 2.1500 .9302325581395349 2004-12-31 00:00:00.000
3 2.1400 .4672897196261682 2004-12-30 00:00:00.000
3 2.1400 .4672897196261682 2004-12-29 00:00:00.000
3 2.1800 .4587155963302752 2004-12-28 00:00:00.000
3 2.1900 3.1963470319634703 2004-12-27 00:00:00.000
3 2.1700 .4608294930875576 2004-12-23 00:00:00.000
3 2.0900 .4784688995215311 2004-12-22 00:00:00.000
3 2.0700 .9661835748792271 2004-12-21 00:00:00.000
3 2.0700 .9661835748792271 2004-12-20 00:00:00.000
3 2.1000 1.9047619047619048 2004-12-17 00:00:00.000
3 2.3400 NULL 2004-12-16 00:00:00.000
3 1.7300 2.8901734104046243 2004-12-15 00:00:00.000
3 1.7300 2.8901734104046243 2004-12-14 00:00:00.000
3 1.8200 12.6373626373626374 2004-12-13 00:00:00.000
3 1.7800 2.2471910112359551 2004-12-10 00:00:00.000
3 1.7000 1.7647058823529412 2004-12-09 00:00:00.000
3 1.6300 1.2269938650306748 2004-12-08 00:00:00.000
3 1.6500 1.2121212121212121 2004-12-07 00:00:00.000
3 1.6700 1.7964071856287425 2004-12-06 00:00:00.000

here i should not get a value for col3 as there is no value for the
next date of this id..same for the next id also

3 1.7000 1.7647058823529412 2004-12-03 00:00:00.000



16 112.1800 .1871991442324835 2005-01-05 00:00:00.000
16 112.8900 .2214545132429799 2005-01-04 00:00:00.000
16 114.5500 .1396769969445657 2005-01-03 00:00:00.000
16 115.1200 .3300903405142460 2004-12-31 00:00:00.000
16 115.5000 NULL 2004-12-30 00:00:00.000
16 115.0600 .0521467060664001 2004-12-29 00:00:00.000
16 114.8000 .2264808362369338 2004-12-28 00:00:00.000
16 114.7100 .0784587219945951 2004-12-27 00:00:00.000
16 114.2200 .2889161267728944 2004-12-23 00:00:00.000
16 113.1400 .9545695598373696 2004-12-22 00:00:00.000
16 111.6700 .4567027849914928 2004-12-21 00:00:00.000
16 112.3900 .4448794376723908 2004-12-20 00:00:00.000
16 109.4700 2.0096830181784964 2004-12-17 00:00:00.000
16 108.4900 .4332196515807909 2004-12-16 00:00:00.000
16 108.9600 .4680616740088106 2004-12-15 00:00:00.000
16 108.1900 .2772899528607080 2004-12-14 00:00:00.000
16 107.0900 .5509384629750677 2004-12-13 00:00:00.000
16 106.1200 .6125141349415756 2004-12-10 00:00:00.000
16 106.0200 .0943218260705527 2004-12-09 00:00:00.000
16 105.4100 .5786927236505075 2004-12-08 00:00:00.000
16 107.8800 .2873563218390805 2004-12-07 00:00:00.000
16 106.7700 .2997096562704880 2004-12-06 00:00:00.000
16 107.6800 .1857355126300149 2004-12-03 00:00:00.000

please help

thanks & regards
kalyan

[Back to original message]