none
Carry Forward Value

    Question

  • I have 1 project which need to generate the employee turn over month by month. There is 1 column is carry forward in service employee per month from last month. How to i get this value?

    The report sample as below:

    YEAR 2013
    JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
    B/F 80 92 96 106 104 106 103 103 98 91 91 91
    In 12 8 12 2 8 0 5 0 0      
    Out 0 4 2 4 6 3 5 5 7      
    % 0.00 4.00 1.85 3.70 5.36 2.83 4.63 4.85 7.14 0.00 0.00 0.00

    Monday, September 30, 2013 1:41 PM

Answers

  • Hi,

    Pls try the below way to access previous and current row values.

    -- CTE --
    WITH CTE
         AS (SELECT ID,
                    Number,
                    ROW_NUMBER() OVER (ORDER BY ID) AS rn
             FROM   Mytable)
    SELECT ID,
           This.Number AS CurrentValue,
           Prev.Number AS PreviousValue
    FROM   CTE This
           LEFT JOIN CTE Prev
             ON Prev.rn + 1 = This.rn; 
    -- SQL SERVER 2012 --
    SELECT
       ID,
       Number AS CurrentValue,
       LAG(Number) OVER (ORDER BY ID) AS PreviousValue
    FROM
       MyTable

    - Pls mark as answer/vote, if this post is helpful

    Sugumar Pannerselvam

    Monday, September 30, 2013 2:34 PM

All replies

  • Hi Giin Sing,

    Please give us more details about your problem (e.g.: tables, columns, relations, data type, ...)

    Cheers,

    Guillaume

    Monday, September 30, 2013 2:12 PM
  • Hi,

    Pls try the below way to access previous and current row values.

    -- CTE --
    WITH CTE
         AS (SELECT ID,
                    Number,
                    ROW_NUMBER() OVER (ORDER BY ID) AS rn
             FROM   Mytable)
    SELECT ID,
           This.Number AS CurrentValue,
           Prev.Number AS PreviousValue
    FROM   CTE This
           LEFT JOIN CTE Prev
             ON Prev.rn + 1 = This.rn; 
    -- SQL SERVER 2012 --
    SELECT
       ID,
       Number AS CurrentValue,
       LAG(Number) OVER (ORDER BY ID) AS PreviousValue
    FROM
       MyTable

    - Pls mark as answer/vote, if this post is helpful

    Sugumar Pannerselvam

    Monday, September 30, 2013 2:34 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. We do not do reports in SQL. We do queries and pass that data to a report or presentation layer. This is the basis all modern programming. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 30, 2013 3:43 PM
  • Dear Guillaume,

      My doubt is how to bring forward the employee turn over from previous month if the system is running for 10 years. Should i calculate from since day one? Due to this bring forward value is start from day one.

    Tuesday, October 01, 2013 3:03 AM
  • Hi Giin,

    I’m not clear about your question after reading your posting, please post your table definition codes, sample data and your desired data here for analysis.


    Allen Li
    TechNet Community Support

    Wednesday, October 02, 2013 1:37 AM
  • Hi Allen,

      The carry forward value is carry the value from previous month. Lets make a example: 

      If the system is start from year 2010 and i would like to generate the turn over report 2013. Then did i need to compiling the carry forward value from year 2010 to year 2013. The percentage formula as below:

      Formula : [Current Month Resign] \ ([Carry Forward Value] + [Current Month New Intake]) * 10



    Wednesday, October 02, 2013 4:00 AM
  • Database as below:

    Database :

    Fields Employee No Employee Name IsResign Resign_Date Join_Date
    Data E0001 Mr. ABC 0 - 12 Jun 2010
    E0002 Ms. BCD 1 20 Jul 2011 12 Sep 2010

    Wednesday, October 02, 2013 4:00 AM
  • Report as below:

    https://www.dropbox.com/s/1glkhazatu6bn60/MSDN-A.JPG


    https://www.dropbox.com/s/mbsn18hn4c6zab9/Book1.xls
    • Edited by Giin Sing Wednesday, October 02, 2013 4:02 AM
    Wednesday, October 02, 2013 4:01 AM
  • The simple answer to your question is yes, the SQL query would need to be a running sum and would calculate this carry forward value each time from the start.

    An alternative (assuming historical data will not change) is for you to create another table to store the B/F, In and Out values by month e.g.

    year_month (YYYYMM format), start_count, joiners, leavers, end_count (computed column)

    201301, 120, 0, 5, 115

    201302, 115, 3, 0, 118

    Then each month just calculate the new stats for that month and store it in this table


    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

    Wednesday, October 02, 2013 4:04 PM
  • Hi Adam,

      Thanks. Is it any alternative way from store it in a table. I mean if the monthly data is not store in the table, is it able to plot out the data?

    Saturday, October 05, 2013 2:27 AM