Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How many users are working till specify date? Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:38 AM
Points: 16, Visits: 80
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?
Post #1566488
Posted Wednesday, April 30, 2014 10:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1566500
Posted Wednesday, April 30, 2014 10:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
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?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566501
Posted Wednesday, April 30, 2014 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:38 AM
Points: 16, Visits: 80

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)
Post #1566522
Posted Wednesday, April 30, 2014 12:19 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
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





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566541
Posted Wednesday, April 30, 2014 1:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:38 AM
Points: 16, Visits: 80
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?
Post #1566582
Posted Wednesday, April 30, 2014 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
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.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566589
Posted Thursday, May 1, 2014 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:38 AM
Points: 16, Visits: 80
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
Post #1566681
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse