How many users are working till specify date?

  • Helllo,

    Can somebody help me? I have a table with 5000 rows history about working and retired time.

    It is the serveral rows from table(User, StartDate, EndDate):

    User1 2011-05-09 00:00 2014-01-17 00:00

    User2 2012-07-01 00:00 2012-08-20 00:00

    User2 2013-08-26 00:00 2013-09-02 00:00

    User2 2013-10-07 00:00 NULL

    User3 2013-09-01 00:00 2014-04-07 00:00

    User3 2014-04-08 00:00 NULL

    How many users have worked and have retired by years and months,

    Example:

    2011-01 working 2000

    2011-02 retired -50

    2011-02 working 1950

    2011-02 retired -27

    and etc,,

    Does it need to join to a time dimension table?

  • mindaugas 67490 (4/30/2014)


    Helllo,

    Can somebody help me? I have a table with 5000 rows history about working and retired time.

    It is the serveral rows from table(User, StartDate, EndDate):

    User1 2011-05-09 00:00 2014-01-17 00:00

    User2 2012-07-01 00:00 2012-08-20 00:00

    User2 2013-08-26 00:00 2013-09-02 00:00

    User2 2013-10-07 00:00 NULL

    User3 2013-09-01 00:00 2014-04-07 00:00

    User3 2014-04-08 00:00 NULL

    How many users have worked and have retired by years and months,

    Example:

    2011-01 working 2000

    2011-02 retired -50

    2011-02 working 1950

    2011-02 retired -27

    and etc,,

    Does it need to join to a time dimension table?

    I may be missing something but I don't see the relationship between the data and the expected results as posted.

  • I think joining to a date table would probably be the easiest way to solve the problem.

    A couple of questions:

    What do you mean by retired time?

    Is the end date considered a working day? For example would User1 be considered working or retired on 2014-01-17 00:00?

  • I thinking too about a data table, but I haven't thought how do it.

    User - It is employee of company

    Retired date - the last working day in company

    working day - It is time between startdate(the first working day in company) and enddate(last working day in company)

    Example for the first row - User1 2011-05-09 00:00 2014-01-17 00:00

    I want get it:

    2011-05-09 Working 1(users)

    2011-05-09 Retired 0(users, because he is working)

    2011-05-10 Working 1(users)

    2011-05-10 Retired 0(users, because he is working)

    .

    .

    2014-01-17 Working 1(users)

    2014-01-17 Retired 0(users, because he is working)

    2014-01-18 Working 0(users)

    2014-01-18 Retired -1 or 1(users, because he has retired)

  • I doubt this is exactly what you are looking for, but I think it'll get you moving it the right direction.

    USE test;

    GO

    DECLARE @workers TABLE

    (

    name VARCHAR(10),

    StartDate DATE,

    EndDate DATE

    );

    DECLARE @dates TABLE

    (

    theDate DATE,

    theYear SMALLINT,

    theMonth TINYINT,

    theDay TINYINT

    );

    INSERT INTO @workers

    (name, StartDate, EndDate)

    VALUES

    ('User1', '2011-05-09 00:00', '2014-01-17 00:00'),

    ('User2 ', '2012-07-01 00:00', '2012-08-20 00:00'),

    ('User3 ', '2013-08-26 00:00', '2013-09-02 00:00'),

    ('User4 ', '2013-10-07 00:00', NULL),

    ('User5 ', '2013-09-01 00:00', '2014-04-07 00:00'),

    ('User6 ', '2014-04-08 00:00', NULL);

    INSERT INTO @dates

    (

    theDate,

    theYear,

    theMonth,

    theDay

    )

    SELECT TOP 20000

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (

    SELECT

    NULL

    )) - 1, '19700101') AS theDate,

    DATEPART(YEAR,

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (

    SELECT

    NULL

    )) - 1,

    '19700101')) AS theYear,

    DATEPART(MONTH,

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (

    SELECT

    NULL

    )) - 1,

    '19700101')) AS theMonth,

    DATEPART(DAY,

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (

    SELECT

    NULL

    )) - 1,

    '19700101')) AS theDay

    FROM

    sys.all_objects AS AC

    CROSS JOIN sys.all_objects AS AO

    SELECT

    D.theYear,

    D.theMonth,

    D.theDay,

    SUM(CASE WHEN D.theDate > W.EndDate THEN 1

    ELSE 0

    END) AS retired,

    SUM(CASE WHEN D.theDate BETWEEN W.StartDate

    AND ISNULL(W.EndDate, CURRENT_TIMESTAMP)

    THEN 1

    ELSE 0

    END) AS working

    FROM

    @workers AS W

    CROSS JOIN @dates AS D

    WHERE

    D.theDate < CURRENT_TIMESTAMP

    GROUP BY

    D.theYear,

    D.theMonth,

    D.theDay

  • Nice Job for working.

    Yes I have done, but my query is working long, about 1minute, you one second 🙂

    Talking about retired people some incorrect. I will try it, if I have it

    Time Working Retirede

    2014-04-28 885 883-885=-2

    2014-04-29 883 888-883=+5

    2014-04-30 888 NULL

    I am thing about subquery and i can't use lag function, becaue my sql server 2008 🙁

    Maybe do you have any idea instead lag function for it result?

  • If you could set up some test data like I did and provide the expected results form the test data someone may come up with an answer. Right now there aren't enough details for anyone to come up with exactly what you need. Please read the article in the first link in my signature.

  • Thank you very much!

    I have added insteed retired this:

    SUM(CASE WHEN D.Date BETWEEN ISNULL(W.StartDate,'1970-01-01')

    AND ISNULL(W.EndDate, getdate())

    THEN 1

    ELSE 0

    END)-sum(CASE WHEN D.Date BETWEEN ISNULL(W.StartDate,'1970-01-01')-1

    AND ISNULL(W.EndDate, getdate())-1

    THEN 1

    ELSE 0

    END)as changed

Viewing 8 posts - 1 through 7 (of 7 total)

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