April 30, 2014 at 10:14 am
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?
April 30, 2014 at 10:41 am
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.
April 30, 2014 at 10:43 am
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
Consultant - Straight Path Solutions
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
April 30, 2014 at 11:32 am
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)
April 30, 2014 at 12:19 pm
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
Consultant - Straight Path Solutions
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
April 30, 2014 at 1:34 pm
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?
April 30, 2014 at 1:44 pm
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
Consultant - Straight Path Solutions
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
May 1, 2014 at 6:06 am
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