SQL calculation based on NEXT record. Is it possible? very complex

[Coyote2]

Hi,
I have a query that calculates some sort of running_total and also another field with completed_tasks.

I need to create on the fly an addional field that substracts Completed_tasks in the NEXT record from the New_created_field of the current record. however, The first record in the new column always equals the running_total so I have a starting point.

Please sample data below:

Date running_total completed_tasks New_field
---- ------------- ---------------- ----------
9/2 367 5 367 = running
9/3 364 12 355 (367-12)
9/4 346 2 353 (355-2)
9/5 341 6 347 (353-6)

 

[bborissov]

What version of SQL Server?


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Coyote2]

Sql server 2005.
thank you.

 

[bborissov]

On top off my head:

--- Preparing test data
DECLARE @Test TABLE (Date datetime, RunTot int, ComplTask int)

INSERT INTO @Test VALUES ('20100902', 367, 5)
INSERT INTO @Test VALUES ('20100903', 364, 12)
INSERT INTO @Test VALUES ('20100904', 346, 2 )
INSERT INTO @Test VALUES ('20100905', 341, 6 )
--- End 


SELECT Test.*,
       MAX(Tst1.RunTot)              - 
       ISNULL(SUM(tst.ComplTask), 0) +
       MAX(Tst1.ComplTask) AS Test
FROM (SELECT *,
             ROW_NUMBER() OVER (/*Partition BY Date*/ ORDER BY Date)  AS RowNum
       FROM @Test) Test
LEFT JOIN (SELECT *,
             ROW_NUMBER() OVER (/*Partition BY Date*/ ORDER BY Date)  AS RowNum 
       FROM @Test) Tst ON Test.RowNum >= Tst.RowNum

INNER JOIN (SELECT *,
             ROW_NUMBER() OVER (/*Partition BY Date*/ ORDER BY Date)  AS RowNum 
       FROM @Test) Tst1 ON Tst1.RowNum = 1
GROUP BY Test.Date, Test.RunTot, Test.ComplTask, Test.RowNum

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Coyote2]

bborissov,
my dataset is dynamic and every time it runs it brings different data so I can't do Insert...

thank you.

 

[philhege]

Coyote2, bborissov was just providing a means to populate a test dataset. With a bit of adjustment, you should be able to use his code against your database, with the INSERT.

-------++NO CARRIER++-------

 

[bborissov]

Remove the code block between:
--- Preparing test data
and
--- End


Then change everywhere @Test to your table name and try it
(Of course you should adjust and column names also.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Coyote2]

bborissov,
thank you for your help. for some reason when I apply this logic to my table I get null value for the calculated field.

Please see my query below.
----------------------------------------------------
SELECT test.*,
MAX(Tst1.RUNNING_TOTAL) - ISNULL(SUM(tst.COMPLETED_COUNT), 0) + MAX(Tst1.COMPLETED_COUNT) AS Variance

FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY PERIOD_START_DATE) AS RowNum FROM TASK_MONTHLY_COUNT_TEST) TEST
LEFT JOIN (SELECT *,ROW_NUMBER() OVER (ORDER BY PERIOD_START_DATE) AS RowNum
FROM TASK_MONTHLY_COUNT_TEST) Tst ON Test.RowNum >= Tst.RowNum
INNER JOIN (SELECT *,ROW_NUMBER() OVER (ORDER BY PERIOD_START_DATE) AS RowNum
FROM TASK_MONTHLY_COUNT_TEST) Tst1 ON Tst1.RowNum = 1
GROUP BY TEST.RANK,TEST.FINISH_MONTH,TEST.BASELINE_COUNT,Test.PERIOD_START_DATE,TEST.PROJECT_ID, Test.RUNNING_TOTAL, Test.COMPLETED_COUNT, Test.RowNum


----------------------------------------
th results are correct for the RowNum but the calculated filed is Null.

any suggestions?
thanks again.

 

[bborissov]

Could you post some REAL data and desired result from it?
You have NULL in the RUNNING_TOTAL or COMPLETED_COUNT in some fields.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Coyote2]

bborissov,
please attached file for sample data.
FYI - not sure if that will make any difference but the running total is a value that I'm calculating on the fly so it's not a physical value in the database. with this in mind I even tried to insert the result into a table then do the needed calculation using your method but still didn't work. using the data sample you suggested it worked so my syntax should be correct.
thank you for your help. I'm trying to get this done now for a week....

 

[bborissov]

Coyote2,
Instead of picture could you post it here, so I can copy & paste it.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Coyote2]

let me know if that works. I can also send you an email with all my queries and results.
thank you very much
-------------------------------------------
RANK FINISH_MONTH PERIOD_START_DATE PROJ_ID BASELINE RUNNING_TOTAL COMPLETED ROWNUM VAR EXPECTED VAR
1 2009-02 2/1/2009 5067752 3 367 0 1 NULL 367
2 2009-03 3/1/2009 5067752 18 364 12 2 NULL 355
3 2009-04 4/1/2009 5067752 5 346 2 3 NULL 353
4 2009-05 5/1/2009 5067752 10 341 6 4 NULL 347
5 2009-06 6/1/2009 5067752 7 331 6 5 NULL 341
6 2009-07 7/1/2009 5067752 5 324 5 6 NULL 336
7 2009-08 8/1/2009 5067752 2 319 2 7 NULL 334
8 2009-09 9/1/2009 5067752 4 317 3 8 NULL 331
9 2009-10 10/1/2009 5067752 35 313 28 9 NULL 303
10 2009-11 11/1/2009 5067752 37 278 28 10 NULL 275
11 2009-12 12/1/2009 5067752 12 241 7 11 NULL 268
12 2010-01 1/1/2010 5067752 7 229 7 12 NULL 261
13 2010-02 2/1/2010 5067752 23 222 16 13 NULL 245
14 2010-03 3/1/2010 5067752 43 199 28 14 NULL 217
15 2010-04 4/1/2010 5067752 43 156 17 15 NULL 200
16 2010-05 5/1/2010 5067752 20 113 7 16 NULL 193
17 2010-06 6/1/2010 5067752 33 93 2 17 NULL 191
18 2010-07 7/1/2010 5067752 20 60 1 18 NULL 190
19 2010-08 8/1/2010 5067752 20 40 5 19 NULL 185
20 2010-09 9/1/2010 5067752 4 20 2 20 NULL 183
21 2010-10 10/1/2010 5067752 16 16 2 21 NULL 181
----------------------------------

 

[bborissov]

I get the right values from your data:

DECLARE @Test TABLE (RNK int, FINISH_MONTH Char(7), PERIOD_START_DATE datetime,
                     PROJ_ID int,  BASELINE int, RUNNING_TOTAL int, 
                     COMPLETED int)

INSERT INTO @Test VALUES (1 ,'2009-02','2/1/2009' ,5067752,3 ,367, 0)
INSERT INTO @Test VALUES (2 ,'2009-03','3/1/2009' ,5067752,18,364,12)
INSERT INTO @Test VALUES (3 ,'2009-04','4/1/2009' ,5067752,5 ,346,2 )
INSERT INTO @Test VALUES (4 ,'2009-05','5/1/2009' ,5067752,10,341,6 )
INSERT INTO @Test VALUES (5 ,'2009-06','6/1/2009' ,5067752,7 ,331,6 )
INSERT INTO @Test VALUES (6 ,'2009-07','7/1/2009' ,5067752,5 ,324,5 )
INSERT INTO @Test VALUES (7 ,'2009-08','8/1/2009' ,5067752,2 ,319,2 )
INSERT INTO @Test VALUES (8 ,'2009-09','9/1/2009' ,5067752,4 ,317,3 )
INSERT INTO @Test VALUES (9 ,'2009-10','10/1/2009',5067752,35,313,28)
INSERT INTO @Test VALUES (10,'2009-11','11/1/2009',5067752,37,278,28)
INSERT INTO @Test VALUES (11,'2009-12','12/1/2009',5067752,12,241,7 )
INSERT INTO @Test VALUES (12,'2010-01','1/1/2010' ,5067752,7 ,229,7 )
INSERT INTO @Test VALUES (13,'2010-02','2/1/2010' ,5067752,23,222,16)
INSERT INTO @Test VALUES (14,'2010-03','3/1/2010' ,5067752,43,199,28)
INSERT INTO @Test VALUES (15,'2010-04','4/1/2010' ,5067752,43,156,17)
INSERT INTO @Test VALUES (16,'2010-05','5/1/2010' ,5067752,20,113,7 )
INSERT INTO @Test VALUES (17,'2010-06','6/1/2010' ,5067752,33,93 ,2 )
INSERT INTO @Test VALUES (18,'2010-07','7/1/2010' ,5067752,20,60 ,1 )
INSERT INTO @Test VALUES (19,'2010-08','8/1/2010' ,5067752,20,40 ,5 )
INSERT INTO @Test VALUES (20,'2010-09','9/1/2010' ,5067752,4 ,20 ,2 )
INSERT INTO @Test VALUES (21,'2010-10','10/1/2010',5067752,16,16 ,2 )


SELECT Test.*,
       MAX(Tst1.RUNNING_TOTAL)              -
       ISNULL(SUM(tst.COMPLETED), 0) +
       MAX(Tst1.COMPLETED) AS Test
FROM (SELECT *,
             ROW_NUMBER() OVER (/*Partition BY Date*/ ORDER BY PERIOD_START_DATE)  AS RowNum
       FROM @Test) Test
LEFT JOIN (SELECT *,
             ROW_NUMBER() OVER (/*Partition BY Date*/ ORDER BY PERIOD_START_DATE)  AS RowNum
       FROM @Test) Tst ON Test.RowNum >= Tst.RowNum

INNER JOIN (SELECT *,
             ROW_NUMBER() OVER (/*Partition BY Date*/ ORDER BY PERIOD_START_DATE)  AS RowNum
       FROM @Test) Tst1 ON Tst1.RowNum = 1
GROUP BY Test.PERIOD_START_DATE, Test.RUNNING_TOTAL,
         Test.COMPLETED, Test.RowNum, Test.Rnk, Test.FINISH_MONTH,
         Test.PROJ_ID, Test.BASELINE

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Coyote2]

bborissov,
I found my problem and why it didn't work for me.
the first completed_count row was NULL and screwed up everything. I did a case statement and replaced Null with 0 and worked wonderful.

the sample data I sent you was from excel so the null became 0 and never thought about that.

thank you very much for you help.
PROBLEM SOLVED.