Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Demanding and difficult logic qns. Sorry but i need help. Expand / Collapse
Author
Message
Posted Sunday, December 09, 2012 2:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180
Hi pros out there,

I am introduced to SSIS to do ETL for repporting in SSRS very recently. I am given a task to analyse employees working hours and pattern through the use of data gathered from access point. I have done something similar to do with QlikView(through use of several sql statements and residents table) however SSIS is totally a stranger to me. I do not uds how i can achieve what this using SSIS. Cant even get the logic and work flow. I need some serious help here. The first table is somewhat my source table data:

PASS_M EVENT_D EVENT_T MESSAGE_X
Staff A 30/04/2012 18:10:00 Exit
Staff B 01/05/2012 08:30:10 Access
Staff B 01/05/2012 18:31:20 Exit
Staff C 30/04/2012 22:00:17 Access
Staff C 30/04/2012 22:01:28 Access
Staff C 01/05/2012 05:31:00 Exit
Staff D 30/04/2012 10:00:00 Access
Staff D 30/04/2012 16:00:00 Exit
Staff D 30/04/2012 16:03:00 Exit
Staff D 01/05/2012 19:50:00 Access
Staff D 02/05/2012 03:50:00 Exit
Staff D 02/05/2012 12:00:00 Access
Staff D 02/05/2012 18:00:00 Exit
Staff E 02/05/2012 19:00:00 Access

This is what i want to achieve, my target table:

PASS_M ENTRY_DT EXIT_DT WORKED_HRS CONSECUTIVE_D
Staff B 01/05/2012 08:30:10 01/05/2012 18:31:20 10:01 1
Staff C 30/04/2012 22:00:17 01/05/2012 05:31:00 07:31 1
Staff D 30/04/2012 10:00:00 30/04/2012 16:03:00 06:03 1
Staff D 01/05/2012 19:50:00 02/05/2012 03:50:00 08:00 2
Staff D 02/05/2012 12:00:00 02/05/2012 18:00:00 06:00 3

Assumptions:
If there are multiple access for one exit, take the earliest access date and time. If there are multiple exit for an access, take the latest exit date time. Not able to take earliest access or latest exit base on the date itself as some legit access maybe during late evenings and some exits may be early in the morning. Secondly, for those accesses with no exit or exit with any access, do not include it in first.

I know many will feel i am asking alot over here, i do agree but i really need serious help. For those who know how, and willing to help please give some advice.
Attached is a nicer view of my table.

Greatly appreciate any help given.
Thanks,
10e5x


  Post Attachments 
AccessTable.docx (14 views, 16.32 KB)
Post #1394387
Posted Sunday, December 09, 2012 4:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 4,828, Visits: 11,184
May I ask how you think that SSIS can help you with this problem?

Based on the data provided, it looks like a stored proc would be suffucient to provide the info to the SSRS report.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1394395
Posted Sunday, December 09, 2012 8:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180
Phil Parkin (12/9/2012)
May I ask how you think that SSIS can help you with this problem?

Based on the data provided, it looks like a stored proc would be suffucient to provide the info to the SSRS report.


Hi Phil,
Firstly i want to thank you for replying. Thanks. Oh i am sorry if my way of asking is way too lousy, i am quite new to forums, i will read on the link provided. U mentioned about how can i think SSIS can help me with my problem? actually i am not even sure myself. This task was just throw to me without further guidance and i was just told "study the data and requirement, Use SSIS to perform the etl process and then use SSRS to do a report" I have done a few SSIS tutorials and i realize SSIS always work with data flow. So in order to achieve what i want, how will my flow look like? i know my first step is get alll my date and time fields into correct ssis data type. Then maybe i can do some select bla bla bla from table order by staff name, event_d and event_t... how shall i do this?

You mentioned about stored procedure, can it be done at SSIS or?

Sorry if i sound too noob but i am really confused.
Post #1394397
Posted Sunday, December 09, 2012 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
So what do you want to do if there's an access late in the day and no exit until the next day?

Also, what do you want CONSECUTIVE_D to be if it spans a weekend?

Last but not least, I disagree with the rule of using the earliest entry date for this. Someone could come in and immediately turn around and walk out without closing the door and still have "hours worked" accrued. Same thing with the latest exit. They could exit and then later follow someone in and exit "for real and have "hours worked" accrued the whole time they're gone.

For sure, this system should NOT be used for a timekeeping system of any type.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394402
Posted Sunday, December 09, 2012 12:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 4,828, Visits: 11,184
guoxiang1610 (12/9/2012)
Phil Parkin (12/9/2012)
May I ask how you think that SSIS can help you with this problem?

Based on the data provided, it looks like a stored proc would be suffucient to provide the info to the SSRS report.


Hi Phil,
Firstly i want to thank you for replying. Thanks. Oh i am sorry if my way of asking is way too lousy, i am quite new to forums, i will read on the link provided. U mentioned about how can i think SSIS can help me with my problem? actually i am not even sure myself. This task was just throw to me without further guidance and i was just told "study the data and requirement, Use SSIS to perform the etl process and then use SSRS to do a report" I have done a few SSIS tutorials and i realize SSIS always work with data flow. So in order to achieve what i want, how will my flow look like? i know my first step is get alll my date and time fields into correct ssis data type. Then maybe i can do some select bla bla bla from table order by staff name, event_d and event_t... how shall i do this?

You mentioned about stored procedure, can it be done at SSIS or?

Sorry if i sound too noob but i am really confused.


No need to apologise, I can tell that you are trying to get this resolved.

Can you first of all confirm the source of the data - is it a SQL Server table?

If it is (and leaving aside Jeff's comments around the proposed business rules - which also need to be resolved), this not an SSIS problem. What you need is a stored proc - of whatever complexity - which returns the data in the form you have shown. This proc will be your report's data source, simple as that.

If your data is coming from, for example, a text file, we're back in SSIS territory. But in this case, there would be at least two processes involved:

1) A regular SSIS package to import the data into SQL Server
2) One or more SSRS reports which use stored procs, as necessary, to access and present the imported data.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1394408
Posted Sunday, December 09, 2012 12:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Here's one possible answer...

Assuming the "ACCESS_SOURCE" is a table, we need to first combine the Event_D and Event_T columns into a single, sortable, calculable column. This should have been done when the table was imported by SSIS instead of in the code below.

We also take the opportunity to build a working table with some extra columns to make it easier to calculate and aggregate entry and exit times. Here's the code to build the working table. As always, the details are in the comments. The clustered index applied to this table is absolutely required for the next step to work correctly.

--===== 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 (--==== 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)
;
--===== Add the quintessential clustered index for the Quirky Update
CREATE UNIQUE CLUSTERED INDEX IX_#Work_QU
ON #Work (Pass_M, Event_DT)
;

I'm pretty sure that someone could figure this all out just using some prestidigitation with ROW_NUMBER() but I decided to do it a different way. This is known as the "Quirky Update" method and it emulates the same method that you would use if you were doing this in front-end code. It will blow the doors off of any recursive CTE, Cursor, or While loop method that you'll be able to come up with. I must warn you that the method is NOT supported by Microsoft but has been a viable method even in the earliest versions of SQL Server and continues to be viable even in SQL Server 2012. It does not support partitioned tables but the Temp Table takes care of that little problem.

The table and option "hints" are absolutely required.

--===== Declare some obviously named variables just like you would do in the front end
DECLARE @PrevPass_M VARCHAR(10),
@PrevEntry_DT DATETIME,
@PrevMessage_X VARCHAR(6),
@SafetyCounter INT
;
--===== Assuming there's at least one row to be process,
-- preset the safety counter to "1"
SELECT @SafetyCounter = 1
;
--===== "Smear" the "first" entry dates down to other rows.
-- See the embedded comments on the CASE function for details.
WITH
cteSafetyRowNumber AS
( --=== Adds a "counter" to the rows in the expected processing order.
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Pass_M, Event_DT),
Pass_M, Event_DT, Message_X, Entry_DT
FROM #Work
)
UPDATE srn
SET @PrevEntry_DT = CASE --This CASE does the safety check
WHEN RowNumber = @SafetyCounter --Checks sequence of processing
THEN
CASE --This CASE does the data "smear"
--== Finds first "Access" after "Exit" for same Pass_M
WHEN Pass_M = @PrevPass_M
AND @PrevMessage_X = 'Exit'
AND Message_X = 'Access'
THEN Event_DT
--== Finds first "Access" for new Pass_M
WHEN Pass_M <> @PrevPass_M
AND Message_X = 'Access'
THEN Event_DT
--== "Data smear" the current date for everything else
ELSE @PrevEntry_DT
END
ELSE 1/0 --Forces an error if out of sequence
END,
--== Setup for next "iteration"
Entry_Dt = @PrevEntry_DT,
@PrevPass_M = Pass_M,
@PrevMessage_X = Message_X,
@SafetyCounter = @SafetyCounter + 1
FROM cteSafetyRowNumber AS srn WITH (TABLOCKX)
OPTION (MAXDOP 1)
;
--===== This will create the final required output
WITH
ctePreAggregate AS
( --=== Find the max exit date for every smeared PASS_M/entry date group.
SELECT Pass_M,
Entry_DT,
Exit_DT = MAX(Exit_Dt)
FROM #Work
WHERE Entry_DT IS NOT NULL
AND Exit_DT IS NOT NULL
GROUP BY Pass_M, Entry_Dt
) --=== This simply calculates the columns that require calculations.
SELECT Pass_M,
Entry_Dt,
Exit_Dt,
Worked_Hrs = CAST(DATEDIFF(hh,0,Exit_DT-Entry_DT) AS VARCHAR(10))
+ ':'
+ SUBSTRING(CONVERT(CHAR(5),DATEADD(ss,30,Exit_DT-Entry_DT),108),4,2),
Consecutive_D = ROW_NUMBER() OVER (PARTITION BY Pass_M ORDER BY Entry_DT)
FROM ctePreAggregate
ORDER BY Pass_M, Entry_DT
;

That produces the requested output as follows.
Pass_M  Entry_Dt                Exit_Dt                 Worked_Hrs Consecutive_D
------- ----------------------- ----------------------- ---------- -------------
Staff B 2012-05-01 08:30:10.000 2012-05-01 18:31:20.000 10:01 1
Staff C 2012-04-30 22:00:17.000 2012-05-01 05:31:00.000 7:31 1
Staff D 2012-04-30 10:00:00.000 2012-04-30 16:03:00.000 6:03 1
Staff D 2012-05-01 19:50:00.000 2012-05-02 03:50:00.000 8:00 2
Staff D 2012-05-02 12:00:00.000 2012-05-02 18:00:00.000 6:00 3


Again and as a reminder, if this is meant to be some form of timekeeping system, there are some serious faults with the logic of the original requirements. Unless door entry and exit can be guaranteed recorded events and that every entry will have one and only one exit, this data must NOT be used for keeping time especially for pay!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394411
Posted Sunday, December 09, 2012 5:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180
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 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
Post #1394427
Posted Sunday, December 09, 2012 7:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180
Hi phil,

My source is an SQL table from SQL server 2005. So you are suggesting me to leave everything till the SSRS side, then do a stored procedure? In my opinion which will be most prob wrong, would be getting the EVENT_DT column out in the correct format then followed by filtering the data that i want using condition split. After getting this two done. i shall do a order by, then store it in a destination table. Then i will be able to use the destination table at SSRS with the stored procedure methods u mention. Am i right to assume that?

Thanks,
10e5x
Post #1394429
Posted Monday, December 10, 2012 5:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
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 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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394566
Posted Monday, December 10, 2012 6:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 4,828, Visits: 11,184
This is a most entertaining thread.

I could help with the SSIS stuff (thanks Jeff!) - if it were needed, but it is not.

First task: write the proc to return the data in the required format. With parameters to limit selection, based on requirements.

Second task: develop whatever SSRS reports are needed, using the proc as a data source.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1394567
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse