Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: How do I get the previous row from one column add the next one from another column

    How do I get the previous row from one column add the next one from another column and record the result so I can get this result.

    Bi_date Total_Pay_Period Total_monthly
    1 2 2
    2 2 4
    3 2 6
    4 2 8
    5 3 11

    this is my code so far:

    drop table ##dates
    create table ##dates (Bi_Date datetime);
    with cte as
    (
    select Date = cast('2013-10-04' as datetime) -- first friday in 09
    union all
    select dateadd(day,14,Date) from cte
    where dateadd(day,14,Date) < '2014-09-30'
    )
    insert into ##dates(Bi_Date)
    select Date
    from cte
    OPTION(MAXRECURSION 0)


    drop table ##bi_month
    create table ##bi_month(Bi_date int, Total_Pay_Period int,Total_monthly int)
    insert into ##bi_month(Bi_date, Total_Pay_Period, Total_monthly)
    SELECT datepart(MONTH,Bi_Date)Bi_month , COUNT(*) TotalCount, null
    FROM ##dates
    GROUP BY datepart(MONTH,Bi_Date)
    HAVING COUNT(*) > 1
    ORDER by datepart(mm,Bi_Date)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on your example, it looks like you are trying to build a running total.

    Your sample code seems to be doing something different, but I'm not quite sure what it is supposed to do.

    It would help me if you disregard SQL for the moment, and focus on your input data, your output result set, and the rules that get from input to output.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    there is no user input I am calculating the pay periods between to given dates in my case for 12 month based on a biweekly schedule.

    My ##bi_month temp table gives me the following result
    Bi_date Total_Pay_Period Total_monthly
    1 2 NULL
    2 2 NULL
    3 2 NULL
    4 2 NULL
    5 3 NULL
    6 2 NULL
    7 2 NULL
    . . .
    . . .

    What I need to do is update my Total_monthly column to be the running total of my Total_Pay_Period column


    Bi_date Total_Pay_Period Total_monthly
    1 2 2
    2 2 4
    3 2 6
    4 2 8
    5 3 11
    6 2 13
    7 2 15
    . . .
    . . .

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see
    C. Producing a moving average and cumulative total
    and
    D. Specifying the ROWS clause

    in OVER Clause (Transact-SQL)

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're still missing a lot of important details, but for what you've posted so far:
    Code:
    DECLARE @t TABLE (
       BI_Date          INT
    ,  Total_Pay_Period INT
       )
    
    INSERT INTO @t (BI_Date, Total_Pay_Period)
       VALUES  (1, 2)
    ,  (2, 2), (3, 2)
    ,  (4, 2), (5, 3)
    ,  (6, 2), (7, 2)
    
    SELECT BI_Date, Total_Pay_Period
    ,  Sum(Total_Pay_Period) OVER (ORDER BY BI_DATE) AS Total_Monthly
       FROM @t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2013
    Posts
    46
    Thanks Pat, bit I am working on SQL serer 2008r and Over is for 2012

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •