Need help on SQL statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need help on SQL statement

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-11-28 : 08:29:18

My table and data as follow


declare @t1 table
(idx int identity,dtTaken datetime, tTaken float);
insert into @t1 values ('9/1/2010',9.03);
insert into @t1 values ('9/15/2010',8.45);
insert into @t1 values ('9/30/2010',8.5);
insert into @t1 values ('10/1/2010',6.09);
insert into @t1 values ('10/15/2010',4.04);
insert into @t1 values ('10/30/2010',6.11);
insert into @t1 values ('11/1/2010',1.23);
insert into @t1 values ('11/15/2010',0.2);
insert into @t1 values ('11/30/2010',0.15);

Above data is time taken for perform work based on date. Looks like,
1 Sept 2010 to 30 Nov 2010 time taken is decrease.

How my SQL looks like to calculate % of decrease from 1 Sept 2010 to 30 Nov 2010?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 08:36:33

[code]
Select max(tTaken) as HighestTime
,min(tTaken) as LowestTime
,max(tTaken)-min(tTaken) as ChangeinTime
,convert(numeric(5,2),(max(tTaken)-min(tTaken))/max(tTaken)) as PercChange
FROM @t1
Where dtTaken >='9/1/2010' and dtTaken <='11/30/2010'
[/code]

Poor planning on your part does not constitute an emergency on my part.