Result issues after last change

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

SQL Server 2008 Forums

Transact-SQL (2008)

Result issues after last change

Author

Topic

JPAucamp
Starting Member

49 Posts

Posted - 2011-08-29 : 21:49:43

Dear forum members

I am very new to sql meaning about 4 weeks of experience. I am using SQL 2008 R2 on a Windows vista machine and have it installed locally.

I used to do most of what I do in excel and vba but due to the time some of the calculations and code took to run I was advised to put my data into a database and most people advised me to use sql.

Unfortunately I am finding it extremely hard to think in terms of a database strcuture as opposed to a spreadsheet and this is causing me to struggle converting a specific project I was working on into a sql database with associated queries.

I was hoping for some assistance with my query design.

My data consists of:
1. PriceRowNo
2. PriceDate
3. Price

What I want to do is calculate the following things:
1. The % move of today vs prior day
2. The direction of the move being Up,Down or No movement
3. The count of the direction (DirectionCounter). This needs to count the number of times the price have moved in the same direction for the series until it changes direction again. For example two down days followed by three up days in a row and then one down day should show:
DOWN1
DOWN2
UP1
UP2
UP3 and then
DOWN 1
4. The total % move of the series (MoveOfSeries) - for example this should show the price change from the DOWN2 price to the UP3 price.
I know that historical calculations can be done for this but not for the latest price as we won't know what the price movement the next day is going to be so I would like to show the value of the current series as if the move for the series ended.

Please find below my existing code which someone was kind enough to help me write. It creates the table and also tries to calculate some of the columns as per the above. Part of it like MoveForDay and Direction is getting the correct results but DirectionCounter is not working. For example on line 21 of the result set it shows 4 but it should be 3. I have absolutely no idea why it's not working. With regards to the MoveOfSeries column I have no idea how to even begin to calculate this other than using a cursor which I have been warned against. I have also showed the desired results below the code but could only do it in a csv format.

ANY assistance will be highly appreciated!!

CREATE TABLE PriceData (
PriceRowNo INT NOT NULL
CONSTRAINT XPKPriceData
PRIMARY KEY CLUSTERED (PriceRowNo)
, PriceDate DATETIME NOT NULL
, Price MONEY NOT NULL
)
GO

CREATE INDEX XIE02PriceData ON PriceData (PriceDate)
GO

INSERT INTO PriceData
VALUES ( 1,'2010-10-28',16.66)
, ( 2,'2010-10-29',16.85)
, ( 3,'2010-11-01',16.93)
, ( 4,'2010-11-02',16.98)
, ( 5,'2010-11-03',17.08)
, ( 6,'2010-11-04',17.03)
, ( 7,'2010-11-05',17.09)
, ( 8,'2010-11-08',16.76)
, ( 9,'2010-11-09',16.67)
, ( 10,'2010-11-10',16.72)
, ( 11,'2010-11-11',16.86)
, ( 12,'2010-11-12',16.85)
, ( 13,'2010-11-15',16.87)
, ( 14,'2010-11-16',16.9)
, ( 15,'2010-11-17',16.92)
, ( 16,'2010-11-18',16.86)
, ( 17,'2010-11-19',16.74)
, ( 18,'2010-11-22',16.73)
, ( 19,'2010-11-23',16.82)
, ( 20,'2010-11-24',17.02)
, ( 21,'2010-11-26',17.05)
, ( 22,'2010-11-29',17.05)
, ( 23,'2010-11-30',17.23)
, ( 24,'2010-12-01',17.29)
, ( 25,'2010-12-02',17.32)
, ( 26,'2010-12-03',17.23)
, ( 27,'2010-12-06',17.21)
, ( 28,'2010-12-07',17.28)
, ( 29,'2010-12-08',17.24)
, ( 30,'2010-12-09',17.06)
, ( 31,'2010-12-10',17.06)
, ( 32,'2010-12-13',16.99)
, ( 33,'2010-12-14',17.15)
, ( 34,'2010-12-15',17.2)
, ( 35,'2010-12-16',17.17)
, ( 36,'2010-12-17',17.21)
, ( 37,'2010-12-20',17.28)
, ( 38,'2010-12-21',17.28)
, ( 39,'2010-12-22',17.22)
, ( 40,'2010-12-23',17.24)
, ( 41,'2010-12-27',17.23)
, ( 42,'2010-12-28',17.29)
, ( 43,'2010-12-29',17.32)
, ( 44,'2010-12-30',17.2)
, ( 45,'2010-12-31',17.19)
, ( 46,'2011-01-03',17.07)
, ( 47,'2011-01-04',17.09)
, ( 48,'2011-01-05',17.12)
, ( 49,'2011-01-06',17.25)
, ( 50,'2011-01-07',17.45)
, ( 51,'2011-01-10',17.49)
, ( 52,'2011-01-11',17.45)
, ( 53,'2011-01-12',17.44)
, ( 54,'2011-01-13',17.45)
, ( 55,'2011-01-14',17.55)
, ( 56,'2011-01-18',17.56)
, ( 57,'2011-01-19',17.56)
, ( 58,'2011-01-20',17.46)
, ( 59,'2011-01-21',17.53)
, ( 60,'2011-01-24',17.44)
, ( 61,'2011-01-25',17.3)
, ( 62,'2011-01-26',17.29)
, ( 63,'2011-01-27',17.53)
, ( 64,'2011-01-28',17.55)
, ( 65,'2011-01-31',17.63)
, ( 66,'2011-02-01',17.78)
, ( 67,'2011-02-02',17.85)
, ( 68,'2011-02-03',17.75)
, ( 69,'2011-02-04',17.94)
, ( 70,'2011-02-07',17.98)
, ( 71,'2011-02-08',17.96)
, ( 72,'2011-02-09',17.88)
, ( 73,'2011-02-10',18.03)
, ( 74,'2011-02-11',18.05)
, ( 75,'2011-02-14',17.93)
, ( 76,'2011-02-15',17.96)
, ( 77,'2011-02-16',17.83)
, ( 78,'2011-02-17',17.83)
, ( 79,'2011-02-18',17.76)
, ( 80,'2011-02-22',17.86)
, ( 81,'2011-02-23',17.96)
, ( 82,'2011-02-24',18.22)
, ( 83,'2011-02-25',18.11)
, ( 84,'2011-02-28',18.27)
, ( 85,'2011-03-01',18.12)
, ( 86,'2011-03-02',18.22)
, ( 87,'2011-03-03',18.27)
, ( 88,'2011-03-04',18.27)
, ( 89,'2011-03-07',18.29)
, ( 90,'2011-03-08',18.29)
, ( 91,'2011-03-09',18.18)
, ( 92,'2011-03-10',18.26)
, ( 93,'2011-03-11',18.44)
, ( 94,'2011-03-14',18.52)
, ( 95,'2011-03-15',18.56)
, ( 96,'2011-03-16',18.68)
, ( 97,'2011-03-17',18.6)
, ( 98,'2011-03-18',18.71)
, ( 99,'2011-03-21',18.69)
, (100,'2011-03-22',18.69)
, (101,'2011-03-23',18.67)
, (102,'2011-03-24',18.72)
, (103,'2011-03-25',18.78)
, (104,'2011-03-28',18.77)
, (105,'2011-03-29',18.79)
, (106,'2011-03-30',18.6)
, (107,'2011-03-31',18.88)
, (108,'2011-04-01',18.93)
, (109,'2011-04-04',19.14)
, (110,'2011-04-05',19.26)
, (111,'2011-04-06',19.4)
, (112,'2011-04-07',19.25)
, (113,'2011-04-08',18.98)
, (114,'2011-04-11',18.93)
, (115,'2011-04-12',18.97)
, (116,'2011-04-13',18.92)
, (117,'2011-04-14',18.83)
, (118,'2011-04-15',19)
, (119,'2011-04-18',19.16)
, (120,'2011-04-19',19.14)
, (121,'2011-04-20',18.11)
, (122,'2011-04-21',17.91)
, (123,'2011-04-25',18.11)
, (124,'2011-04-26',17.44)
, (125,'2011-04-27',17.69)
, (126,'2011-04-28',17.64)
, (127,'2011-04-29',17.81)
, (128,'2011-05-02',17.91)
, (129,'2011-05-03',17.67)
, (130,'2011-05-04',17.59)
, (131,'2011-05-05',17.32)
, (132,'2011-05-06',16.87)
, (133,'2011-05-09',16.69)
, (134,'2011-05-10',16.87)
, (135,'2011-05-11',16.68)
, (136,'2011-05-12',17.06)
, (137,'2011-05-13',17.36)
, (138,'2011-05-16',17.61)
, (139,'2011-05-17',17.59)
, (140,'2011-05-18',17.48)
, (141,'2011-05-19',17.23)
, (142,'2011-05-20',17.27)
, (143,'2011-05-23',17.5)
, (144,'2011-05-24',17.69)
, (145,'2011-05-25',17.84)
, (146,'2011-05-26',18.02)
, (147,'2011-05-27',17.95)
, (148,'2011-05-31',17.99)
, (149,'2011-06-01',18.14)
, (150,'2011-06-02',18.41)
, (151,'2011-06-03',18.46)
, (152,'2011-06-06',18.61)
, (153,'2011-06-07',18.48)
, (154,'2011-06-08',18.28)
, (155,'2011-06-09',18.29)
, (156,'2011-06-10',18.32)
, (157,'2011-06-13',18.34)
, (158,'2011-06-14',18.54)
, (159,'2011-06-15',18.68)
, (160,'2011-06-16',18.7)
, (161,'2011-06-17',18.68)
, (162,'2011-06-20',18.82)
, (163,'2011-06-21',18.79)
, (164,'2011-06-22',18.54)
, (165,'2011-06-23',18.53)
, (166,'2011-06-24',18.54)
, (167,'2011-06-27',18.43)
, (168,'2011-06-28',18.42)
, (169,'2011-06-29',18.55)
, (170,'2011-06-30',18.68)
, (171,'2011-07-01',18.54)
, (172,'2011-07-05',18.59)
, (173,'2011-07-06',18.75)
, (174,'2011-07-07',18.61)
, (175,'2011-07-08',18.87)
, (176,'2011-07-11',19.09)
, (177,'2011-07-12',19.18)
, (178,'2011-07-13',19.29)
, (179,'2011-07-14',19.37)
, (180,'2011-07-15',19.31)
, (181,'2011-07-18',19.21)
, (182,'2011-07-19',19.37)
, (183,'2011-07-20',19.44)
, (184,'2011-07-21',19.42)
, (185,'2011-07-22',19.14)
, (186,'2011-07-25',19.41)
, (187,'2011-07-26',19.42)
, (188,'2011-07-27',19.45)
, (189,'2011-07-28',19.69)
, (190,'2011-07-29',19.66)
, (191,'2011-08-01',20)
, (192,'2011-08-02',19.89)
, (193,'2011-08-03',20.12)
, (194,'2011-08-04',20)
, (195,'2011-08-05',19.78)
, (196,'2011-08-08',19.86)
, (197,'2011-08-09',19.85)
, (198,'2011-08-10',19.71)
, (199,'2011-08-11',19.89)
, (200,'2011-08-12',20.01)
GO

