January 30, 2015 at 11:26 am
Hello... I need some help building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistancies with this, and i'm hoping to exclude them.. Here is a sample table:
[font="Courier New"]Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM
Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2[/font]
Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type. I hope i made some sense with this.
Thanks in Advance.
January 30, 2015 at 12:31 pm
Hi,
Try something like this:
select
Employee,
CAST([DateTime] as Date) as Date1,
MIN(case when [Function] = 1 then [DateTime] end) as InType1,
MAX(case when [Function] = 2 then [DateTime] end) as OutType1,
MIN(case when [Function] = 3 then [DateTime] end) as InType2,
MAX(case when [Function] = 4 then [DateTime] end) as OutType2
from MyTable
group by
Employee,
CAST([DateTime] as Date)
Hope this helps.
January 30, 2015 at 12:40 pm
Thank You.. However, there are instances where there may be a clock in/out cycle multiple times a day. If employee a clocks in at 8am, out at 9am, then again at 11am-12pm, this would give me 8am (min) and 12pm(max).
January 30, 2015 at 1:09 pm
rjdpa2 (1/30/2015)
Hello... I need some help building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistancies with this, and i'm hoping to exclude them.. Here is a sample table:[font="Courier New"]Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM
Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2[/font]
Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type. I hope i made some sense with this.
Thanks in Advance.
SO, how do you want the various outcomes to be handled? Without knowing how to handle all the situations you mention it is hard to help you develop a solution.
January 30, 2015 at 1:15 pm
Try:
with CTE_In as
(
select
Employee,
[Function],
[DateTime],
ROW_NUMBER() OVER(PARTITION BY Employee, [Function] ORDER BY [DateTime]) as RN
from MyTable
where
[Function] in (1, 3)
)
select
i.Employee,
i.[Function] / 2 + 1 as [Type],
i.[DateTime] as DateTimeIn,
(select top 1 o.[DateTime]
from MyTable as o
where
o.Employee = i.Employee and
o.[Function] = i.[Function] + 1 and
o.[DateTime] > i.[DateTime] and
(o.[DateTime] < ni.[DateTime] or ni.[DateTime] is null)
order by
o.[DateTime]) as DateTimeOut
from CTE_In as i
left join CTE_In as ni
on ni.Employee = i.Employee and
ni.[Function] = i.[Function] and
ni.RN = i.RN + 1
Hope this helps.
February 1, 2015 at 5:16 pm
There are various ways to do this depending on what version of SQL you're in.
Creating a Date Range from Multiple Rows Based on a Single Date[/url]
The article shows them and shows the performance of each.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 2, 2015 at 1:44 pm
CELKO (2/2/2015)
>> I need some help building a query. I have a table with 4 columns and need to try and put the times together. <<And thanks to your bad manners, we have no DDL! The ASCII picture you did post has every column wrong. The temporal model is also wrong; a (start_timestamp, end_timestamp) interval pair is never split over multiple rows. And we use ISO-8601 temporal format, not that silly local dialect you had. You used AM-PM in the 21-st century! You failed to use ISO-8601 dates!
>> There are some inconsistencies with this, and I'm hoping to exclude them. Here is a sample table: <<
So you admit to making us work with garbage data! So polite of you! This is the idiom for temporal data, I am not going to your job for you; give us the data and a spec, and we will help you.
CREATE TABLE Garbage
(emp_id CHAR(1) NOT NULL,
event_type SMALLINT NOT NULL -– bad choice of data type
CHECK (event_type IN (1,2)),
in_something_timestamp DATETIME2(0) NOT NULL,
out_something_timestamp DATETIME2(0), -- null is still active
CHECK (in_something_timestamp < out_something_timestamp)
);
>> Basically what I need to do is take the time from rows with non_relational_function_flg 1 and match it with non_relational_function_flg 2 so I can get a total time of the clock in. <<
NO! They are part of a single interval data type.
> I'm basically trying to get totals for each Clock In-Out type. I hope I made some sense with this. <<
No, but it tells us that you never read a single book on SQL, Data modeling or RDBMS. The INTERVAL data type is a fundamental concept. Please stop programming until you have the fundamental concepts; you are dangerous to your employer.
Did your boss do this to you? You can only fail. It is time to quit and update the resume.
Good grief Joe!!! Do you ever offer anything constructive or are you always the guy nobody wants around because he such an old curmudgeon???
I agree this question lacks a lot of things to help us help them but your approach lacks anything resembling help. How do you know this person created these structures? Maybe this person is pretty new and is working an older boss who mandated this is the way to do it "because they have been doing it for so long and it is the only right way".
Do you just always assume that every single person on the planet who has to interact with sql server should have the same level of experience as you do? Here's a news flash Joe, there aren't many people still alive that has been working with this technology longer than you have. With so much knowledge and expertise it is such a shame that you just irritate and put people down instead of actually trying to help them learn.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2015 at 2:24 pm
It sounds like you're trying to build some kind of a time card system, you could mess around with something like this. This isn't tested at all strongly for those exceptions you mentioned or bad data but it theoretically will handle hanging clock ins or clock outs.
CREATE TABLE #TEST
(
FUNC int,
EMP int,
CLOCK_TIME datetime
)
INSERT INTO #TEST VALUES (1, 1, '2/2/2015 9:00:00.000')
INSERT INTO #TEST VALUES (2, 1, '2/2/2015 9:30:00.000')
INSERT INTO #TEST VALUES (1, 1, '2/2/2015 10:00:00.000')
INSERT INTO #TEST VALUES (2, 1, '2/2/2015 10:15:00.000')
INSERT INTO #TEST VALUES (1, 1, '2/2/2015 10:30:00.000')
INSERT INTO #TEST VALUES (3, 2, '2/2/2015 7:00:00.000')
INSERT INTO #TEST VALUES (4, 2, '2/2/2015 8:15:00.000')
INSERT INTO #TEST VALUES (1, 2, '2/2/2015 7:00:00.000')
INSERT INTO #TEST VALUES (2, 2, '2/2/2015 8:15:00.000')
INSERT INTO #TEST VALUES (2, 3, '2/2/2015 8:15:00.000')
WITH TIME_STAMPS AS
(
SELECT FUNC, EMP, CLOCK_TIME, ROW_NUMBER() OVER(PARTITION BY EMP ORDER BY CASE WHEN FUNC = 1 OR FUNC = 2 THEN 1 ELSE 2 END, CLOCK_TIME ASC) AS ROW_NUM FROM #TEST
), TIME_CARDS AS
(
SELECT A.EMP, CASE WHEN A.FUNC = 1 THEN 1 ELSE 2 END AS CARD_TYPE, DATEDIFF(minute, A.CLOCK_TIME, B.CLOCK_TIME) AS TOTAL_TIME
FROM TIME_STAMPS A, TIME_STAMPS B
WHERE
((A.FUNC = 1 AND B.FUNC = 2) OR (A.FUNC = 3 AND B.FUNC = 4)) AND A.ROW_NUM = B.ROW_NUM - 1
AND A.EMP = B.EMP
)
SELECT CARD_TYPE, SUM(TOTAL_TIME) FROM TIME_CARDS GROUP BY CARD_TYPE
February 2, 2015 at 4:08 pm
CELKO (2/2/2015)
>> I need some help building a query. I have a table with 4 columns and need to try and put the times together. <<And thanks to your bad manners, we have no DDL! The ASCII picture you did post has every column wrong. The temporal model is also wrong; a (start_timestamp, end_timestamp) interval pair is never split over multiple rows. And we use ISO-8601 temporal format, not that silly local dialect you had. You used AM-PM in the 21-st century! You failed to use ISO-8601 dates!
>> There are some inconsistencies with this, and I'm hoping to exclude them. Here is a sample table: <<
So you admit to making us work with garbage data! So polite of you! This is the idiom for temporal data, I am not going to your job for you; give us the data and a spec, and we will help you.
CREATE TABLE Garbage
(emp_id CHAR(1) NOT NULL,
event_type SMALLINT NOT NULL -– bad choice of data type
CHECK (event_type IN (1,2)),
in_something_timestamp DATETIME2(0) NOT NULL,
out_something_timestamp DATETIME2(0), -- null is still active
CHECK (in_something_timestamp < out_something_timestamp)
);
>> Basically what I need to do is take the time from rows with non_relational_function_flg 1 and match it with non_relational_function_flg 2 so I can get a total time of the clock in. <<
NO! They are part of a single interval data type.
> I'm basically trying to get totals for each Clock In-Out type. I hope I made some sense with this. <<
No, but it tells us that you never read a single book on SQL, Data modeling or RDBMS. The INTERVAL data type is a fundamental concept. Please stop programming until you have the fundamental concepts; you are dangerous to your employer.
Did your boss do this to you? You can only fail. It is time to quit and update the resume.
Are you a Seahawks fan? What else could drive such gratuitous vitriol?
Don Simpson
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply