SQL Server Performance Forum – Threads Archive

SQ: Query

Hi All,
I have a table which has 4 fields (Code, Year, Amt1, Amt2). Code Year Amt1 Amt2
C1 2000 1111 1111
C1 2001 2222 2222
C1 2002 3333 3333
C1 2003 4444 4444 A user will enter Code and a range of year like 2000-2002. The query will take the rane of years and calculate the average of Amt1 Year 1999 and 2000, 2000-2001, 2001-2002 and display the result. If the data for the Year 1999 doesn’t exist, it will not calculate the average of 1999-2000. Can anyone help me to form the query? Thanks

---

Refer Cross-tab Reports in sql server help file Madhivanan Failing to plan is Planning to fail

---

try this: create table c1(Code varchar(2), cnYear int, Amt1 int, Amt2 int) insert into c1 values(‘C1’, 2000, 1111, 1111)
insert into c1 values(‘C1’, 2001 ,2222 ,2222)
insert into c1 values(‘C1’, 2002 ,3333 ,3333)
insert into c1 values(‘C1’, 2003 ,4444 ,4444) declare @styear int,@endyear int
select @styear=2000,@endyear =2003 select convert(varchar(4),c1.cnyear)+’-‘+convert(varchar(4),c2.cnyear),(c1.amt1+c2.amt1)/2 from c1 inner join c1 c2 on c1.cnyear=c2.cnyear-1
where c1.cnyear between @styear-1 and @endyear
and c2.cnyear between @styear and @endyear

---

]]>