SELECT (this.Price - prev.Price) / prev.Price AS MoveForDay
, CASE
--WHEN this.Price = prev.Price THEN 'Same'
WHEN this.Price < prev.Price THEN 'Down'
WHEN this.Price > prev.Price THEN 'Up'
ELSE 'No Movement'
END AS Direction
, DATEDIFF(day, ch.PriceDate, this.PriceDate)
- DATEDIFF(week, ch.PriceDate, this.PriceDate) * 2 AS DirectionCounter
FROM PriceData AS this
LEFT JOIN PriceData AS prev
ON (prev.PriceDate = (SELECT MAX(z1.PriceDate)
FROM PriceData AS z1
WHERE z1.PriceDate < this.PriceDate))
LEFT JOIN PriceData AS ma
ON (ma.PriceDate = (SELECT MAX(z2.PriceDate)
FROM PriceData AS z2
WHERE z2.PriceDate < DateAdd(day, -14, this.PriceDate)))
LEFT JOIN PriceData AS ch
ON (ch.PriceDate = (SELECT Max(z3.PriceDate)
FROM PriceData AS z3
INNER JOIN PriceData AS z4
ON (z4.PriceDate = (SELECT Max(z5.PriceDate)
FROM PriceData AS z5
WHERE z5.PriceDate < z3.PriceDate))
WHERE z3.PriceDate < this.PriceDate
AND ( (this.Price = prev.Price AND NOT (z3.Price = z4.Price))
OR (this.Price < prev.Price AND NOT (z3.Price < z4.Price))
OR (this.Price > prev.Price AND NOT (z3.Price > z4.Price))
)
))

PriceDate,Price,MoveForDay,Direction,Count,Direction&Count,MoveOfSeries
20101028,16.66,0.00%,0,0,0,0
20101029,16.85,1.14%, Up ,1,Up1,0
20101101,16.93,0.47%, Up ,2,Up2,0
20101102,16.98,0.30%, Up ,3,Up3,0
20101103,17.08,0.59%, Up ,4,Up4,2.52%
20101104,17.03,-0.29%, Down ,1,Down1,-0.29%
20101105,17.09,0.35%, Up ,1,Up1,0.35%
20101108,16.76,-1.93%, Down ,1,Down1,0.00%
20101109,16.67,-0.54%, Down ,2,Down2,-2.46%
20101110,16.72,0.30%, Up ,1,Up1,0.00%
20101111,16.86,0.84%, Up ,2,Up2,1.14%
20101112,16.85,-0.06%, Down ,1,Down1,-0.06%
20101115,16.87,0.12%, Up ,1,Up1,0.00%
20101116,16.9,0.18%, Up ,2,Up2,0.00%
20101117,16.92,0.12%, Up ,3,Up3,0.42%
20101118,16.86,-0.35%, Down ,1,Down1,0.00%
20101119,16.74,-0.71%, Down ,2,Down2,0.00%
20101122,16.73,-0.06%, Down ,3,Down3,-1.12%
20101123,16.82,0.54%, Up ,1,Up1,0.00%
20101124,17.02,1.19%, Up ,2,Up2,0.00%
20101126,17.05,0.18%, Up ,3,Up3,1.91%
20101129,17.05,0.00%, No movement ,1,No movement1,0.00%
20101130,17.23,1.06%, Up ,1,Up1,0.00%
20101201,17.29,0.35%, Up ,2,Up2,0.00%
20101202,17.32,0.17%, Up ,3,Up3,1.58%
20101203,17.23,-0.52%, Down ,1,Down1,0.00%
20101206,17.21,-0.12%, Down ,2,Down2,-0.64%
20101207,17.28,0.41%, Up ,1,Up1,0.41%
20101208,17.24,-0.23%, Down ,1,Down1,0.00%
20101209,17.06,-1.04%, Down ,2,Down2,-1.27%
20101210,17.06,0.00%, No movement ,1,No movement1,0.00%
20101213,16.99,-0.41%, Down ,1,Down1,-0.41%
20101214,17.15,0.94%, Up ,1,Up1,0.00%
20101215,17.2,0.29%, Up ,2,Up2,1.24%
20101216,17.17,-0.17%, Down ,1,Down1,-0.17%
20101217,17.21,0.23%, Up ,1,Up1,0.00%
20101220,17.28,0.41%, Up ,2,Up2,0.64%
20101221,17.28,0.00%, No movement ,1,No movement1,0.00%
20101222,17.22,-0.35%, Down ,1,Down1,-0.35%
20101223,17.24,0.12%, Up ,1,Up1,0.12%
20101227,17.23,-0.06%, Down ,1,Down1,-0.06%
20101228,17.29,0.35%, Up ,1,Up1,0.00%
20101229,17.32,0.17%, Up ,2,Up2,0.52%
20101230,17.2,-0.69%, Down ,1,Down1,0.00%
20101231,17.19,-0.06%, Down ,2,Down2,0.00%
20110103,17.07,-0.70%, Down ,3,Down3,-1.44%
20110104,17.09,0.12%, Up ,1,Up1,0.00%
20110105,17.12,0.18%, Up ,2,Up2,0.00%
20110106,17.25,0.76%, Up ,3,Up3,0.00%
20110107,17.45,1.16%, Up ,4,Up4,0.00%
20110110,17.49,0.23%, Up ,5,Up5,2.46%
20110111,17.45,-0.23%, Down ,1,Down1,0.00%
20110112,17.44,-0.06%, Down ,2,Down2,-0.29%
20110113,17.45,0.06%, Up ,1,Up1,0.00%
20110114,17.55,0.57%, Up ,2,Up2,0.00%
20110118,17.56,0.06%, Up ,3,Up3,0.69%
20110119,17.56,0.00%, No movement ,1,No movement1,0.00%
20110120,17.46,-0.57%, Down ,1,Down1,-0.57%
20110121,17.53,0.40%, Up ,1,Up1,0.40%
20110124,17.44,-0.51%, Down ,1,Down1,0.00%
20110125,17.3,-0.80%, Down ,2,Down2,0.00%
20110126,17.29,-0.06%, Down ,3,Down3,-1.37%
20110127,17.53,1.388%, Up ,1,Up1,0.00%
20110128,17.55,0.114%, Up ,2,Up2,0.00%
20110131,17.63,0.456%, Up ,3,Up3,0.00%
20110201,17.78,0.851%, Up ,4,Up4,0.00%
20110202,17.85,0.394%, Up ,5,Up5,3.239%
20110203,17.75,-0.56%, Down ,1,Down1,-0.56%
20110204,17.94,1.07%, Up ,1,Up1,0.00%
20110207,17.98,0.22%, Up ,2,Up2,1.30%
20110208,17.96,-0.11%, Down ,1,Down1,0.00%
20110209,17.88,-0.45%, Down ,2,Down2,-0.56%
20110210,18.03,0.84%, Up ,1,Up1,0.00%
20110211,18.05,0.11%, Up ,2,Up2,0.95%
20110214,17.93,-0.66%, Down ,1,Down1,-0.66%
20110215,17.96,0.17%, Up ,1,Up1,0.17%
20110216,17.83,-0.72%, Down ,1,Down1,-0.72%
20110217,17.83,0.00%, No movement ,1,No movement1,0.00%
20110218,17.76,-0.39%, Down ,1,Down1,-0.39%
20110222,17.86,0.56%, Up ,1,Up1,0.00%
20110223,17.96,0.56%, Up ,2,Up2,0.00%
20110224,18.22,1.45%, Up ,3,Up3,2.59%
20110225,18.11,-0.60%, Down ,1,Down1,-0.60%
20110228,18.27,0.88%, Up ,1,Up1,0.88%
20110301,18.12,-0.82%, Down ,1,Down1,-0.82%
20110302,18.22,0.55%, Up ,1,Up1,0.00%
20110303,18.27,0.27%, Up ,2,Up2,0.83%
20110304,18.27,0.00%, No movement ,1,No movement1,0.00%
20110307,18.29,0.11%, Up ,1,Up1,0.11%
20110308,18.29,0.00%, No movement ,1,No movement1,0.00%
20110309,18.18,-0.60%, Down ,1,Down1,-0.60%
20110310,18.26,0.44%, Up ,1,Up1,0.00%
20110311,18.44,0.99%, Up ,2,Up2,0.00%
20110314,18.52,0.43%, Up ,3,Up3,0.00%
20110315,18.56,0.22%, Up ,4,Up4,0.00%
20110316,18.68,0.65%, Up ,5,Up5,2.75%
20110317,18.6,-0.43%, Down ,1,Down1,-0.43%
20110318,18.71,0.59%, Up ,1,Up1,0.59%
20110321,18.69,-0.11%, Down ,1,Down1,-0.11%
20110322,18.69,0.00%, No movement ,1,No movement1,0.00%
20110323,18.67,-0.11%, Down ,1,Down1,-0.11%
20110324,18.72,0.27%, Up ,1,Up1,0.00%
20110325,18.78,0.32%, Up ,2,Up2,0.59%
20110328,18.77,-0.05%, Down ,1,Down1,-0.05%
20110329,18.79,0.11%, Up ,1,Up1,0.11%
20110330,18.6,-1.01%, Down ,1,Down1,-1.01%
20110331,18.88,1.51%, Up ,1,Up1,0.00%
20110401,18.93,0.26%, Up ,2,Up2,0.00%
20110404,19.14,1.11%, Up ,3,Up3,0.00%
20110405,19.26,0.63%, Up ,4,Up4,0.00%
20110406,19.4,0.73%, Up ,5,Up5,4.30%
20110407,19.25,-0.77%, Down ,1,Down1,0.00%
20110408,18.98,-1.40%, Down ,2,Down2,0.00%
20110411,18.93,-0.26%, Down ,3,Down3,-2.42%
20110412,18.97,0.21%, Up ,1,Up1,0.21%
20110413,18.92,-0.26%, Down ,1,Down1,0.00%
20110414,18.83,-0.48%, Down ,2,Down2,-0.74%
20110415,19,0.90%, Up ,1,Up1,0.00%
20110418,19.16,0.84%, Up ,2,Up2,1.75%
20110419,19.14,-0.10%, Down ,1,Down1,0.00%
20110420,18.11,-5.38%, Down ,2,Down2,0.00%
20110421,17.91,-1.10%, Down ,3,Down3,-6.52%
20110425,18.11,1.12%, Up ,1,Up1,1.12%
20110426,17.44,-3.70%, Down ,1,Down1,-3.70%
20110427,17.69,1.43%, Up ,1,Up1,1.43%
20110428,17.64,-0.28%, Down ,1,Down1,-0.28%
20110429,17.81,0.96%, Up ,1,Up1,0.00%
20110502,17.91,0.56%, Up ,2,Up2,1.53%
20110503,17.67,-1.34%, Down ,1,Down1,0.00%
20110504,17.59,-0.45%, Down ,2,Down2,0.00%
20110505,17.32,-1.53%, Down ,3,Down3,0.00%
20110506,16.87,-2.60%, Down ,4,Down4,0.00%
20110509,16.69,-1.07%, Down ,5,Down5,-6.81%
20110510,16.87,1.08%, Up ,1,Up1,1.08%
20110511,16.68,-1.13%, Down ,1,Down1,-1.13%
20110512,17.06,2.28%, Up ,1,Up1,0.00%
20110513,17.36,1.76%, Up ,2,Up2,0.00%
20110516,17.61,1.44%, Up ,3,Up3,5.58%
20110517,17.59,-0.11%, Down ,1,Down1,0.00%
20110518,17.48,-0.63%, Down ,2,Down2,0.00%
20110519,17.23,-1.43%, Down ,3,Down3,-2.16%
20110520,17.27,0.23%, Up ,1,Up1,0.00%
20110523,17.5,1.33%, Up ,2,Up2,0.00%
20110524,17.69,1.09%, Up ,3,Up3,0.00%
20110525,17.84,0.85%, Up ,4,Up4,0.00%
20110526,18.02,1.01%, Up ,5,Up5,4.59%
20110527,17.95,-0.39%, Down ,1,Down1,-0.39%
20110531,17.99,0.22%, Up ,1,Up1,0.00%
20110601,18.14,0.83%, Up ,2,Up2,0.00%
20110602,18.41,1.49%, Up ,3,Up3,0.00%
20110603,18.46,0.27%, Up ,4,Up4,0.00%
20110606,18.61,0.81%, Up ,5,Up5,3.68%
20110607,18.48,-0.70%, Down ,1,Down1,0.00%
20110608,18.28,-1.08%, Down ,2,Down2,-1.77%
20110609,18.29,0.05%, Up ,1,Up1,0.00%
20110610,18.32,0.16%, Up ,2,Up2,0.00%
20110613,18.34,0.11%, Up ,3,Up3,0.00%
20110614,18.54,1.09%, Up ,4,Up4,0.00%
20110615,18.68,0.76%, Up ,5,Up5,0.00%
20110616,18.7,0.11%, Up ,6,Up6,2.30%
20110617,18.68,-0.11%, Down ,1,Down1,-0.11%
20110620,18.82,0.75%, Up ,1,Up1,0.75%
20110621,18.79,-0.16%, Down ,1,Down1,0.00%
20110622,18.54,-1.33%, Down ,2,Down2,0.00%
20110623,18.53,-0.05%, Down ,3,Down3,-1.54%
20110624,18.54,0.05%, Up ,1,Up1,0.05%
20110627,18.43,-0.59%, Down ,1,Down1,0.00%
20110628,18.42,-0.05%, Down ,2,Down2,-0.65%
20110629,18.55,0.71%, Up ,1,Up1,0.00%
20110630,18.68,0.70%, Up ,2,Up2,1.41%
20110701,18.54,-0.75%, Down ,1,Down1,-0.75%
20110705,18.59,0.27%, Up ,1,Up1,0.00%
20110706,18.75,0.86%, Up ,2,Up2,1.13%
20110707,18.61,-0.75%, Down ,1,Down1,-0.75%
20110708,18.87,1.40%, Up ,1,Up1,0.00%
20110711,19.09,1.17%, Up ,2,Up2,0.00%
20110712,19.18,0.47%, Up ,3,Up3,0.00%
20110713,19.29,0.57%, Up ,4,Up4,0.00%
20110714,19.37,0.41%, Up ,5,Up5,4.08%
20110715,19.31,-0.31%, Down ,1,Down1,0.00%
20110718,19.21,-0.52%, Down ,2,Down2,-0.83%
20110719,19.37,0.83%, Up ,1,Up1,0.00%
20110720,19.44,0.36%, Up ,2,Up2,1.20%
20110721,19.42,-0.10%, Down ,1,Down1,0.00%
20110722,19.14,-1.44%, Down ,2,Down2,-1.54%
20110725,19.41,1.41%, Up ,1,Up1,0.00%
20110726,19.42,0.05%, Up ,2,Up2,0.00%
20110727,19.45,0.15%, Up ,3,Up3,0.00%
20110728,19.69,1.23%, Up ,4,Up4,2.87%
20110729,19.66,-0.15%, Down ,1,Down1,-0.15%
20110801,20,1.73%, Up ,1,Up1,1.73%
20110802,19.89,-0.55%, Down ,1,Down1,-0.55%
20110803,20.12,1.16%, Up ,1,Up1,1.16%
20110804,20,-0.60%, Down ,1,Down1,0.00%
20110805,19.78,-1.10%, Down ,2,Down2,-1.69%
20110808,19.86,0.40%, Up ,1,Up1,0.40%
20110809,19.85,-0.05%, Down ,1,Down1,0.00%
20110810,19.71,-0.71%, Down ,2,Down2,-0.76%
20110811,19.89,0.91%, Up ,1,Up1,0.00%
20110812,20.01,0.60%, Up ,2,Up2,1.52%

JPAucamp
Starting Member

49 Posts

Posted - 2011-08-30 : 07:48:01

Hi there! Do I need to supply more info for this question as I have not had any replies? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 01:24:08

what you're telling is not matching with output shown. it shows 3 only in 21st line so I cant make out what exactly is your problem. can you elaborate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JPAucamp
Starting Member

49 Posts

Posted - 2011-08-31 : 08:42:50

Hi visakh16!

Thanks for getting back to me. I really appreciate it a lot. When I execute the query in sql the results I get on row 21 is
MoveForDay 0.0017
Direction Up
DirectionCounter 4

In row 21 the number of times Up is 3 so it should show me a 3 not a 4.

In the same way on row 41 from the sql output it shows:
MoveForDay -0.0005
Direction Down
DirectionCounter 2

As the direction on row 40 is Up and row 41 is Down the DirectionCounter should be showing a 1 as it is the first day Down since the direction changed from Up to Down.

I hope I have explained this well enough. If I can do anything to make it clearer please let me know.

Thanks again for taking time to help me!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 09:20:19

this should give you a start


;With CTE
AS(
select p.PriceDate,p.Price,(p.Price-COALESCE(p1.Price,0))*100/p1.Price AS [Change%],
CASE WHEN p1.Price IS NULL THEN ''
	 WHEN p.Price-p1.Price > 0  THEN 'UP' 
	 WHEN p.Price-p1.Price = 0 THEN 'No MOVEMENT'
	 ELSE 'DOWN'
END AS Direction
from PriceData p
OUTER APPLY (SELECT TOP 1 Price
             FROM PriceData
             WHERE PriceDate< p.PriceDate
             ORDER BY PriceDate DESC
             )p1

)
SELECT PriceDate,Price,[Change%],Direction,ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) ,
Direction + CAST(ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) as varchar(10)) 
FROM CTE c1
OUTER APPLY (SELECT TOP 1 PriceDate AS PrevDate
             FROM CTE
             WHERE PriceDate<c1.PriceDate
             AND Direction <> c1.Direction
             ORDER BY PriceDate DESC)c2


output
---------------------------------------------------
PriceDate	Price	Change%	Direction	(No column name)	(No column name)
2010-10-28 00:00:00.000	16.66	NULL		1	1
2010-10-29 00:00:00.000	16.85	1.1404	UP	1	UP1
2010-11-01 00:00:00.000	16.93	0.4747	UP	2	UP2
2010-11-02 00:00:00.000	16.98	0.2953	UP	3	UP3
2010-11-03 00:00:00.000	17.08	0.5889	UP	4	UP4
2010-11-04 00:00:00.000	17.03	-0.2927	DOWN	1	DOWN1
2010-11-05 00:00:00.000	17.09	0.3523	UP	1	UP1
2010-11-08 00:00:00.000	16.76	-1.9309	DOWN	1	DOWN1
2010-11-09 00:00:00.000	16.67	-0.5369	DOWN	2	DOWN2
2010-11-10 00:00:00.000	16.72	0.2999	UP	1	UP1
2010-11-11 00:00:00.000	16.86	0.8373	UP	2	UP2
2010-11-12 00:00:00.000	16.85	-0.0593	DOWN	1	DOWN1
2010-11-15 00:00:00.000	16.87	0.1186	UP	1	UP1
2010-11-16 00:00:00.000	16.90	0.1778	UP	2	UP2
2010-11-17 00:00:00.000	16.92	0.1183	UP	3	UP3
2010-11-18 00:00:00.000	16.86	-0.3546	DOWN	1	DOWN1
2010-11-19 00:00:00.000	16.74	-0.7117	DOWN	2	DOWN2
2010-11-22 00:00:00.000	16.73	-0.0597	DOWN	3	DOWN3
2010-11-23 00:00:00.000	16.82	0.5379	UP	1	UP1
2010-11-24 00:00:00.000	17.02	1.189	UP	2	UP2
2010-11-26 00:00:00.000	17.05	0.1762	UP	3	UP3
2010-11-29 00:00:00.000	17.05	0.00	No MOVEMENT	1	No MOVEMENT1
2010-11-30 00:00:00.000	17.23	1.0557	UP	1	UP1
2010-12-01 00:00:00.000	17.29	0.3482	UP	2	UP2
2010-12-02 00:00:00.000	17.32	0.1735	UP	3	UP3
2010-12-03 00:00:00.000	17.23	-0.5196	DOWN	1	DOWN1
2010-12-06 00:00:00.000	17.21	-0.116	DOWN	2	DOWN2
2010-12-07 00:00:00.000	17.28	0.4067	UP	1	UP1
2010-12-08 00:00:00.000	17.24	-0.2314	DOWN	1	DOWN1
2010-12-09 00:00:00.000	17.06	-1.044	DOWN	2	DOWN2
2010-12-10 00:00:00.000	17.06	0.00	No MOVEMENT	1	No MOVEMENT1
2010-12-13 00:00:00.000	16.99	-0.4103	DOWN	1	DOWN1
2010-12-14 00:00:00.000	17.15	0.9417	UP	1	UP1
2010-12-15 00:00:00.000	17.20	0.2915	UP	2	UP2
2010-12-16 00:00:00.000	17.17	-0.1744	DOWN	1	DOWN1
2010-12-17 00:00:00.000	17.21	0.2329	UP	1	UP1
2010-12-20 00:00:00.000	17.28	0.4067	UP	2	UP2
2010-12-21 00:00:00.000	17.28	0.00	No MOVEMENT	1	No MOVEMENT1
2010-12-22 00:00:00.000	17.22	-0.3472	DOWN	1	DOWN1
2010-12-23 00:00:00.000	17.24	0.1161	UP	1	UP1
2010-12-27 00:00:00.000	17.23	-0.058	DOWN	1	DOWN1
2010-12-28 00:00:00.000	17.29	0.3482	UP	1	UP1
2010-12-29 00:00:00.000	17.32	0.1735	UP	2	UP2
2010-12-30 00:00:00.000	17.20	-0.6928	DOWN	1	DOWN1
2010-12-31 00:00:00.000	17.19	-0.0581	DOWN	2	DOWN2
2011-01-03 00:00:00.000	17.07	-0.698	DOWN	3	DOWN3
2011-01-04 00:00:00.000	17.09	0.1171	UP	1	UP1
2011-01-05 00:00:00.000	17.12	0.1755	UP	2	UP2
2011-01-06 00:00:00.000	17.25	0.7593	UP	3	UP3
2011-01-07 00:00:00.000	17.45	1.1594	UP	4	UP4
2011-01-10 00:00:00.000	17.49	0.2292	UP	5	UP5
2011-01-11 00:00:00.000	17.45	-0.2287	DOWN	1	DOWN1
2011-01-12 00:00:00.000	17.44	-0.0573	DOWN	2	DOWN2
2011-01-13 00:00:00.000	17.45	0.0573	UP	1	UP1
2011-01-14 00:00:00.000	17.55	0.573	UP	2	UP2
2011-01-18 00:00:00.000	17.56	0.0569	UP	3	UP3
2011-01-19 00:00:00.000	17.56	0.00	No MOVEMENT	1	No MOVEMENT1
2011-01-20 00:00:00.000	17.46	-0.5694	DOWN	1	DOWN1
2011-01-21 00:00:00.000	17.53	0.4009	UP	1	UP1
2011-01-24 00:00:00.000	17.44	-0.5134	DOWN	1	DOWN1
2011-01-25 00:00:00.000	17.30	-0.8027	DOWN	2	DOWN2
2011-01-26 00:00:00.000	17.29	-0.0578	DOWN	3	DOWN3
2011-01-27 00:00:00.000	17.53	1.388	UP	1	UP1
2011-01-28 00:00:00.000	17.55	0.114	UP	2	UP2
2011-01-31 00:00:00.000	17.63	0.4558	UP	3	UP3
2011-02-01 00:00:00.000	17.78	0.8508	UP	4	UP4
2011-02-02 00:00:00.000	17.85	0.3937	UP	5	UP5
2011-02-03 00:00:00.000	17.75	-0.5602	DOWN	1	DOWN1
2011-02-04 00:00:00.000	17.94	1.0704	UP	1	UP1
2011-02-07 00:00:00.000	17.98	0.2229	UP	2	UP2
2011-02-08 00:00:00.000	17.96	-0.1112	DOWN	1	DOWN1
2011-02-09 00:00:00.000	17.88	-0.4454	DOWN	2	DOWN2
2011-02-10 00:00:00.000	18.03	0.8389	UP	1	UP1
2011-02-11 00:00:00.000	18.05	0.1109	UP	2	UP2
2011-02-14 00:00:00.000	17.93	-0.6648	DOWN	1	DOWN1
2011-02-15 00:00:00.000	17.96	0.1673	UP	1	UP1
2011-02-16 00:00:00.000	17.83	-0.7238	DOWN	1	DOWN1
2011-02-17 00:00:00.000	17.83	0.00	No MOVEMENT	1	No MOVEMENT1
2011-02-18 00:00:00.000	17.76	-0.3925	DOWN	1	DOWN1
2011-02-22 00:00:00.000	17.86	0.563	UP	1	UP1
2011-02-23 00:00:00.000	17.96	0.5599	UP	2	UP2
2011-02-24 00:00:00.000	18.22	1.4476	UP	3	UP3
2011-02-25 00:00:00.000	18.11	-0.6037	DOWN	1	DOWN1
2011-02-28 00:00:00.000	18.27	0.8834	UP	1	UP1
2011-03-01 00:00:00.000	18.12	-0.821	DOWN	1	DOWN1
2011-03-02 00:00:00.000	18.22	0.5518	UP	1	UP1
2011-03-03 00:00:00.000	18.27	0.2744	UP	2	UP2
2011-03-04 00:00:00.000	18.27	0.00	No MOVEMENT	1	No MOVEMENT1
2011-03-07 00:00:00.000	18.29	0.1094	UP	1	UP1
2011-03-08 00:00:00.000	18.29	0.00	No MOVEMENT	1	No MOVEMENT1
2011-03-09 00:00:00.000	18.18	-0.6014	DOWN	1	DOWN1
2011-03-10 00:00:00.000	18.26	0.44	UP	1	UP1
2011-03-11 00:00:00.000	18.44	0.9857	UP	2	UP2
2011-03-14 00:00:00.000	18.52	0.4338	UP	3	UP3
2011-03-15 00:00:00.000	18.56	0.2159	UP	4	UP4
2011-03-16 00:00:00.000	18.68	0.6465	UP	5	UP5
2011-03-17 00:00:00.000	18.60	-0.4282	DOWN	1	DOWN1
2011-03-18 00:00:00.000	18.71	0.5913	UP	1	UP1
2011-03-21 00:00:00.000	18.69	-0.1068	DOWN	1	DOWN1
2011-03-22 00:00:00.000	18.69	0.00	No MOVEMENT	1	No MOVEMENT1
2011-03-23 00:00:00.000	18.67	-0.107	DOWN	1	DOWN1
2011-03-24 00:00:00.000	18.72	0.2678	UP	1	UP1
2011-03-25 00:00:00.000	18.78	0.3205	UP	2	UP2
2011-03-28 00:00:00.000	18.77	-0.0532	DOWN	1	DOWN1
2011-03-29 00:00:00.000	18.79	0.1065	UP	1	UP1
2011-03-30 00:00:00.000	18.60	-1.0111	DOWN	1	DOWN1
2011-03-31 00:00:00.000	18.88	1.5053	UP	1	UP1
2011-04-01 00:00:00.000	18.93	0.2648	UP	2	UP2
2011-04-04 00:00:00.000	19.14	1.1093	UP	3	UP3
2011-04-05 00:00:00.000	19.26	0.6269	UP	4	UP4
2011-04-06 00:00:00.000	19.40	0.7268	UP	5	UP5
2011-04-07 00:00:00.000	19.25	-0.7731	DOWN	1	DOWN1
2011-04-08 00:00:00.000	18.98	-1.4025	DOWN	2	DOWN2
2011-04-11 00:00:00.000	18.93	-0.2634	DOWN	3	DOWN3
2011-04-12 00:00:00.000	18.97	0.2113	UP	1	UP1
2011-04-13 00:00:00.000	18.92	-0.2635	DOWN	1	DOWN1
2011-04-14 00:00:00.000	18.83	-0.4756	DOWN	2	DOWN2
2011-04-15 00:00:00.000	19.00	0.9028	UP	1	UP1
2011-04-18 00:00:00.000	19.16	0.8421	UP	2	UP2
2011-04-19 00:00:00.000	19.14	-0.1043	DOWN	1	DOWN1
2011-04-20 00:00:00.000	18.11	-5.3814	DOWN	2	DOWN2
2011-04-21 00:00:00.000	17.91	-1.1043	DOWN	3	DOWN3
2011-04-25 00:00:00.000	18.11	1.1166	UP	1	UP1
2011-04-26 00:00:00.000	17.44	-3.6996	DOWN	1	DOWN1
2011-04-27 00:00:00.000	17.69	1.4334	UP	1	UP1
2011-04-28 00:00:00.000	17.64	-0.2826	DOWN	1	DOWN1
2011-04-29 00:00:00.000	17.81	0.9637	UP	1	UP1
2011-05-02 00:00:00.000	17.91	0.5614	UP	2	UP2
2011-05-03 00:00:00.000	17.67	-1.34	DOWN	1	DOWN1
2011-05-04 00:00:00.000	17.59	-0.4527	DOWN	2	DOWN2
2011-05-05 00:00:00.000	17.32	-1.5349	DOWN	3	DOWN3
2011-05-06 00:00:00.000	16.87	-2.5981	DOWN	4	DOWN4
2011-05-09 00:00:00.000	16.69	-1.0669	DOWN	5	DOWN5
2011-05-10 00:00:00.000	16.87	1.0784	UP	1	UP1
2011-05-11 00:00:00.000	16.68	-1.1262	DOWN	1	DOWN1
2011-05-12 00:00:00.000	17.06	2.2781	UP	1	UP1
2011-05-13 00:00:00.000	17.36	1.7584	UP	2	UP2
2011-05-16 00:00:00.000	17.61	1.44	UP	3	UP3
2011-05-17 00:00:00.000	17.59	-0.1135	DOWN	1	DOWN1
2011-05-18 00:00:00.000	17.48	-0.6253	DOWN	2	DOWN2
2011-05-19 00:00:00.000	17.23	-1.4302	DOWN	3	DOWN3
2011-05-20 00:00:00.000	17.27	0.2321	UP	1	UP1
2011-05-23 00:00:00.000	17.50	1.3317	UP	2	UP2
2011-05-24 00:00:00.000	17.69	1.0857	UP	3	UP3
2011-05-25 00:00:00.000	17.84	0.8479	UP	4	UP4
2011-05-26 00:00:00.000	18.02	1.0089	UP	5	UP5
2011-05-27 00:00:00.000	17.95	-0.3884	DOWN	1	DOWN1
2011-05-31 00:00:00.000	17.99	0.2228	UP	1	UP1
2011-06-01 00:00:00.000	18.14	0.8337	UP	2	UP2
2011-06-02 00:00:00.000	18.41	1.4884	UP	3	UP3
2011-06-03 00:00:00.000	18.46	0.2715	UP	4	UP4
2011-06-06 00:00:00.000	18.61	0.8125	UP	5	UP5
2011-06-07 00:00:00.000	18.48	-0.6985	DOWN	1	DOWN1
2011-06-08 00:00:00.000	18.28	-1.0822	DOWN	2	DOWN2
2011-06-09 00:00:00.000	18.29	0.0547	UP	1	UP1
2011-06-10 00:00:00.000	18.32	0.164	UP	2	UP2
2011-06-13 00:00:00.000	18.34	0.1091	UP	3	UP3
2011-06-14 00:00:00.000	18.54	1.0905	UP	4	UP4
2011-06-15 00:00:00.000	18.68	0.7551	UP	5	UP5
2011-06-16 00:00:00.000	18.70	0.107	UP	6	UP6
2011-06-17 00:00:00.000	18.68	-0.1069	DOWN	1	DOWN1
2011-06-20 00:00:00.000	18.82	0.7494	UP	1	UP1
2011-06-21 00:00:00.000	18.79	-0.1594	DOWN	1	DOWN1
2011-06-22 00:00:00.000	18.54	-1.3304	DOWN	2	DOWN2
2011-06-23 00:00:00.000	18.53	-0.0539	DOWN	3	DOWN3
2011-06-24 00:00:00.000	18.54	0.0539	UP	1	UP1
2011-06-27 00:00:00.000	18.43	-0.5933	DOWN	1	DOWN1
2011-06-28 00:00:00.000	18.42	-0.0542	DOWN	2	DOWN2
2011-06-29 00:00:00.000	18.55	0.7057	UP	1	UP1
2011-06-30 00:00:00.000	18.68	0.7008	UP	2	UP2
2011-07-01 00:00:00.000	18.54	-0.7494	DOWN	1	DOWN1
2011-07-05 00:00:00.000	18.59	0.2696	UP	1	UP1
2011-07-06 00:00:00.000	18.75	0.8606	UP	2	UP2
2011-07-07 00:00:00.000	18.61	-0.7466	DOWN	1	DOWN1
2011-07-08 00:00:00.000	18.87	1.397	UP	1	UP1
2011-07-11 00:00:00.000	19.09	1.1658	UP	2	UP2
2011-07-12 00:00:00.000	19.18	0.4714	UP	3	UP3
2011-07-13 00:00:00.000	19.29	0.5735	UP	4	UP4
2011-07-14 00:00:00.000	19.37	0.4147	UP	5	UP5
2011-07-15 00:00:00.000	19.31	-0.3097	DOWN	1	DOWN1
2011-07-18 00:00:00.000	19.21	-0.5178	DOWN	2	DOWN2
2011-07-19 00:00:00.000	19.37	0.8328	UP	1	UP1
2011-07-20 00:00:00.000	19.44	0.3613	UP	2	UP2
2011-07-21 00:00:00.000	19.42	-0.1028	DOWN	1	DOWN1
2011-07-22 00:00:00.000	19.14	-1.4418	DOWN	2	DOWN2
2011-07-25 00:00:00.000	19.41	1.4106	UP	1	UP1
2011-07-26 00:00:00.000	19.42	0.0515	UP	2	UP2
2011-07-27 00:00:00.000	19.45	0.1544	UP	3	UP3
2011-07-28 00:00:00.000	19.69	1.2339	UP	4	UP4
2011-07-29 00:00:00.000	19.66	-0.1523	DOWN	1	DOWN1
2011-08-01 00:00:00.000	20.00	1.7293	UP	1	UP1
2011-08-02 00:00:00.000	19.89	-0.55	DOWN	1	DOWN1
2011-08-03 00:00:00.000	20.12	1.1563	UP	1	UP1
2011-08-04 00:00:00.000	20.00	-0.5964	DOWN	1	DOWN1
2011-08-05 00:00:00.000	19.78	-1.10	DOWN	2	DOWN2
2011-08-08 00:00:00.000	19.86	0.4044	UP	1	UP1
2011-08-09 00:00:00.000	19.85	-0.0503	DOWN	1	DOWN1
2011-08-10 00:00:00.000	19.71	-0.7052	DOWN	2	DOWN2
2011-08-11 00:00:00.000	19.89	0.9132	UP	1	UP1
2011-08-12 00:00:00.000	20.01	0.6033	UP	2	UP2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JPAucamp
Starting Member

49 Posts

Posted - 2011-08-31 : 12:35:27

Hi Visakh,

Thanks for this! I am unable to check it out at the moment but just wanted to say thank you so long.

Have a good day!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 12:37:03

ok...good luck

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JPAucamp
Starting Member

49 Posts

Posted - 2011-08-31 : 15:46:19

Hi again Visakh

I have checked your code and AWESOME!!! It is spot-on!! Thanks a million! You sure know your stuff!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 00:27:41

No need of cursor. just a small addition to earlier code


;With CTE
AS(
select p.PriceDate,p.Price,(p.Price-COALESCE(p1.Price,0))*100/p1.Price AS [Change%],
CASE WHEN p1.Price IS NULL THEN ''
	 WHEN p.Price-p1.Price > 0  THEN 'UP' 
	 WHEN p.Price-p1.Price = 0 THEN 'No MOVEMENT'
	 ELSE 'DOWN'
END AS Direction
from PriceData p
OUTER APPLY (SELECT TOP 1 Price
             FROM PriceData
             WHERE PriceDate< p.PriceDate
             ORDER BY PriceDate DESC
             )p1

)
SELECT PriceDate,Price,[Change%],Direction,
ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) AS [Count],
Direction + CAST(ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) as varchar(10)) AS [Direction_Count],
CASE WHEN PriceDate=MAX(PriceDate) OVER (PARTITION BY  PrevDate)
     THEN (Price-PrevPrice)*100/PrevPrice
     ELSE 0
     END AS  MoveOfSeries
FROM CTE c1
OUTER APPLY (SELECT TOP 1 PriceDate AS PrevDate,Price AS PrevPrice
             FROM CTE
             WHERE PriceDate<c1.PriceDate
             AND Direction <> c1.Direction
             ORDER BY PriceDate DESC)c2

output
-----------------------------------------------------
PriceDate	Price	Change%	Direction	Count	Direction_Count	MoveOfSeries
2010-10-28 00:00:00.000	16.66	NULL		1	1	NULL
2010-10-29 00:00:00.000	16.85	1.1404	UP	1	UP1	0.00
2010-11-01 00:00:00.000	16.93	0.4747	UP	2	UP2	0.00
2010-11-02 00:00:00.000	16.98	0.2953	UP	3	UP3	0.00
2010-11-03 00:00:00.000	17.08	0.5889	UP	4	UP4	2.521
2010-11-04 00:00:00.000	17.03	-0.2927	DOWN	1	DOWN1	-0.2927
2010-11-05 00:00:00.000	17.09	0.3523	UP	1	UP1	0.3523
2010-11-08 00:00:00.000	16.76	-1.9309	DOWN	1	DOWN1	0.00
2010-11-09 00:00:00.000	16.67	-0.5369	DOWN	2	DOWN2	-2.4575
2010-11-10 00:00:00.000	16.72	0.2999	UP	1	UP1	0.00
2010-11-11 00:00:00.000	16.86	0.8373	UP	2	UP2	1.1397
2010-11-12 00:00:00.000	16.85	-0.0593	DOWN	1	DOWN1	-0.0593
2010-11-15 00:00:00.000	16.87	0.1186	UP	1	UP1	0.00
2010-11-16 00:00:00.000	16.90	0.1778	UP	2	UP2	0.00
2010-11-17 00:00:00.000	16.92	0.1183	UP	3	UP3	0.4154
2010-11-18 00:00:00.000	16.86	-0.3546	DOWN	1	DOWN1	0.00
2010-11-19 00:00:00.000	16.74	-0.7117	DOWN	2	DOWN2	0.00
2010-11-22 00:00:00.000	16.73	-0.0597	DOWN	3	DOWN3	-1.1229
2010-11-23 00:00:00.000	16.82	0.5379	UP	1	UP1	0.00
2010-11-24 00:00:00.000	17.02	1.189	UP	2	UP2	0.00
2010-11-26 00:00:00.000	17.05	0.1762	UP	3	UP3	1.9127
2010-11-29 00:00:00.000	17.05	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2010-11-30 00:00:00.000	17.23	1.0557	UP	1	UP1	0.00
2010-12-01 00:00:00.000	17.29	0.3482	UP	2	UP2	0.00
2010-12-02 00:00:00.000	17.32	0.1735	UP	3	UP3	1.5835
2010-12-03 00:00:00.000	17.23	-0.5196	DOWN	1	DOWN1	0.00
2010-12-06 00:00:00.000	17.21	-0.116	DOWN	2	DOWN2	-0.6351
2010-12-07 00:00:00.000	17.28	0.4067	UP	1	UP1	0.4067
2010-12-08 00:00:00.000	17.24	-0.2314	DOWN	1	DOWN1	0.00
2010-12-09 00:00:00.000	17.06	-1.044	DOWN	2	DOWN2	-1.2731
2010-12-10 00:00:00.000	17.06	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2010-12-13 00:00:00.000	16.99	-0.4103	DOWN	1	DOWN1	-0.4103
2010-12-14 00:00:00.000	17.15	0.9417	UP	1	UP1	0.00
2010-12-15 00:00:00.000	17.20	0.2915	UP	2	UP2	1.236
2010-12-16 00:00:00.000	17.17	-0.1744	DOWN	1	DOWN1	-0.1744
2010-12-17 00:00:00.000	17.21	0.2329	UP	1	UP1	0.00
2010-12-20 00:00:00.000	17.28	0.4067	UP	2	UP2	0.6406
2010-12-21 00:00:00.000	17.28	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2010-12-22 00:00:00.000	17.22	-0.3472	DOWN	1	DOWN1	-0.3472
2010-12-23 00:00:00.000	17.24	0.1161	UP	1	UP1	0.1161
2010-12-27 00:00:00.000	17.23	-0.058	DOWN	1	DOWN1	-0.058
2010-12-28 00:00:00.000	17.29	0.3482	UP	1	UP1	0.00
2010-12-29 00:00:00.000	17.32	0.1735	UP	2	UP2	0.5223
2010-12-30 00:00:00.000	17.20	-0.6928	DOWN	1	DOWN1	0.00
2010-12-31 00:00:00.000	17.19	-0.0581	DOWN	2	DOWN2	0.00
2011-01-03 00:00:00.000	17.07	-0.698	DOWN	3	DOWN3	-1.4434
2011-01-04 00:00:00.000	17.09	0.1171	UP	1	UP1	0.00
2011-01-05 00:00:00.000	17.12	0.1755	UP	2	UP2	0.00
2011-01-06 00:00:00.000	17.25	0.7593	UP	3	UP3	0.00
2011-01-07 00:00:00.000	17.45	1.1594	UP	4	UP4	0.00
2011-01-10 00:00:00.000	17.49	0.2292	UP	5	UP5	2.4604
2011-01-11 00:00:00.000	17.45	-0.2287	DOWN	1	DOWN1	0.00
2011-01-12 00:00:00.000	17.44	-0.0573	DOWN	2	DOWN2	-0.2858
2011-01-13 00:00:00.000	17.45	0.0573	UP	1	UP1	0.00
2011-01-14 00:00:00.000	17.55	0.573	UP	2	UP2	0.00
2011-01-18 00:00:00.000	17.56	0.0569	UP	3	UP3	0.688
2011-01-19 00:00:00.000	17.56	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2011-01-20 00:00:00.000	17.46	-0.5694	DOWN	1	DOWN1	-0.5694
2011-01-21 00:00:00.000	17.53	0.4009	UP	1	UP1	0.4009
2011-01-24 00:00:00.000	17.44	-0.5134	DOWN	1	DOWN1	0.00
2011-01-25 00:00:00.000	17.30	-0.8027	DOWN	2	DOWN2	0.00
2011-01-26 00:00:00.000	17.29	-0.0578	DOWN	3	DOWN3	-1.369
2011-01-27 00:00:00.000	17.53	1.388	UP	1	UP1	0.00
2011-01-28 00:00:00.000	17.55	0.114	UP	2	UP2	0.00
2011-01-31 00:00:00.000	17.63	0.4558	UP	3	UP3	0.00
2011-02-01 00:00:00.000	17.78	0.8508	UP	4	UP4	0.00
2011-02-02 00:00:00.000	17.85	0.3937	UP	5	UP5	3.2388
2011-02-03 00:00:00.000	17.75	-0.5602	DOWN	1	DOWN1	-0.5602
2011-02-04 00:00:00.000	17.94	1.0704	UP	1	UP1	0.00
2011-02-07 00:00:00.000	17.98	0.2229	UP	2	UP2	1.2957
2011-02-08 00:00:00.000	17.96	-0.1112	DOWN	1	DOWN1	0.00
2011-02-09 00:00:00.000	17.88	-0.4454	DOWN	2	DOWN2	-0.5561
2011-02-10 00:00:00.000	18.03	0.8389	UP	1	UP1	0.00
2011-02-11 00:00:00.000	18.05	0.1109	UP	2	UP2	0.9507
2011-02-14 00:00:00.000	17.93	-0.6648	DOWN	1	DOWN1	-0.6648
2011-02-15 00:00:00.000	17.96	0.1673	UP	1	UP1	0.1673
2011-02-16 00:00:00.000	17.83	-0.7238	DOWN	1	DOWN1	-0.7238
2011-02-17 00:00:00.000	17.83	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2011-02-18 00:00:00.000	17.76	-0.3925	DOWN	1	DOWN1	-0.3925
2011-02-22 00:00:00.000	17.86	0.563	UP	1	UP1	0.00
2011-02-23 00:00:00.000	17.96	0.5599	UP	2	UP2	0.00
2011-02-24 00:00:00.000	18.22	1.4476	UP	3	UP3	2.59
2011-02-25 00:00:00.000	18.11	-0.6037	DOWN	1	DOWN1	-0.6037
2011-02-28 00:00:00.000	18.27	0.8834	UP	1	UP1	0.8834
2011-03-01 00:00:00.000	18.12	-0.821	DOWN	1	DOWN1	-0.821
2011-03-02 00:00:00.000	18.22	0.5518	UP	1	UP1	0.00
2011-03-03 00:00:00.000	18.27	0.2744	UP	2	UP2	0.8278
2011-03-04 00:00:00.000	18.27	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2011-03-07 00:00:00.000	18.29	0.1094	UP	1	UP1	0.1094
2011-03-08 00:00:00.000	18.29	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2011-03-09 00:00:00.000	18.18	-0.6014	DOWN	1	DOWN1	-0.6014
2011-03-10 00:00:00.000	18.26	0.44	UP	1	UP1	0.00
2011-03-11 00:00:00.000	18.44	0.9857	UP	2	UP2	0.00
2011-03-14 00:00:00.000	18.52	0.4338	UP	3	UP3	0.00
2011-03-15 00:00:00.000	18.56	0.2159	UP	4	UP4	0.00
2011-03-16 00:00:00.000	18.68	0.6465	UP	5	UP5	2.7502
2011-03-17 00:00:00.000	18.60	-0.4282	DOWN	1	DOWN1	-0.4282
2011-03-18 00:00:00.000	18.71	0.5913	UP	1	UP1	0.5913
2011-03-21 00:00:00.000	18.69	-0.1068	DOWN	1	DOWN1	-0.1068
2011-03-22 00:00:00.000	18.69	0.00	No MOVEMENT	1	No MOVEMENT1	0.00
2011-03-23 00:00:00.000	18.67	-0.107	DOWN	1	DOWN1	-0.107
2011-03-24 00:00:00.000	18.72	0.2678	UP	1	UP1	0.00
2011-03-25 00:00:00.000	18.78	0.3205	UP	2	UP2	0.5891
2011-03-28 00:00:00.000	18.77	-0.0532	DOWN	1	DOWN1	-0.0532
2011-03-29 00:00:00.000	18.79	0.1065	UP	1	UP1	0.1065
2011-03-30 00:00:00.000	18.60	-1.0111	DOWN	1	DOWN1	-1.0111
2011-03-31 00:00:00.000	18.88	1.5053	UP	1	UP1	0.00
2011-04-01 00:00:00.000	18.93	0.2648	UP	2	UP2	0.00
2011-04-04 00:00:00.000	19.14	1.1093	UP	3	UP3	0.00
2011-04-05 00:00:00.000	19.26	0.6269	UP	4	UP4	0.00
2011-04-06 00:00:00.000	19.40	0.7268	UP	5	UP5	4.301
2011-04-07 00:00:00.000	19.25	-0.7731	DOWN	1	DOWN1	0.00
2011-04-08 00:00:00.000	18.98	-1.4025	DOWN	2	DOWN2	0.00
2011-04-11 00:00:00.000	18.93	-0.2634	DOWN	3	DOWN3	-2.4226
2011-04-12 00:00:00.000	18.97	0.2113	UP	1	UP1	0.2113
2011-04-13 00:00:00.000	18.92	-0.2635	DOWN	1	DOWN1	0.00
2011-04-14 00:00:00.000	18.83	-0.4756	DOWN	2	DOWN2	-0.738
2011-04-15 00:00:00.000	19.00	0.9028	UP	1	UP1	0.00
2011-04-18 00:00:00.000	19.16	0.8421	UP	2	UP2	1.7525
2011-04-19 00:00:00.000	19.14	-0.1043	DOWN	1	DOWN1	0.00
2011-04-20 00:00:00.000	18.11	-5.3814	DOWN	2	DOWN2	0.00
2011-04-21 00:00:00.000	17.91	-1.1043	DOWN	3	DOWN3	-6.524
2011-04-25 00:00:00.000	18.11	1.1166	UP	1	UP1	1.1166
2011-04-26 00:00:00.000	17.44	-3.6996	DOWN	1	DOWN1	-3.6996
2011-04-27 00:00:00.000	17.69	1.4334	UP	1	UP1	1.4334
2011-04-28 00:00:00.000	17.64	-0.2826	DOWN	1	DOWN1	-0.2826
2011-04-29 00:00:00.000	17.81	0.9637	UP	1	UP1	0.00
2011-05-02 00:00:00.000	17.91	0.5614	UP	2	UP2	1.5306
2011-05-03 00:00:00.000	17.67	-1.34	DOWN	1	DOWN1	0.00
2011-05-04 00:00:00.000	17.59	-0.4527	DOWN	2	DOWN2	0.00
2011-05-05 00:00:00.000	17.32	-1.5349	DOWN	3	DOWN3	0.00
2011-05-06 00:00:00.000	16.87	-2.5981	DOWN	4	DOWN4	0.00
2011-05-09 00:00:00.000	16.69	-1.0669	DOWN	5	DOWN5	-6.8118
2011-05-10 00:00:00.000	16.87	1.0784	UP	1	UP1	1.0784
2011-05-11 00:00:00.000	16.68	-1.1262	DOWN	1	DOWN1	-1.1262
2011-05-12 00:00:00.000	17.06	2.2781	UP	1	UP1	0.00
2011-05-13 00:00:00.000	17.36	1.7584	UP	2	UP2	0.00
2011-05-16 00:00:00.000	17.61	1.44	UP	3	UP3	5.5755
2011-05-17 00:00:00.000	17.59	-0.1135	DOWN	1	DOWN1	0.00
2011-05-18 00:00:00.000	17.48	-0.6253	DOWN	2	DOWN2	0.00
2011-05-19 00:00:00.000	17.23	-1.4302	DOWN	3	DOWN3	-2.1578
2011-05-20 00:00:00.000	17.27	0.2321	UP	1	UP1	0.00
2011-05-23 00:00:00.000	17.50	1.3317	UP	2	UP2	0.00
2011-05-24 00:00:00.000	17.69	1.0857	UP	3	UP3	0.00
2011-05-25 00:00:00.000	17.84	0.8479	UP	4	UP4	0.00
2011-05-26 00:00:00.000	18.02	1.0089	UP	5	UP5	4.585
2011-05-27 00:00:00.000	17.95	-0.3884	DOWN	1	DOWN1	-0.3884
2011-05-31 00:00:00.000	17.99	0.2228	UP	1	UP1	0.00
2011-06-01 00:00:00.000	18.14	0.8337	UP	2	UP2	0.00
2011-06-02 00:00:00.000	18.41	1.4884	UP	3	UP3	0.00
2011-06-03 00:00:00.000	18.46	0.2715	UP	4	UP4	0.00
2011-06-06 00:00:00.000	18.61	0.8125	UP	5	UP5	3.6768
2011-06-07 00:00:00.000	18.48	-0.6985	DOWN	1	DOWN1	0.00
2011-06-08 00:00:00.000	18.28	-1.0822	DOWN	2	DOWN2	-1.7732
2011-06-09 00:00:00.000	18.29	0.0547	UP	1	UP1	0.00
2011-06-10 00:00:00.000	18.32	0.164	UP	2	UP2	0.00
2011-06-13 00:00:00.000	18.34	0.1091	UP	3	UP3	0.00
2011-06-14 00:00:00.000	18.54	1.0905	UP	4	UP4	0.00
2011-06-15 00:00:00.000	18.68	0.7551	UP	5	UP5	0.00
2011-06-16 00:00:00.000	18.70	0.107	UP	6	UP6	2.2975
2011-06-17 00:00:00.000	18.68	-0.1069	DOWN	1	DOWN1	-0.1069
2011-06-20 00:00:00.000	18.82	0.7494	UP	1	UP1	0.7494
2011-06-21 00:00:00.000	18.79	-0.1594	DOWN	1	DOWN1	0.00
2011-06-22 00:00:00.000	18.54	-1.3304	DOWN	2	DOWN2	0.00
2011-06-23 00:00:00.000	18.53	-0.0539	DOWN	3	DOWN3	-1.5409
2011-06-24 00:00:00.000	18.54	0.0539	UP	1	UP1	0.0539
2011-06-27 00:00:00.000	18.43	-0.5933	DOWN	1	DOWN1	0.00
2011-06-28 00:00:00.000	18.42	-0.0542	DOWN	2	DOWN2	-0.6472
2011-06-29 00:00:00.000	18.55	0.7057	UP	1	UP1	0.00
2011-06-30 00:00:00.000	18.68	0.7008	UP	2	UP2	1.4115
2011-07-01 00:00:00.000	18.54	-0.7494	DOWN	1	DOWN1	-0.7494
2011-07-05 00:00:00.000	18.59	0.2696	UP	1	UP1	0.00
2011-07-06 00:00:00.000	18.75	0.8606	UP	2	UP2	1.1326
2011-07-07 00:00:00.000	18.61	-0.7466	DOWN	1	DOWN1	-0.7466
2011-07-08 00:00:00.000	18.87	1.397	UP	1	UP1	0.00
2011-07-11 00:00:00.000	19.09	1.1658	UP	2	UP2	0.00
2011-07-12 00:00:00.000	19.18	0.4714	UP	3	UP3	0.00
2011-07-13 00:00:00.000	19.29	0.5735	UP	4	UP4	0.00
2011-07-14 00:00:00.000	19.37	0.4147	UP	5	UP5	4.0838
2011-07-15 00:00:00.000	19.31	-0.3097	DOWN	1	DOWN1	0.00
2011-07-18 00:00:00.000	19.21	-0.5178	DOWN	2	DOWN2	-0.826
2011-07-19 00:00:00.000	19.37	0.8328	UP	1	UP1	0.00
2011-07-20 00:00:00.000	19.44	0.3613	UP	2	UP2	1.1972
2011-07-21 00:00:00.000	19.42	-0.1028	DOWN	1	DOWN1	0.00
2011-07-22 00:00:00.000	19.14	-1.4418	DOWN	2	DOWN2	-1.5432
2011-07-25 00:00:00.000	19.41	1.4106	UP	1	UP1	0.00
2011-07-26 00:00:00.000	19.42	0.0515	UP	2	UP2	0.00
2011-07-27 00:00:00.000	19.45	0.1544	UP	3	UP3	0.00
2011-07-28 00:00:00.000	19.69	1.2339	UP	4	UP4	2.8735
2011-07-29 00:00:00.000	19.66	-0.1523	DOWN	1	DOWN1	-0.1523
2011-08-01 00:00:00.000	20.00	1.7293	UP	1	UP1	1.7293
2011-08-02 00:00:00.000	19.89	-0.55	DOWN	1	DOWN1	-0.55
2011-08-03 00:00:00.000	20.12	1.1563	UP	1	UP1	1.1563
2011-08-04 00:00:00.000	20.00	-0.5964	DOWN	1	DOWN1	0.00
2011-08-05 00:00:00.000	19.78	-1.10	DOWN	2	DOWN2	-1.6898
2011-08-08 00:00:00.000	19.86	0.4044	UP	1	UP1	0.4044
2011-08-09 00:00:00.000	19.85	-0.0503	DOWN	1	DOWN1	0.00
2011-08-10 00:00:00.000	19.71	-0.7052	DOWN	2	DOWN2	-0.7552
2011-08-11 00:00:00.000	19.89	0.9132	UP	1	UP1	0.00
2011-08-12 00:00:00.000	20.01	0.6033	UP	2	UP2	1.522

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-01 : 08:52:56

Visakh- the man of the hour, the man with the power, too sweet to be sour. That is awesome! You have been so helpful I can't thank you enough!

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-03 : 15:32:08

Visakh, further to my above post I have tried to get the 2nd and 3rd CASE statements to work to get the reversal indicator and the MovementRange columns populated. Unfortunately I can't get it to work.

Please give me some advice on where the code should be placed!!

Thanks!

My three version for the MovementRange looks like this:

--Version1
;With CTE
AS(
select p.PriceDate,p.Price,(p.Price-COALESCE(p1.Price,0))*100/p1.Price AS [Change%],
CASE WHEN p1.Price is NULL THEN 'No Movement'
WHEN (p.Price-COALESCE(p1.Price,0))*100/p1.Price BETWEEN -100 and -1 THEN 'DOWN >1%'
WHEN (p.Price-COALESCE(p1.Price,0))*100/p1.Price BETWEEN 1 and 100 THEN 'UP >1%'
ELSE 'INBETWEEN'
END AS MovementRange
from PriceData p
OUTER APPLY (SELECT TOP 1 Price
FROM PriceData
WHERE PriceDate< p.PriceDate
ORDER BY PriceDate DESC
)p1

)

--Version2
Select [Change%],
FROM CTE
CASE WHEN [Change%]BETWEEN -100 and -1 THEN 'DOWN >1%'
WHEN [Change%] BETWEEN 1 and 100 THEN 'UP >1%'
ELSE 'INBETWEEN'
END AS MovementRange

--Version3
Select [Change%],
CASE WHEN [Change%]BETWEEN -100 and -1 THEN 'DOWN >1%'
WHEN [Change%] BETWEEN 1 and 100 THEN 'UP >1%'
ELSE 'INBETWEEN'
END AS MovementRange
FROM CTE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 01:02:56

see modified code below for ReversalIndicator.
For Moving ranges rather than having a set of case statements its much better to have them stored in another table and then do a join with them in code below to get associated range. this will enable you to add new ranges or redefine existing ones by changing values in the table and will increase maintanability as well as scalability of the code


;With CTE
AS(
select p.PriceDate,p.Price,(p.Price-COALESCE(p1.Price,0))*100/p1.Price AS [Change%],
CASE WHEN p1.Price IS NULL THEN ''
	 WHEN p.Price-p1.Price > 0  THEN 'UP' 
	 WHEN p.Price-p1.Price = 0 THEN 'No MOVEMENT'
	 ELSE 'DOWN'
END AS Direction
from PriceData p
OUTER APPLY (SELECT TOP 1 Price
             FROM PriceData
             WHERE PriceDate< p.PriceDate
             ORDER BY PriceDate DESC
             )p1

)
SELECT PriceDate,Price,[Change%],c1.Direction,
ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) AS [Count],
c1.Direction + CAST(ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) as varchar(10)) AS [Direction_Count],
CASE WHEN PriceDate=MAX(PriceDate) OVER (PARTITION BY  PrevDate)
     THEN (Price-PrevPrice)*100/PrevPrice
     ELSE 0
     END AS  MoveOfSeries,
CASE WHEN c3.Direction = c1.Direction THEN 'No' ELSE 'Yes' END AS ReversalIndicator
FROM CTE c1
OUTER APPLY (SELECT TOP 1 PriceDate AS PrevDate,Price AS PrevPrice
             FROM CTE
             WHERE PriceDate<c1.PriceDate
             AND Direction <> c1.Direction
             ORDER BY PriceDate DESC)c2
OUTER APPLY (SELECT TOP 1 Direction
             FROM CTE
             WHERE PriceDate>c1.PriceDate
             ORDER BY PriceDate ASC)c3



PriceDate               Price                 Change%               Direction   Count                Direction_Count       MoveOfSeries          ReversalIndicator
----------------------- --------------------- --------------------- ----------- -------------------- --------------------- --------------------- -----------------
2010-10-28 00:00:00.000 16.66                 NULL                              1                    1                     NULL                  Yes
2010-10-29 00:00:00.000 16.85                 1.1404                UP          1                    UP1                   0.00                  No
2010-11-01 00:00:00.000 16.93                 0.4747                UP          2                    UP2                   0.00                  No
2010-11-02 00:00:00.000 16.98                 0.2953                UP          3                    UP3                   0.00                  No
2010-11-03 00:00:00.000 17.08                 0.5889                UP          4                    UP4                   2.521                 Yes
2010-11-04 00:00:00.000 17.03                 -0.2927               DOWN        1                    DOWN1                 -0.2927               Yes
2010-11-05 00:00:00.000 17.09                 0.3523                UP          1                    UP1                   0.3523                Yes
2010-11-08 00:00:00.000 16.76                 -1.9309               DOWN        1                    DOWN1                 0.00                  No
2010-11-09 00:00:00.000 16.67                 -0.5369               DOWN        2                    DOWN2                 -2.4575               Yes
2010-11-10 00:00:00.000 16.72                 0.2999                UP          1                    UP1                   0.00                  No
2010-11-11 00:00:00.000 16.86                 0.8373                UP          2                    UP2                   1.1397                Yes
2010-11-12 00:00:00.000 16.85                 -0.0593               DOWN        1                    DOWN1                 -0.0593               Yes
2010-11-15 00:00:00.000 16.87                 0.1186                UP          1                    UP1                   0.00                  No
2010-11-16 00:00:00.000 16.90                 0.1778                UP          2                    UP2                   0.00                  No
2010-11-17 00:00:00.000 16.92                 0.1183                UP          3                    UP3                   0.4154                Yes
2010-11-18 00:00:00.000 16.86                 -0.3546               DOWN        1                    DOWN1                 0.00                  No
2010-11-19 00:00:00.000 16.74                 -0.7117               DOWN        2                    DOWN2                 0.00                  No
2010-11-22 00:00:00.000 16.73                 -0.0597               DOWN        3                    DOWN3                 -1.1229               Yes
2010-11-23 00:00:00.000 16.82                 0.5379                UP          1                    UP1                   0.00                  No
2010-11-24 00:00:00.000 17.02                 1.189                 UP          2                    UP2                   1.7334                Yes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 01:53:39

the table should be something like


CREATE TABLE MovingRanges  
(
ID int IDENTITY(1,1) PRIMARY KEY,
StartValue Numeric(10,3),
EndValue Numeric(10,3),
RangeDescription varchar(10)
)

and populate it like:-
INSERT MovingRanges (StartValue,EndValue,RangeDescription)
VALUES (-1,-0.2,'DOWN-20.00% +'),
(-0.15,-0.1999,'DOWN-15.00%-19.99%'),
(-0.1,-0.1499,'DOWN-10.00%-14.99%'),
(-0.08,-0.0999,'DOWN-8.00%-9.99%'),
(-0.0775,-0.0799,'DOWN-7.75%-7.99%')
......

and then use it like

;With CTE
AS(
select p.PriceDate,p.Price,(p.Price-COALESCE(p1.Price,0))*100/p1.Price AS [Change%],
CASE WHEN p1.Price IS NULL THEN ''
	 WHEN p.Price-p1.Price > 0  THEN 'UP' 
	 WHEN p.Price-p1.Price = 0 THEN 'No MOVEMENT'
	 ELSE 'DOWN'
END AS Direction
from PriceData p
OUTER APPLY (SELECT TOP 1 Price
             FROM PriceData
             WHERE PriceDate< p.PriceDate
             ORDER BY PriceDate DESC
             )p1

)

SELECT PriceDate,Price,Direction,[Count],[Direction_Count],MoveOfSeries,ReversalIndicator,mr.RangeDescription 
FROM
(
SELECT PriceDate,Price,[Change%],c1.Direction,
ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) AS [Count],
c1.Direction + CAST(ROW_NUMBER() OVER (PARTITION BY  PrevDate ORDER BY PriceDate) as varchar(10)) AS [Direction_Count],
CASE WHEN PriceDate=MAX(PriceDate) OVER (PARTITION BY  PrevDate)
     THEN (Price-PrevPrice)*100/PrevPrice
     ELSE 0
     END AS  MoveOfSeries,
CASE WHEN c3.Direction = c1.Direction THEN 'No' ELSE 'Yes' END AS ReversalIndicator
FROM CTE c1
OUTER APPLY (SELECT TOP 1 PriceDate AS PrevDate,Price AS PrevPrice
             FROM CTE
             WHERE PriceDate<c1.PriceDate
             AND Direction <> c1.Direction
             ORDER BY PriceDate DESC)c2
OUTER APPLY (SELECT TOP 1 Direction
             FROM CTE
             WHERE PriceDate>c1.PriceDate
             ORDER BY PriceDate ASC)c3
)p
CROSS APPLY (SELECT RangeDescription 
             FROM MovingRanges
             WHERE MoveOfSeries BETWEEN StartValue AND EndValue
             )mr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-04 : 18:43:07

Mr Viaskh! Thank you X millions! I appreciate it so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 21:17:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-07 : 00:29:07

Hi Visakh! Sorry but I have to ask your help once again!

I only had time tonight to work on the code you so kindly wrote me a few days ago and have noticed that some of the values shown in the results appear to be incorrect or it is missing altogether. For instance when I run the code the value for the 4th of Nov is not shown although this should reflect a down movement. The same is true for example for the 12th of Nov, the 22nd of Nov. and the 6th of Dec. Would you mind taking a look at why this is happening? I was also wondering if it would be possible to include the Change% in the result set. Should this go into the select statement after the OUTER APPLY?. I did try this and then my direction column values were completely wrong so I have removed it again.

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-07 : 00:31:16

Here are my result set:
PriceDate Price Direction Count Direction_Count MoveOfSeries ReversalIndicator RangeDescription
2010-10-29 16.85 UP 1 UP1 0.00 No UP-<0.25%
2010-11-01 16.93 UP 2 UP2 0.00 No UP-<0.25%
2010-11-02 16.98 UP 3 UP3 0.00 No UP-<0.25%
2010-11-03 17.08 UP 4 UP4 2.521 Yes UP-2.50%-2.74%
2010-11-05 17.09 UP 1 UP1 0.3523 Yes UP-0.25%<0.49%
2010-11-08 16.76 DOWN 1 DOWN1 0.00 No UP-<0.25%
2010-11-10 16.72 UP 1 UP1 0.00 No UP-<0.25%
2010-11-11 16.86 UP 2 UP2 1.1397 Yes UP-1%<1.24%
2010-11-15 16.87 UP 1 UP1 0.00 No UP-<0.25%
2010-11-16 16.90 UP 2 UP2 0.00 No UP-<0.25%
2010-11-17 16.92 UP 3 UP3 0.4154 Yes UP-0.25%<0.49%
2010-11-18 16.86 DOWN 1 DOWN1 0.00 No UP-<0.25%
2010-11-19 16.74 DOWN 2 DOWN2 0.00 No UP-<0.25%
2010-11-23 16.82 UP 1 UP1 0.00 No UP-<0.25%
2010-11-24 17.02 UP 2 UP2 0.00 No UP-<0.25%
2010-11-26 17.05 UP 3 UP3 1.9127 Yes UP-1.75%<1.99%
2010-11-29 17.05 No MOVEMENT 1 No MOVEMENT1 0.00 Yes UP-<0.25%
2010-11-30 17.23 UP 1 UP1 0.00 No UP-<0.25%
2010-12-01 17.29 UP 2 UP2 0.00 No UP-<0.25%
2010-12-02 17.32 UP 3 UP3 1.5835 Yes UP-1.50%<1.74%
2010-12-03 17.23 DOWN 1 DOWN1 0.00 No UP-<0.25%
2010-12-07 17.28 UP 1 UP1 0.4067 Yes UP-0.25%<0.49%
2010-12-08 17.24 DOWN 1 DOWN1 0.00 No UP-<0.25%
2010-12-10 17.06 No MOVEMENT 1 No MOVEMENT1 0.00 Yes UP-<0.25%
2010-12-14 17.15 UP 1 UP1 0.00 No UP-<0.25%
2010-12-15 17.20 UP 2 UP2 1.236 Yes UP-1%<1.24%
2010-12-17 17.21 UP 1 UP1 0.00 No UP-<0.25%
2010-12-20 17.28 UP 2 UP2 0.6406 Yes UP-0.50%<0.74%
2010-12-21 17.28 No MOVEMENT 1 No MOVEMENT1 0.00 Yes UP-<0.25%

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-07 : 08:04:37

Me again Visakh! I went back to the previous version of the code and checked to make sure that the issues I refer to above only started with the last change and it is definitely so. Prior to the last update for the reversal indicator and moving ranges the results were perfect. As I only understand about half of your code I can't see where the issue comes from. As always your help is MUCH appreciated.

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-07 : 16:38:19

Hi Visakh, i think i have managed to sort the issue out but would still like you to check it out. I will also compare the data results fully later tonight to make sure that I am getting the right results but it seemed like the only change needed was to change the last CROSS APPLY on the RangeDescription select to an OUTER APPLY. Please let me know if you think this is wrong!

I would also like to ask you two further things if you don't mind.
1.The one is a moving average calculation based on two different periods. The one should be a fixed period being the MA for the last 14 prices. ( I am thinking it should be based on a number of instances rather than dates as otherwise the whole thing about weekends and holidays come into play)
2.The second moving average should be based on a parameter that can be passed so that I can change the number of price instances used in the calculation of it. I know a function exists DATEDIFF which I guess can be used if dates were used but how would this be done using price instances such as the PriceRowNo.

As always grateful for your help!

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-07 : 17:01:32

Visakh, sorry but I spoke to soon. The results are still not correct after changing it to OUTER APPLY. I will await your advice but I have a feeling it has something to do with my range table?? Thanks

JPAucamp
Starting Member

49 Posts

Posted - 2011-09-08 : 23:34:33

Hi again Visakh. I am posting the results of the code as it stands at the moment.

The 1st result set is from the original code prior to the reversal indicator and range description being added. This I checked against the expected results and it works perfectly. The second result set is from the cross apply version of the range description. as you will see from the results it is omitting some of the data for example the down day on the 4th of nov 2010. It picks up the down day on the 8th of Nov but the 9th is missing again. Like I said in my previous post I have a feeling it is with my range description table as it seems it is exclusively missing some down days and not returning any results from those days in any case for the range description if I do an outer apply which is the 3rd result set.

Would really appreciate your assistance with this and also with the MA question I posted 2 posts ago. Also, if you could explain what the OVER (PARTITION BY...) command does to me as if I was 3 years old I would love to understand it as it seems to be central to your code. Thanks again!

Here are the results as mentioned above:

Results prior to reversal indicator and range

PriceDate Price Change% Direction Count Direction_Count MoveOfSeries
2010-10-28 00:00:00.000 16.66 NULL 1 1 NULL
2010-10-29 00:00:00.000 16.85 1.1404 UP 1 UP1 0.00
2010-11-01 00:00:00.000 16.93 0.4747 UP 2 UP2 0.00
2010-11-02 00:00:00.000 16.98 0.2953 UP 3 UP3 0.00
2010-11-03 00:00:00.000 17.08 0.5889 UP 4 UP4 2.521
2010-11-04 00:00:00.000 17.03 -0.2927 DOWN 1 DOWN1 -0.2927
2010-11-05 00:00:00.000 17.09 0.3523 UP 1 UP1 0.3523
2010-11-08 00:00:00.000 16.76 -1.9309 DOWN 1 DOWN1 0.00
2010-11-09 00:00:00.000 16.67 -0.5369 DOWN 2 DOWN2 -2.4575
2010-11-10 00:00:00.000 16.72 0.2999 UP 1 UP1 0.00
2010-11-11 00:00:00.000 16.86 0.8373 UP 2 UP2 1.1397
2010-11-12 00:00:00.000 16.85 -0.0593 DOWN 1 DOWN1 -0.0593
2010-11-15 00:00:00.000 16.87 0.1186 UP 1 UP1 0.00
2010-11-16 00:00:00.000 16.90 0.1778 UP 2 UP2 0.00
2010-11-17 00:00:00.000 16.92 0.1183 UP 3 UP3 0.4154
2010-11-18 00:00:00.000 16.86 -0.3546 DOWN 1 DOWN1 0.00
2010-11-19 00:00:00.000 16.74 -0.7117 DOWN 2 DOWN2 0.00
2010-11-22 00:00:00.000 16.73 -0.0597 DOWN 3 DOWN3 -1.1229
2010-11-23 00:00:00.000 16.82 0.5379 UP 1 UP1 0.00
2010-11-24 00:00:00.000 17.02 1.189 UP 2 UP2 0.00
2010-11-26 00:00:00.000 17.05 0.1762 UP 3 UP3 1.9127
2010-11-29 00:00:00.000 17.05 0.00 No MOVEMENT 1 No MOVEMENT1 0.00
2010-11-30 00:00:00.000 17.23 1.0557 UP 1 UP1 0.00
2010-12-01 00:00:00.000 17.29 0.3482 UP 2 UP2 0.00
2010-12-02 00:00:00.000 17.32 0.1735 UP 3 UP3 1.5835
2010-12-03 00:00:00.000 17.23 -0.5196 DOWN 1 DOWN1 0.00
2010-12-06 00:00:00.000 17.21 -0.116 DOWN 2 DOWN2 -0.6351
2010-12-07 00:00:00.000 17.28 0.4067 UP 1 UP1 0.4067
2010-12-08 00:00:00.000 17.24 -0.2314 DOWN 1 DOWN1 0.00
2010-12-09 00:00:00.000 17.06 -1.044 DOWN 2 DOWN2 -1.2731
2010-12-10 00:00:00.000 17.06 0.00 No MOVEMENT 1 No MOVEMENT1 0.00
2010-12-13 00:00:00.000 16.99 -0.4103 DOWN 1 DOWN1 -0.4103

CROSS APPLY

PriceDate Price Direction Count Direction_Count MoveOfSeries ReversalIndicator RangeDescription
29/10/2010 16.85 UP 1 UP1 0 No UP-<0.25%
01/11/2010 16.93 UP 2 UP2 0 No UP-<0.25%
02/11/2010 16.98 UP 3 UP3 0 No UP-<0.25%
03/11/2010 17.08 UP 4 UP4 2.521 Yes UP-2.50%-2.74%
05/11/2010 17.09 UP 1 UP1 0.3523 Yes UP-0.25%<0.49%
08/11/2010 16.76 DOWN 1 DOWN1 0 No UP-<0.25%
10/11/2010 16.72 UP 1 UP1 0 No UP-<0.25%
11/11/2010 16.86 UP 2 UP2 1.1397 Yes UP-1%<1.24%
15/11/2010 16.87 UP 1 UP1 0 No UP-<0.25%
16/11/2010 16.9 UP 2 UP2 0 No UP-<0.25%
17/11/2010 16.92 UP 3 UP3 0.4154 Yes UP-0.25%<0.49%
18/11/2010 16.86 DOWN 1 DOWN1 0 No UP-<0.25%
19/11/2010 16.74 DOWN 2 DOWN2 0 No UP-<0.25%
23/11/2010 16.82 UP 1 UP1 0 No UP-<0.25%
24/11/2010 17.02 UP 2 UP2 0 No UP-<0.25%
26/11/2010 17.05 UP 3 UP3 1.9127 Yes UP-1.75%<1.99%
29/11/2010 17.05 No MOVEMENT 1 No MOVEMENT1 0 Yes UP-<0.25%
30/11/2010 17.23 UP 1 UP1 0 No UP-<0.25%
01/12/2010 17.29 UP 2 UP2 0 No UP-<0.25%
02/12/2010 17.32 UP 3 UP3 1.5835 Yes UP-1.50%<1.74%
03/12/2010 17.23 DOWN 1 DOWN1 0 No UP-<0.25%
07/12/2010 17.28 UP 1 UP1 0.4067 Yes UP-0.25%<0.49%

OUTER APPLY

PriceDate Price Direction Count Direction_Count MoveOfSeries ReversalIndicator RangeDescription
28/10/2010 16.66 1 1 NULL Yes NULL
29/10/2010 16.85 UP 1 UP1 0 No UP-<0.25%
01/11/2010 16.93 UP 2 UP2 0 No UP-<0.25%
02/11/2010 16.98 UP 3 UP3 0 No UP-<0.25%
03/11/2010 17.08 UP 4 UP4 2.521 Yes UP-2.50%-2.74%
04/11/2010 17.03 DOWN 1 DOWN1 -0.2927 Yes NULL
05/11/2010 17.09 UP 1 UP1 0.3523 Yes UP-0.25%<0.49%
08/11/2010 16.76 DOWN 1 DOWN1 0 No UP-<0.25%
09/11/2010 16.67 DOWN 2 DOWN2 -2.4575 Yes NULL
10/11/2010 16.72 UP 1 UP1 0 No UP-<0.25%
11/11/2010 16.86 UP 2 UP2 1.1397 Yes UP-1%<1.24%
12/11/2010 16.85 DOWN 1 DOWN1 -0.0593 Yes NULL
15/11/2010 16.87 UP 1 UP1 0 No UP-<0.25%
16/11/2010 16.9 UP 2 UP2 0 No UP-<0.25%
17/11/2010 16.92 UP 3 UP3 0.4154 Yes UP-0.25%<0.49%
18/11/2010 16.86 DOWN 1 DOWN1 0 No UP-<0.25%
19/11/2010 16.74 DOWN 2 DOWN2 0 No UP-<0.25%
22/11/2010 16.73 DOWN 3 DOWN3 -1.1229 Yes NULL
23/11/2010 16.82 UP 1 UP1 0 No UP-<0.25%
24/11/2010 17.02 UP 2 UP2 0 No UP-<0.25%
26/11/2010 17.05 UP 3 UP3 1.9127 Yes UP-1.75%<1.99%
29/11/2010 17.05 No MOVEMENT 1 No MOVEMENT1 0 Yes UP-<0.25%

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources