Calculating - 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 2000 Forums

Transact-SQL (2000)

Calculating

Author

Topic

tariqali
Starting Member

7 Posts

Posted - 2006-10-16 : 11:35:26

Hi,

I have this table:

x.weeknumber|x.weekestart|x.weekending|x.New
35 08/28/2006 09/02/2006 3307
36 09/03/2006 09/09/2006 2714
37 09/10/2006 09/16/2006 3349
38 09/17/2006 09/23/2006 3439
39 09/25/2006 09/30/2006 3503
40 10/01/2006 10/07/2006 3408

What I am trying to do is create a new column called "Increase". that column will give me the difference in New value between current week and last week. so for example, Weeknumber 36 should have 2714-3307 = (593). I can't seem to figure this out and was hoping someone can help... I would really appreciate it!

THANKS.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-16 : 11:37:40

Alter table x add Increase int
update x1
set Increase = x1.New - x2.New
from x x1
join x x2
on x1.weeknumber - x2.weeknumber + 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tariqali
Starting Member

7 Posts

Posted - 2006-10-16 : 13:04:36

Sorry for not making myself clear in my first post. The table above is the results for the following select"

SELECT     x.weeknumber, x.weekestart, x.weekending, x.New
FROM

(SELECT DATEPART(Week,ADATE)weeknumber, Convert(varchar,Min(a.adate),101) as weekestart, Convert(VARCHAR,Max(a.adate),101) as weekending,
SUM(CASE WHEN a.SORD = 'N' THEN 1 ELSE 0 END) AS New, 
			SUM(CASE WHEN a.SORD = 'C' THEN 1 ELSE 0 END) AS Changed, 
			SUM(CASE WHEN a.SORD = 'D' THEN 1 ELSE 0 END) AS Disconnect_, 
			SUM(CASE WHEN a.SORD = 'F' THEN 1 ELSE 0 END) AS From_, 
			SUM(CASE WHEN a.SORD = 'T' THEN 1 ELSE 0 END) AS To_, 
			SUM(CASE WHEN a.SORD = 'R' THEN 1 ELSE 0 END) AS RecordAll_, 
			SUM((CASE WHEN a.[LISTING] IN ('o', 'i', 'ir', 'OR') AND a.SORD = 'R' THEN 1 ELSE 0 END)) AS IO, 
			SUM((CASE WHEN a.SORD IN ('N', 'C', 'D', 'F', 'T', 'R') THEN 1 ELSE 0 END)) AS ProductFamilyTotal



FROM         dbo.ABS_GLOBAL_ORDER_VOLUMES a
INNER JOIN
                      dbo.ABS_GLOBAL_CLASS_SERVICE_DESC ON a.CLSRV = dbo.ABS_GLOBAL_CLASS_SERVICE_DESC.CLASS_OF_SERVICE_CD
WHERE     (dbo.ABS_GLOBAL_CLASS_SERVICE_DESC.[Core Voice or Metro Transport] = N'Core Voice')

GROUP BY DATEPART(Week,ADATE)) x

How can I add the new column "increase" to caluclate the difference in the "New" column for each week?

thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 20:59:02

use a temp table.

insert the result of your query into a temp table and use nr's script

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources