DECLARE @d TABLE(dt DATETIME);
INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');
;WITH x AS
(
SELECT
e = dt,
r = ROW_NUMBER() OVER (ORDER BY dt),
p = LAG(dt, 1) OVER (ORDER BY dt),
m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
FROM @d
)
SELECT
start = p,
[end] = e,
[minutes_elapsed] = m,
[time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
FROM x
WHERE r % 2 = 0
ORDER BY e;
If you need to do this on older versions (2005 -> 2008 R2):
;WITH cte1 AS
(
SELECT
e = dt,
r = ROW_NUMBER() OVER (ORDER BY dt)
FROM @d
),
cte2 AS
(
SELECT
x.r,
[start] = x.e,
[end] = y.e,
m = DATEDIFF(MINUTE, x.e, y.e)
FROM cte1 AS x INNER JOIN cte1 AS y
ON x.r = y.r - 1
WHERE y.r % 2 = 0
)
SELECT
[start],
[end],
minutes_elapsed = m,
[time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
FROM cte2
ORDER BY [start];
Suggested schema for recording time in/time out is pretty simple:
CREATE TABLE dbo.Shifts
(
EmployeeID INT NOT NULL, -- probably Foreign Key, right?
ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ClockOut DATETIME NULL
);
Now your query is:
SELECT
EmployeeID,
ClockIn,
ClockOut,
ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE,
DATEDIFF(MINUTE, ClockIn,
COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
), 0))
FROM dbo.Shifts
WHERE ...
Create an initial Execute SQL Task
in the Control Flow and use the following code in it:
IF ((SELECT CAST(SERVERPROPERTY('ProductVersion') as CHAR(2))) = 11)
BEGIN
SELECT 'TRUE' As Result
END
ELSE
SELECT 'FALSE' As Result
You can of course do several things with this, one of the easiest and clearest for SSIS developers would be to store the results in a SSIS variable and assign the Precedent Constraint after the SQL Task to Expression and Constraint
.
If the Execute SQL Task
Succeeds and returns TRUE it will allow the rest of the execution, which could be a single package, part of a larger execution stream, or a master package. If it fails or returns FALSE your package stops... might be worth putting in another path for an alert message or log of this though.
You can also use (SELECT @@VERSION) LIKE 'Microsoft SQL Server 2014%'
. I like this as it's a more familiar version nomenclature.
Best Answer
Method 1 : YOu can use the below VB code in SQL agent job to find the information on packages running on sql server 2012:
Method 2: Refer to below excellent read from Jamie Thomson which have neat and clean scripts to provide info on you're running packages:
http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx
Method 3: YOu can use below SQL code which i've been using: