10e5x (12/9/2012)
Hi Jeff,HOLY MAMA!!! Omg how did that even come about? Its my third time reading your method and i am still like circling around the forest. Thats way too high level for me:-D I shall read a few more times to digest before starting to try it out. Answering to your earlier qns, if there is a late access is the night with maybe exit only the following day? E.g:if one access at 01/05/2012 23:40:00 and with exit only the next day 02/05/2012 06:00:00, i would pair these two up as a valid record. If there are no exit yet, therefore nth to pair with, we shall ignore this entry record first. Ya we are not using these hrs to pay our workers, we are using these to track how long have they worked. We are concern about their health, we do not want them to overwork, resulting to be sick or neglecting their family. This system will only act as a guideline. We really hope we will be able to start first phase testing next week.
And warning.... Dumb QNS AHEAD:
I feel guilty asking these following qns:
1) where should all these codes goes to? SSIS have a place to place all this codes? (mind to give steps details?)
2) i do not need to have those tutorial steps putting data flow task in control bla bla bla?
3) thirdly:
INTO #Work
FROM (--==== This whole section would just be FROM dbo.ACCESS_SOURCE
SELECT 'Staff A','30/04/2012','18:10:00','Exit' UNION ALL
SELECT 'Staff B','01/05/2012','08:30:10','Access' UNION ALL
SELECT 'Staff B','01/05/2012','18:31:20','Exit' UNION ALL
SELECT 'Staff C','30/04/2012','22:00:17','Access' UNION ALL
SELECT 'Staff C','30/04/2012','22:01:28','Access' UNION ALL
SELECT 'Staff C','01/05/2012','05:31:00','Exit' UNION ALL
SELECT 'Staff D','30/04/2012','10:00:00','Access' UNION ALL
SELECT 'Staff D','30/04/2012','16:00:00','Exit' UNION ALL
SELECT 'Staff D','30/04/2012','16:03:00','Exit' UNION ALL
SELECT 'Staff D','01/05/2012','19:50:00','Access' UNION ALL
SELECT 'Staff D','02/05/2012','03:50:00','Exit' UNION ALL
SELECT 'Staff D','02/05/2012','12:00:00','Access' UNION ALL
SELECT 'Staff D','02/05/2012','18:00:00','Exit' UNION ALL
SELECT 'Staff E','02/05/2012','19:00:00','Access'
) source (PASS_M, EVENT_D, EVENT_T, MESSAGE_X)
CREATE UNIQUE CLUSTERED INDEX IX_#Work_QU
ON #Work (Pass_M, Event_DT)
This i totaly dont uds. I though i should get all the data from the source table? how will unique clustered index looks like? sounds alien to me.
OMG I AM SO NOOB, seriously felt inferior right now:(
Thanks and Sorry,
10e5x
The Quirky Update really isn't that special. It's the same way you'd do things in front-end code except that SQL Server does all the row reading and writing in it's own little loop (a "pseudo cursor") behind the scenes. The "loop" is all done by the UPDATE and the order is controlled by the ORDER BY of the safety counter with a major assist for the order of the Clustered Index. It'll do things like this on a million rows in just a couple of seconds or less on most machines.
As for the intended use your company has for the code, well done. It's nice to see a company that values its people.
As for what to do with the first part of the code to make it look at your table, just replace the stuff in parenthesis with the table name. Like this...
--===== Set the date format for the given data
SET DATEFORMAT DMY
;
--===== Conditionally drop the temp table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#Corrected','U') IS NOT NULL
DROP TABLE #Corrected
;
--===== Create a table with combined dates/times.
-- This should have been done during the SSIS load.
SELECT Pass_M = CAST(PASS_M AS VARCHAR(10)),
Event_DT = CAST(EVENT_D AS DATETIME)
+ CAST(EVENT_T AS DATETIME),
Message_X = CAST(MESSAGE_X AS VARCHAR(6)),
Entry_DT = CAST(NULL AS DATETIME),
Exit_DT = CASE
WHEN MESSAGE_X = 'Exit'
THEN CAST(EVENT_D AS DATETIME)+CAST(EVENT_T AS DATETIME)
ELSE CAST(NULL AS DATETIME)
END
INTO #Work
FROM dbo.Access_Source
;
--===== Add the quintessential clustered index for the Quirky Update
CREATE UNIQUE CLUSTERED INDEX IX_#Work_QU
ON #Work (Pass_M, Event_DT)
;
As for what to do with it in SSIS, I don't really have a clue. SSIS was built to supposedly make ETL easier than trying to do it in T-SQL. I've only worked with it once because I know how to do all of the same stuff pretty easily in T-SQL. That, notwithstanding, I believe this would go into something like an "Execute TSQL" task and you'd probably have to route the output of my code to some other type of file task. I believe that Phil Parkin may be able to help you more in that area because I don't know for sure.
Last but not least, the code will handle the "late entry" stuff as you described just fine as it is. I figured that you'd want it that way but it can reall get the numbers out of whack if there's an entry at something like 1PM without an exit and they "sneak in" the next day and then final have an exit.
--Jeff Moden
Change is inevitable... Change for the better is not.