July 23, 2009 at 1:41 pm
TAman (7/23/2009)
Can anyone send me code that would get me my expected result above based on the data below?
Heh... oh yeah... someone will post code that will solve that... we just need to know the rules for sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2009 at 1:56 pm
I sent the rules... Here they are again
Detail:::: If the file name is the same as 'any' other file name, AND it's match has the same Emp ID, AND 'any' start entries (date/time) of those matching file name(s) are within 60 minutes of 'any' of those start entries then show result.
July 23, 2009 at 2:25 pm
Ok, let's try this on for size. It includes sample data. Not sure if this works 100%, so you'll need a lot more data to test it with, but it produces your modified result.
DECLARE @LOG_TABLE AS TABLE(
Emp_ID int,
Start_Entry DateTime,
End_Entry DateTime,
FName varchar(5)
PRIMARY KEY CLUSTERED (Emp_ID, FName, Start_Entry)
)
INSERT INTO @LOG_TABLE
SELECT 12345, '2009-05-04 02:31:00', '2009-05-04 02:32:00', '1' UNION ALL
SELECT 12345, '2009-05-04 02:37:00', '2009-05-04 02:38:00', '1' UNION ALL
SELECT 12345, '2009-05-04 03:36:00', '2009-05-04 03:37:00', '1' UNION ALL
SELECT 45321, '2009-05-04 02:30:00', '2009-05-04 02:33:00', '33' UNION ALL
SELECT 78989, '2009-05-04 02:37:00', '2009-05-04 02:39:00', '2'
SELECT L1.Emp_ID, CAST(MIN(L1.Start_Entry) AS varchar(11)) AS Start_Entry, L1.FName, COUNT(*) AS ENTRY_COUNT
FROM @LOG_TABLE AS L1 INNER JOIN @LOG_TABLE AS L2
ON L1.Emp_ID = L2.Emp_ID AND L1.FName = L2.FName AND L1.Start_Entry < L2.Start_Entry
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60
GROUP BY L1.Emp_ID, L1.FName
ORDER BY L1.Emp_ID, L1.FName, MIN(L1.Start_Entry)
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2009 at 2:31 pm
Had an oops in the code, updated mm to mi in the DATEDIFF function.
Steve
(aka smunson)
:-D:-D:-D
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2009 at 2:34 pm
Oops again! Here's the updated code. If you want to see a count for each record, we have to add 1 to the count in the group. I also updated the sample data to allow for showing how that works.
DECLARE @LOG_TABLE AS TABLE(
Emp_ID int,
Start_Entry DateTime,
End_Entry DateTime,
FName varchar(5)
PRIMARY KEY CLUSTERED (Emp_ID, FName, Start_Entry)
)
INSERT INTO @LOG_TABLE
SELECT 12345, '2009-05-04 02:31:00', '2009-05-04 02:32:00', '1' UNION ALL
SELECT 12345, '2009-05-04 02:37:00', '2009-05-04 02:38:00', '1' UNION ALL
SELECT 12345, '2009-05-04 03:36:00', '2009-05-04 03:37:00', '1' UNION ALL
SELECT 45321, '2009-05-04 02:30:00', '2009-05-04 02:33:00', '33' UNION ALL
SELECT 45321, '2009-05-04 03:20:00', '2009-05-04 03:26:00', '33' UNION ALL
SELECT 78989, '2009-05-04 02:37:00', '2009-05-04 02:39:00', '2'
SELECT L1.Emp_ID, CAST(MIN(L1.Start_Entry) AS varchar(11)) AS Start_Entry, L1.FName, COUNT(*) + 1 AS ENTRY_COUNT
FROM @LOG_TABLE AS L1 INNER JOIN @LOG_TABLE AS L2
ON L1.Emp_ID = L2.Emp_ID AND L1.FName = L2.FName AND L1.Start_Entry < L2.Start_Entry
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60
GROUP BY L1.Emp_ID, L1.FName
ORDER BY L1.Emp_ID, L1.FName, MIN(L1.Start_Entry)
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2009 at 2:57 pm
I think this is going to actually work! yeah! Although my table contains dates ranging from 1/1/09 to Current 7/23/09. If I want to narrow the range of data I want to look at, would I be correct in my statement below?
SELECT L1.Emp_ID, CAST(MIN(L1.Start_Entry) AS varchar(11)) AS Start_Entry, L1.FName, COUNT(*) + 1 AS ENTRY_COUNT
FROM @LOG_TABLE AS L1 INNER JOIN @LOG_TABLE AS L2
ON L1.Emp_ID = L2.Emp_ID AND L1.FName = L2.FName AND L1.Start_Entry < L2.Start_Entry
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60 AND Start_Entry between '@Date' AND '@Date'
GROUP BY L1.Emp_ID, L1.FName
ORDER BY L1.Emp_ID, L1.FName, MIN(L1.Start_Entry)
July 23, 2009 at 3:05 pm
TAman (7/23/2009)
I think this is going to actually work! yeah! Although my table contains dates ranging from 1/1/09 to Current 7/23/09. If I want to narrow the range of data I want to look at, would I be correct in my statement below?
SELECT L1.Emp_ID, CAST(MIN(L1.Start_Entry) AS varchar(11)) AS Start_Entry, L1.FName, COUNT(*) + 1 AS ENTRY_COUNTFROM @LOG_TABLE AS L1 INNER JOIN @LOG_TABLE AS L2
ON L1.Emp_ID = L2.Emp_ID AND L1.FName = L2.FName AND L1.Start_Entry < L2.Start_Entry
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60 AND Start_Entry between '@Date' AND '@Date'
GROUP BY L1.Emp_ID, L1.FName
ORDER BY L1.Emp_ID, L1.FName, MIN(L1.Start_Entry)
BETWEEN @Date and @Date -- ??
July 23, 2009 at 6:27 pm
As long as you substitute actual date text for the @Date you have between the quote signs, you'll be ok. @Date would NOT be interpreted as a declared variable if contained within quotes, so that was what Lynn Pettis was reacting to. @ is a delimiter for SQL indicating a variable, but once you put single quotes in front of it, it's text, like anything else between quotes (save additional quotes).
Let us know how this turns out.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2009 at 7:17 am
I think we are really close, but when I add a date range in my WHERE clause(see below)
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60 AND Start_Entry BETWEEN '07/01/09' AND '07/23/09'
I get this error.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name Start_Entry.
Is this because it is looking for (11) characters in length? Just a guess....
How should the code look if I want a date range BETWEEN 07/01/09 and 07/23/09?
July 24, 2009 at 7:26 am
See the changes to your code: All I did was add the table alias, as otherwise there would be two possible fields that SQL Server would have to pick from (there are two identical tables in the FROM clause), and SQL Server isn't going to choose which one until you specify, which is why you got the "Ambiguous column name" error.
Steve
(aka smunson)
:-):-):-)
TAman (7/24/2009)
I think we are really close, but when I add a date range in my WHERE clause(see below)
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60 AND L1.Start_Entry BETWEEN '07/01/09' AND '07/23/09'I get this error.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name Start_Entry.
Is this because it is looking for (11) characters in length? Just a guess....
How should the code look if I want a date range BETWEEN 07/01/09 and 07/23/09?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2009 at 7:27 am
TAman (7/24/2009)
I think we are really close, but when I add a date range in my WHERE clause(see below)
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60 AND Start_Entry BETWEEN '07/01/09' AND '07/23/09'I get this error.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name Start_Entry.
Is this because it is looking for (11) characters in length? Just a guess....
How should the code look if I want a date range BETWEEN 07/01/09 and 07/23/09?
The answer is right there in that line of code. Which Start_Entry column do you want to use, L1.Start_Entry or L2.Start_Entry? The Query Optimizer doesn't know which one you want.
July 24, 2009 at 1:35 pm
This works great!!! although now it has been requested that I get detailed results. So now I am trying to get a different looking result on my report.
Using the same logic as before, just need to show detail.
Here is the table data below that I am working with now. It is the same format as before, just different dates.
Emp_ID Start_Entry End_Entry File_Name
12345 2009-07-08 12:31:00 2009-07-08 12:35:00 1
12345 2009-07-08 12:37:00 2009-07-08 12:39:00 1
12345 2009-07-08 14:19:00 2009-07-08 14:22:00 2
22222 2009-07-09 02:33:00 2009-07-09 02:36:00 6
22222 2009-07-09 02:39:00 2009-07-09 02:40:00 6
45321 2009-07-12 03:36:00 2009-07-12 03:36:00 33
45321 2009-07-12 04:01:00 2009-07-12 04:01:00 33
78989 2009-07-22 02:37:00 2009-07-22 02:37:00 2
I need my expected results to show like this (see below)
Emp_ID Start_Entry File_Name
12345 2009-07-08 12:31:00 1
12345 2009-07-08 12:37:00 1
22222 2009-07-09 02:33:00 6
22222 2009-07-09 02:39:00 6
45321 2009-07-12 03:36:00 33
45321 2009-07-12 04:01:00 33
Here is the code I'm using, but I'm not getting my expected result. I changed the varchar to smalldatetime thinking the result would be the entire date/time. That didn't work so I removed the CAST altogether and added L1.Start_Entry in the SELECT. I know this is probably really something simple, but I'm not seeing it. HELP!!
SELECT L1.Emp_ID, L1.Start_Entry, L1.FName
FROM @LOG_TABLE AS L1 INNER JOIN @LOG_TABLE AS L2
ON L1.Emp_ID = L2.Emp_ID AND L1.FName = L2.FName AND L1.Start_Entry < L2.Start_Entry
WHERE DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry) < 60
GROUP BY L1.Emp_ID, L1.FName
ORDER BY L1.Emp_ID, L1.FName, MIN(L1.Start_Entry)
July 24, 2009 at 2:12 pm
Ever try to hit a thrown pork chop in mid air? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2009 at 2:26 pm
Now we're dealing with an entirely new problem. By abandoning the count, we can't use the GROUP BY quite the same way, so it's not a simple change to the query, as you've fundamentally changed what kind of result you're looking for. Now we have to identify all the records that participate in an "occurrence", so give me a few minutes to see what I can come up with.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2009 at 2:36 pm
Ok, here's the quick version. It's not exactly what you asked for, but it may more clearly represent your "occurrences":
DECLARE @LOG_TABLE AS TABLE(
RECNUM int IDENTITY(1,1),
Emp_ID int,
Start_Entry DateTime,
End_Entry DateTime,
FName varchar(5)
PRIMARY KEY CLUSTERED (Emp_ID, FName, Start_Entry)
)
INSERT INTO @LOG_TABLE(Emp_ID, Start_Entry, End_Entry, FName)
SELECT 12345, '2009-07-08 12:31:00', '2009-07-08 12:35:00', '1' UNION ALL
SELECT 12345, '2009-07-08 12:37:00', '2009-07-08 12:39:00', '1' UNION ALL
SELECT 12345, '2009-07-08 14:19:00', '2009-07-08 14:22:00', '2' UNION ALL
SELECT 22222, '2009-07-09 02:33:00', '2009-07-09 02:36:00', '6' UNION ALL
SELECT 22222, '2009-07-09 02:39:00', '2009-07-09 02:40:00', '6' UNION ALL
SELECT 45321, '2009-07-12 03:36:00', '2009-07-12 03:36:00', '33' UNION ALL
SELECT 45321, '2009-07-12 04:01:00', '2009-07-12 04:01:00', '33' UNION ALL
SELECT 78989, '2009-07-22 02:37:00', '2009-07-22 02:37:00', '2'
SELECT L1.Emp_ID, L1.Start_Entry, L1.FName, L2.Start_Entry AS Corresponding_Start_Entry
FROM @LOG_TABLE AS L1 INNER JOIN @LOG_TABLE AS L2
ON L1.Emp_ID = L2.Emp_ID AND L1.FName = L2.FName AND L1.Start_Entry < L2.Start_Entry
WHERE ABS(DATEDIFF(mi,L1.Start_Entry,L2.Start_Entry)) < 60
ORDER BY L1.Emp_ID, L1.FName, L1.Start_Entry
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply