Aggregate Field Problem - 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

SQL Server Development (2000)

Aggregate Field Problem

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-11 : 17:13:30

I have an excel file that I am importing into a SQL Server 2000 database using DTS. Originally all of the data goes into a holding table for me to use before I move the data into its proper tables.

Here is the problem I'm having. One of the columns in the excel spreadsheet is a milage value. Unfortunetly the process the business uses to put a value in that field is to take a reading of the odometer. This works fine for them, but I need to find a way to take these values and calculate the actual length of the segment.

ex:
Segment - Milage
1 - .1
2 - .2
3 - .3
4 - 1.4

I would want the values
.1
.1
.1
1.1

Is there a way to do this? And conversely I need to be able to export this data back out to an excel file using a query. I would then need get a cumulative sum of the milage up to a particular segment.

Do these questions make any sense and is there a good solution to this problem?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-11 : 18:19:35

This is a tough one, but it's possible that this article might point you in the right direction.

http://www.sqlteam.com/item.asp?ItemID=3856

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-11 : 19:56:20

OK, first, you need to make sure you have a unique ID for each of these records -- a combination such as EmpID, Segment.

If that is the case, then take a look at:

--

declare @t table (EmpID int, Segment int, Mileage decimal(10,2))

insert into @t
select 1,1,.1 union
select 1,2,.2 union
select 1,3,.3 union
select 1,4,1.4

SELECT A.EmpID, A.StartSegment, A.EndSegment, A.EndMiles - ISNULL(B.Mileage,0) AS Miles
FROM
(
SELECT T1.EmpID, T1.Segment as EndSegment, T1.Mileage as EndMiles,
(SELECT Max(Segment) FROM @t T2 WHERE T2.EMpID = T1.EmpID and T2.Segment < T1.Segment) as StartSegment
FROM @t T1
) A
LEFT OUTER JOIN @t B
ON A.EmpID = B.EmpID and A.StartSegment = B.Segment

--

For each empID and segment, get the highest segment just before it and get that segments mileage (the ending mileage). Then join in the table again, and get the mileage from the segment just before it. Subtract the ending mileage from the beginning mileage, and you are good to go. Handle the first one by replacing a NULL value with 0.

Good luck! Enjoy ....

- Jeff

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-12 : 14:24:06

I went with the COALESCE method that was described in the article. I thought I had it setup and working, but it seems to get the wrong calculation at one point. Let me give you some test data and the query I'm using to illustrate this.

 
CREATE TABLE [dbo].[tblSegments] (
	[segmentID] [int] IDENTITY (10001, 1) NOT NULL ,
	[segmentDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[segmentDistance] [decimal](3, 1) NULL ,
) ON [PRIMARY]
GO

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-100, 0)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-200, 0)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-300, 0)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-400, 0)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-500, .2)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-600, 0)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-700, .6)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-800, .3)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-900, .3)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-1000, .2)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-1100, .4)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-1200, .6)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-1300, .6)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-1400, .2)

INSERT INTO tblSegments(segmentDesc, segmentDistance)
VALUES(07/01/04-1500, .1)

Now if I run

 
select 
CAST(RIGHT(ts.segmentDesc, LEN(ts.segmentDesc) - CHARINDEX('-', ts.segmentDesc, 1)) AS int) as sort,
isNull(ts.segmentDistance, 0) as distance, 
COALESCE((SELECT SUM(isNull(segmentDistance, 0)) 
                      FROM tblSegments b 
                      WHERE CAST(RIGHT(b.segmentDesc, LEN(b.segmentDesc) - CHARINDEX('-', b.segmentDesc, 1)) AS int) < CAST(RIGHT(ts.segmentDesc, LEN(ts.segmentDesc) - CHARINDEX('-', ts.segmentDesc, 1)) AS int)) ,0) as previoussum,
isNull(ts.segmentDistance, 0)+COALESCE((SELECT SUM(isNull(segmentDistance, 0)) 
                      FROM tblSegments b 
                      WHERE CAST(RIGHT(b.segmentDesc, LEN(b.segmentDesc) - CHARINDEX('-', b.segmentDesc, 1)) AS int) < CAST(RIGHT(ts.segmentDesc, LEN(ts.segmentDesc) - CHARINDEX('-', ts.segmentDesc, 1)) AS int)) ,0)
                         AS RunningTotal
FROM tblSegments ts

All appears to be going well until we get to the row with segmentDesc='7/1/2004-1200'. The previous sum in this line is 2.1 and it should be 2.0. Can anyone see a reason why the query seems to break down at this point?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-12 : 14:43:01

Nick -- please check out my solution , I think it is what you are looking for. The answer you are trying to implement is a little over complicated.

- Jeff

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-12 : 14:46:03

I will, but I'm still interested in why this isn't working.

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-12 : 14:59:46

jsmith-

I'm not really understanding your solution. Based on the table I posted above could you clarify it for me. Specifically I'm not sure what EmpID is.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-12 : 21:50:28

I just added emp_id in case you needed to do this calculation for multiple employees or something like that, as an example. Ignore it if you like.

I believe, though, based on the data you gave, the following provides the results you requested (I took out the emp_id grouping part). And, the solution is fairly short and effective:

-----

declare @t table (Segment int, Mileage decimal(10,2))

insert into @t
select 1,.1 union
select 2,.2 union
select 3,.3 union
select 4,1.4

SELECT A.StartSegment, A.EndSegment, A.EndMiles - ISNULL(B.Mileage,0) AS Miles
FROM
(
SELECT T1.Segment as EndSegment, T1.Mileage as EndMiles,
(SELECT Max(Segment) FROM @t T2 WHERE T2.Segment < T1.Segment) as StartSegment
FROM @t T1
) A
LEFT OUTER JOIN @t B
ON A.StartSegment = B.Segment

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources