Extra Records

Question

Extra Records

lanky_doodle

SSCrazy

Points: 2378
More actions
June 2, 2011 at 7:34 am

#389642

Hiya,
I have the following query, which returns data from our clocking in database;
SELECTE.EMP_Forename + ' ' + E.EMP_Surname AS Name,
CASE CLK_Type
WHEN 'I' THEN 'In'
WHEN 'B' THEN 'Break (Start)'
WHEN 'R' THEN 'Break (End)'
WHEN 'O' THEN 'Out'
END AS Status, T.TER_Description AS Location, CLK_Date + CLK_Time AS Date_Time
FROMISYS.Intelligent.dbo.Employees E JOIN
ISYS.Intelligent.dbo.[Clocking Records] C ON C.CLK_Clock_No = E.EMP_ClockNumber JOIN
ISYS.Intelligent.dbo.Terminals T ON T.TER_Code = C.CLK_Terminal
ORDER BYE.EMP_Surname, Date_Time DESC
However this returns the following (trimmed down);
James BloggsBreak (End)Sevenoaks TA2011-06-02 12:46:00.000
James BloggsBreak (Start)Sevenoaks TA2011-06-02 12:15:00.000
James BloggsInSevenoaks TA2011-06-02 08:10:00.000
Jill BloggsBreak (Start)London TA2011-06-02 12:36:00.000
Jill BloggsInLondon TA2011-06-02 09:42:00.000
I know why it's doing this, but I need it to show only the most recent row based on the date/time column;
James BloggsBreak (End)Sevenoaks TA2011-06-02 12:46:00.000
Jill BloggsBreak (Start)London TA2011-06-02 12:36:00.000
Also, you see how I am having to specify the full 'name' of the column - ISYS.Intelligent.dbo.Employees for example - is it possible to avoid this by specify some sort of alias at the start and reference this when needed.
Something like this;
z = ISYS.Intelligent.dbo
z.Employees
z.[Clocking Records]
z.Terminals
etc..
Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I don't know any way of aliasing like that, but if you run all commands in the context of the one database, you only need dbo.TableName.

As for only returning the most recent activity for each employee, do something like this:

SELECT

Employee

, MAX(ActivityTime) AS ActivityTime

FROM

MyTable

GROUP BY

Employee

and join that to the original table on Employee and ActivityTime.

John

lanky_doodle SSCrazy Points: 2378 More actions · Answer 2

John Mitchell-245523 (6/2/2011)
but if you run all commands in the context of the one database

That's the 'problem', I connect to one database in my connection string, then reference tables, columns etc in linked servers, hence the need for the full 'path'.

lanky_doodle SSCrazy Points: 2378 More actions · Answer 3

John Mitchell-245523 (6/2/2011)
As for only returning the most recent activity for each employee, do something like this:
SELECT
Employee
, MAX(ActivityTime) AS ActivityTime
FROM
MyTable
GROUP BY
Employee
and join that to the original table on Employee and ActivityTime.

Thanks. I've not come across joining tables in this way. Could you expand?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Brainwave! Have a read about synonyms and see whether that helps you.

For the joining, use a CTE (Common Table Expression). If you do it like this:

;WITH MyCTE AS (

<select query>

)....

Then you can refer to MyCTE in the same batch as if it were just another table - so you can join it to your original table.

John

lanky_doodle SSCrazy Points: 2378 More actions · Answer 5

Thanks

The CTE trick doesn't seem to work either. Well the solution does work, but it's not doing what I want to achieve.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

If I am understanding correctly you want to use a CTE containing the ROW_NUMBER() function where you PARTITION the data by name, order by your date column in descending order and only select the rows from the CTE where the row number = 1.

If you provide DDL for your tables and some DML to create test data we can provide you tested code.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

lanky_doodle SSCrazy Points: 2378 More actions · Answer 7

opc.three (6/2/2011)
CTE, ROW_NUMBER(), PARTITION

Thanks for this! They gave me the platform to, I think, solve the problem;

WITH cte_Clockings AS

(

SELECTE.EMP_Forename, E.EMP_Surname,

ISNULL(CASE C.CLK_Type

WHEN 'I' THEN 'In'

WHEN 'B' THEN 'Out (Break)'

WHEN 'R' THEN 'In'

WHEN 'O' THEN 'Out'

WHEN 'A' THEN 'Activity'

END,'Unknown') AS Status, ISNULL(T.TER_Description,'Unknown') AS Location, ISNULL(C.CLK_Date + C.CLK_Time,'01/01/1900') AS Date_Time,

ROW_NUMBER() over(PARTITION BY E.EMP_ClockNumber ORDER BY C.CLK_Date + C.CLK_Time DESC) AS RowNum

FROMEmployees E LEFT JOIN

[Clocking Records] C ON C.CLK_Clock_No = E.EMP_ClockNumber LEFT JOIN

Terminals T ON T.TER_Code = C.CLK_Terminal

)

SELECTEMP_Forename + ' ' + EMP_Surname AS Name, Status, Location, Date_Time

FROMcte_Clockings

WHERERowNum = 1

ORDER BYEMP_Surname

Does this look sound?

lanky_doodle SSCrazy Points: 2378 More actions · Answer 8

John Mitchell-245523 (6/2/2011)
Brainwave! Have a read about synonyms and see whether that helps you.

That does indeed work;

CREATE SYNONUM synName

FOR

[linkedServerName].[databaseName].[dbo].[tableName]

Thanks!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

lanky_doodle (6/3/2011)
Does this look sound?

That's the query structure I was thinking would work for you. You are using clock number, not name, which is a proper column to partition on since it's going to be unique whereas name may not...I was commenting only on your expected results. Please post DDL and DML next time 😀

Nice work! Thanks for posting back 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato