Help me write sql script

Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:

SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.

or it could be something like this:

(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC.

Please help me translate this into real sql query :)

Could you provide your table structure with some data example like last two weeks?

Thanks

|||There are two related tables. First table tblValues has structure like this:

ItemID

DayID

Value

1

261

1086,8986

2

262

1110,3700

3

263

1110,3700

4

264

1167,9900

5

265

1121,2900

6

266

1121,2900

7

267

1100,9600

8

268

1100,9600

9

269

1061,1000

10

270

1061,1000

11

271

985,6700

12

272

918,1300

13

273

908,5200

14

274

908,5200

and tblDate has structure like this

DayID

Date

261

28.1.2007

262

29.1.2007

263

30.1.2007

264

31.1.2007

265

1.2.2007

266

2.2.2007

267

3.2.2007

268

4.2.2007

269

5.2.2007

270

6.2.2007

271

7.2.2007

272

8.2.2007

273

9.2.2007

274

10.2.2007

|||

try this I hope it will point you in good direction:

createtable #test1(ItemIDint,

DayID

int,Valuenumeric(12,4))

insert

into #test1

Values

(1, 261, 1086.8986)

insert

into #test1

Values

(2, 262, 1110.3700)

insert

into #test1

Values

(3, 263, 1110.3700)

insert

into #test1

Values

(4, 264, 1167.9900)

insert

into #test1

Values

(5, 265, 1121.2900)

insert

into #test1

Values

(6, 266, 1121.2900)

insert

into #test1

Values

(7, 267, 1100.9600)

insert

into #test1

Values

(8, 268, 1100.9600)

insert

into #test1

Values

(9, 269, 1061.1000)

insert

into #test1

Values

(10, 270, 1061.1000)

insert

into #test1

Values

(11, 271, 985.6700)

insert

into #test1

Values

(12, 272, 918.1300)

insert

into #test1

Values

(13, 273, 908.5200)

insert

into #test1

Values

(14, 274, 908.5200)

create

table #days(DayIDint,

Date

datetime)

insert

into #days

Values

(261,convert(datetime,'28.1.2007',104))

insert

into #days

Values

(262,convert(datetime,'29.1.2007',104))

insert

into #days

Values

(263,convert(datetime,'30.1.2007',104))

insert

into #days

Values

(264,convert(datetime,'31.1.2007',104))

insert

into #days

Values

(265,convert(datetime,'1.2.2007',104))

insert

into #days

Values

(266,convert(datetime,'2.2.2007',104))

insert

into #days

Values

(267,convert(datetime,'3.2.2007',104))

insert

into #days

Values

(268,convert(datetime,'4.2.2007',104))

insert

into #days

Values

(269,convert(datetime,'5.2.2007',104))

insert

into #days

Values

(270,convert(datetime,'6.2.2007',104))

insert

into #days

Values

(271,convert(datetime,'7.2.2007',104))

insert

into #days

Values

(272,convert(datetime,'8.2.2007',104))

insert

into #days

Values

(273,convert(datetime,'9.2.2007',104))

insert

into #days

Values

(274,convert(datetime,'10.2.2007',104))

select

day1 [WeekDay],(cc.value-dd.value) [difference],*from(select day1,max(dayID) cur,MIN(dayID) prevfrom(selectdatepart(weekday,date) day1, dayIDfrom #days

where

date>dateadd(day,-13,getdate()))aa

group

by day1)aa

left

join #test1 ccON cc.dayid=aa.cur

left

join #test1 ddON dd.dayid=aa.prev

select

*from #test1

drop

table #days

drop

table #test1