Post reply

SQL 2000 Running Total Reset

  • May 25, 2010 at 6:48 am

    #74292

    I am having a problem resetting the running total calculation in SQL 2000 when an employee has over 34 hours time off. The last column was manually added and represents what the running total should be. For example, Emp 1 on 4/13/10 should be 34,560, i.e. reset the running total to the current work time since the employee had 39 hours off and respresented by the ResetRow = 1. The SQL is adding all of the "0's" and the "1's" together for a specific employee. Anyone have any ideas on how to prevent this from happening.

    --====Create test table

    CREATE Table [ESIDW].[dbo].[Z_VW_DOT]

    (PERSONFULLNAME varchar(64),

    PERSONNUM varchar(15),

    EVENTDATE datetime,

    STARTDTM datetime,

    ENDDTM datetime,

    HoursWorked decimal(8,2),

    PrevEVENTDATE datetime,

    PrevSTARTDTM datetime,

    PrevENDDTM datetime,

    TIMEINSECONDS int,

    PrevTime int,

    TimeOff decimal(8,2),

    LABORLEVELNAME4 varchar(50),

    ResetRow varchar(1),

    Hr10Rule varchar(1),

    Hr14Rule varchar(1)

    )

    --========== Sample Data

    INSERT INTO Z_VW_DOT

    (PERSONFULLNAME, PERSONNUM,EVENTDATE, STARTDTM, ENDDTM, HoursWorked, PrevEVENTDATE, PrevSTARTDTM, PrevENDDTM,TIMEINSECONDS,

    PrevTime, TimeOff, LABORLEVELNAME4, ResetRow, Hr10Rule, Hr14Rule)

    SELECT 'EMP 1','7Z90000885','Apr 1 2010 12:00AM','Apr 1 2010 4:34AM','Apr 1 2010 1:46PM','8.00','Mar 31 2010 12:00AM','Mar 31 2010 3:42AM','Mar 31 2010 2:00PM','31320','35280','14.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 2 2010 12:00AM','Apr 2 2010 5:06AM','Apr 2 2010 1:30PM','7.00','Apr 1 2010 12:00AM','Apr 1 2010 4:34AM','Apr 1 2010 1:46PM','28440','31320','15.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 3 2010 12:00AM','Apr 3 2010 4:16AM','Apr 3 2010 10:07AM','5.00','Apr 2 2010 12:00AM','Apr 2 2010 5:06AM','Apr 2 2010 1:30PM','19260','28440','14.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 5 2010 12:00AM','Apr 5 2010 4:11AM','Apr 5 2010 1:52PM','9.00','Apr 3 2010 12:00AM','Apr 3 2010 4:16AM','Apr 3 2010 10:07AM','33060','19260','42.00','A','1','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 6 2010 12:00AM','Apr 6 2010 3:37AM','Apr 6 2010 12:58PM','8.00','Apr 5 2010 12:00AM','Apr 5 2010 4:11AM','Apr 5 2010 1:52PM','31860','33060','13.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 7 2010 12:00AM','Apr 7 2010 3:23AM','Apr 7 2010 1:23PM','9.00','Apr 6 2010 12:00AM','Apr 6 2010 3:37AM','Apr 6 2010 12:58PM','34200','31860','14.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 8 2010 12:00AM','Apr 8 2010 4:13AM','Apr 8 2010 1:13PM','8.00','Apr 7 2010 12:00AM','Apr 7 2010 3:23AM','Apr 7 2010 1:23PM','30600','34200','14.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 9 2010 12:00AM','Apr 9 2010 4:25AM','Apr 9 2010 2:00PM','9.00','Apr 8 2010 12:00AM','Apr 8 2010 4:13AM','Apr 8 2010 1:13PM','32700','30600','15.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 10 2010 12:00AM','Apr 10 2010 4:35AM','Apr 10 2010 12:19PM','7.00','Apr 9 2010 12:00AM','Apr 9 2010 4:25AM','Apr 9 2010 2:00PM','26040','32700','14.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 12 2010 12:00AM','Apr 12 2010 3:55AM','Apr 12 2010 1:19PM','8.00','Apr 10 2010 12:00AM','Apr 10 2010 4:35AM','Apr 10 2010 12:19PM','32040','26040','39.00','A','1','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 13 2010 12:00AM','Apr 13 2010 3:18AM','Apr 13 2010 1:24PM','9.00','Apr 12 2010 12:00AM','Apr 12 2010 3:55AM','Apr 12 2010 1:19PM','34560','32040','13.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 14 2010 12:00AM','Apr 14 2010 3:10AM','Apr 14 2010 1:06PM','9.00','Apr 13 2010 12:00AM','Apr 13 2010 3:18AM','Apr 13 2010 1:24PM','33960','34560','13.00','A','0','0','0' UNION ALL

    SELECT 'EMP 1','7Z90000885','Apr 15 2010 12:00AM','Apr 15 2010 3:02AM','Apr 15 2010 2:20PM','10.00','Apr 14 2010 12:00AM','Apr 14 2010 3:10AM','Apr 14 2010 1:06PM','38880','33960','13.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 1 2010 12:00AM','Apr 1 2010 3:01AM','Apr 1 2010 2:52PM','11.00','Mar 31 2010 12:00AM','Mar 31 2010 2:26AM','Mar 31 2010 1:47PM','40860','39060','13.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 2 2010 12:00AM','Apr 2 2010 3:07AM','Apr 2 2010 2:26PM','10.00','Apr 1 2010 12:00AM','Apr 1 2010 3:01AM','Apr 1 2010 2:52PM','38940','40860','12.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 5 2010 12:00AM','Apr 5 2010 3:07AM','Apr 5 2010 5:24PM','13.00','Apr 2 2010 12:00AM','Apr 2 2010 3:07AM','Apr 2 2010 2:26PM','49620','38940','60.00','A','1','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 6 2010 12:00AM','Apr 6 2010 3:08AM','Apr 6 2010 2:38PM','11.00','Apr 5 2010 12:00AM','Apr 5 2010 3:07AM','Apr 5 2010 5:24PM','39600','49620','9.00','A','0','1','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 7 2010 12:00AM','Apr 7 2010 2:25AM','Apr 7 2010 3:11PM','12.00','Apr 6 2010 12:00AM','Apr 6 2010 3:08AM','Apr 6 2010 2:38PM','44160','39600','11.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 8 2010 12:00AM','Apr 8 2010 3:05AM','Apr 8 2010 2:49PM','11.00','Apr 7 2010 12:00AM','Apr 7 2010 2:25AM','Apr 7 2010 3:11PM','40440','44160','11.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 9 2010 12:00AM','Apr 9 2010 3:09AM','Apr 9 2010 2:37PM','10.00','Apr 8 2010 12:00AM','Apr 8 2010 3:05AM','Apr 8 2010 2:49PM','39480','40440','12.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 12 2010 12:00AM','Apr 12 2010 5:22AM','Apr 12 2010 6:09PM','12.00','Apr 9 2010 12:00AM','Apr 9 2010 3:09AM','Apr 9 2010 2:37PM','44220','39480','62.00','A','1','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 13 2010 12:00AM','Apr 13 2010 3:25AM','Apr 13 2010 3:59PM','12.00','Apr 12 2010 12:00AM','Apr 12 2010 5:22AM','Apr 12 2010 6:09PM','43440','44220','9.00','A','0','1','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 14 2010 12:00AM','Apr 14 2010 2:56AM','Apr 14 2010 2:13PM','10.00','Apr 13 2010 12:00AM','Apr 13 2010 3:25AM','Apr 13 2010 3:59PM','38820','43440','10.00','A','0','0','0' UNION ALL

    SELECT 'EMP 3','7Z90052634','Apr 15 2010 12:00AM','Apr 15 2010 3:28AM','Apr 15 2010 2:48PM','10.00','Apr 14 2010 12:00AM','Apr 14 2010 2:56AM','Apr 14 2010 2:13PM','39000','38820','13.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 1 2010 12:00AM','Apr 1 2010 3:14AM','Apr 1 2010 5:07PM','13.00','Mar 31 2010 12:00AM','Mar 31 2010 4:11AM','Mar 31 2010 4:48PM','48180','43620','10.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 2 2010 12:00AM','Apr 2 2010 3:11AM','Apr 2 2010 12:48PM','9.00','Apr 1 2010 12:00AM','Apr 1 2010 3:14AM','Apr 1 2010 5:07PM','32820','48180','10.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 5 2010 12:00AM','Apr 5 2010 3:06AM','Apr 5 2010 4:42PM','13.00','Apr 2 2010 12:00AM','Apr 2 2010 3:11AM','Apr 2 2010 12:48PM','47160','32820','62.00','A','1','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 6 2010 12:00AM','Apr 6 2010 3:10AM','Apr 6 2010 5:03PM','13.00','Apr 5 2010 12:00AM','Apr 5 2010 3:06AM','Apr 5 2010 4:42PM','48180','47160','10.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 8 2010 12:00AM','Apr 8 2010 3:18AM','Apr 8 2010 5:48PM','14.00','Apr 6 2010 12:00AM','Apr 6 2010 3:10AM','Apr 6 2010 5:03PM','50400','48180','34.00','A','1','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 9 2010 12:00AM','Apr 9 2010 3:06AM','Apr 9 2010 4:05PM','12.00','Apr 8 2010 12:00AM','Apr 8 2010 3:18AM','Apr 8 2010 5:48PM','44940','50400','9.00','A','0','1','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 10 2010 12:00AM','Apr 10 2010 3:57AM','Apr 10 2010 9:35AM','5.00','Apr 9 2010 12:00AM','Apr 9 2010 3:06AM','Apr 9 2010 4:05PM','18480','44940','11.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 12 2010 12:00AM','Apr 12 2010 3:37AM','Apr 12 2010 4:08PM','12.00','Apr 10 2010 12:00AM','Apr 10 2010 3:57AM','Apr 10 2010 9:35AM','43260','18480','42.00','A','1','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 13 2010 12:00AM','Apr 13 2010 3:16AM','Apr 13 2010 5:02PM','13.00','Apr 12 2010 12:00AM','Apr 12 2010 3:37AM','Apr 12 2010 4:08PM','47760','43260','11.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 14 2010 12:00AM','Apr 14 2010 3:05AM','Apr 14 2010 3:49PM','12.00','Apr 13 2010 12:00AM','Apr 13 2010 3:16AM','Apr 13 2010 5:02PM','44040','47760','10.00','A','0','0','0' UNION ALL

    SELECT 'EMP 2','7Z90005375','Apr 15 2010 12:00AM','Apr 15 2010 3:14AM','Apr 15 2010 3:37PM','11.00','Apr 14 2010 12:00AM','Apr 14 2010 3:05AM','Apr 14 2010 3:49PM','42780','44040','11.00','A','0','0','0'

    --======== Select Statement

    SELECT vpt1.PERSONNUM, vpt1.STARTDTM, vpt1.PrevENDDTM, vpt1.TIMEINSECONDS, vpt1.PrevTime,

    vpt1.TimeOff, Vpt1.resetrow,

    (select sum(vpt0.timeinseconds) from z_vw_DOT vpt0

    where vpt0.personnum = vpt1.personnum

    and vpt0.startdtm <= vpt1.startdtm

    and vpt0.ResetRow = vpt1.ResetRow

    and vpt0.startdtm BETWEEN '4/8/2010' AND '4/14/2010'

    ) as RunningTotal

    FROM z_vw_DOT vpt1

    where

    vpt1.STARTDTM BETWEEN '4/8/2010' AND '4/14/2010'

    AND vpt1.LABORLEVELNAME4 ='A'

    order by vpt1.PERSONNUM, vpt1.STARTDTM

    Results:

    PSNUM STARTDTMPrevEndDTMSecondsPrevTime TimeOff ResetRowRunningTotalShould Be

    Emp 14/8/10 4:134/7/10 13:2330600342001403060030600

    Emp 14/9/10 4:254/8/10 13:1332700306001506330063300

    Emp 14/10/10 4:354/9/10 14:0026040327001408934089340

    Emp 14/12/10 3:554/10/10 12:1932040260403913204032040

    Emp 14/13/10 3:184/12/10 13:19345603204013012390034560

    Emp 24/8/10 3:184/6/10 17:0350400481803415040050400

    Emp 24/9/10 3:064/8/10 17:484494050400904494044940

    Emp 24/10/10 3:574/9/10 16:0518480449401106342089880

    Emp 24/12/10 3:374/10/10 9:3543260184804219366043260

    Emp 24/13/10 3:164/12/10 16:08477604326011011118047760

    Emp 34/9/10 3:324/7/10 15:1340020405603614002040020

    Emp 34/10/10 3:264/9/10 15:0929940400201202994029940

    Emp 34/12/10 3:304/10/10 12:1543080299403918310043080

    Emp 34/13/10 3:354/12/10 15:5838640430801106858038640

  • May 25, 2010 at 7:36 am

    #1172061

    Please refer to the article in my signature on how to post sample data.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • May 25, 2010 at 3:10 pm

    #1172368

    I have added the scripts to create the table and populate with sample data. Thank you.

  • May 25, 2010 at 8:23 pm

    #1172439

    You might want to check out Jeff Moden's excellent article on Running Totals[/url].

    After doing this, if you have any more problems, come on back!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 26, 2010 at 6:36 am

    #1172655

    Wayne,

    Quick question. Is is possible to calculate the running total in Jeff's script without updating a table, i.e. in a select statement? This is a 3rd party vendors database so I am creating a view that can be used for reporting.

  • July 3, 2010 at 6:04 pm

    #1188587

    TKyle (5/26/2010)

    Wayne,

    Quick question. Is is possible to calculate the running total in Jeff's script without updating a table, i.e. in a select statement? This is a 3rd party vendors database so I am creating a view that can be used for reporting.

    Sorry no one got back to you on this. The answer is "No"... not directly. However, you can quickly copy the data to a temp table using SELECT/INTO, add the correct index to that, do the update, do the select, and still be way ahead of the game compared to any of the other methods for doing running totals.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply