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