Feeds:
Posts
Comments

Archive for June, 2014

Say a Business Unit A’s last incident date was 12 April 2013. The date this blog was written is 15 June 2014.

If no incidents and injuries after 360 days, then this business unit has achieved a Perfect Year on 7 Apr 2014 (past). The next Perfect Years would be 2 Apr 2015 (future) and so on.

If no incidents and injuries after 180 days, then this business unit has achieved a Perfect Half Year on 9 Oct 2013 (past). The next Perfect Half Years would be 7 Apr 2014 (past), 4 Oct 2014 (future) and so on.

If no incidents and injuries after 90 days, then this business unit has achieved a Perfect Quarter on 11 Jul 2013. The next Perfect Quarters would be 9 Oct 2013 (past), 7 Jan 2014 (past), 7 Apr 2014 (past), 6 July 2014 (future), 4 Oct 2014 (future) and so on.

In summary, Business Unit A will achieve a Perfect year on 2 Apr 2015, Perfect Half year on 4 Oct 2014 and Perfect Quarter on 6 July 2014. They will be rewarded according to the achievement. The greater the achievement is, the bigger the $$ amount is.

Reward

As shown above, on 4 Oct 2014, Business Unit A will achieve both Perfect Half Year and Perfect Quarter. When there is a tie, we pick the greater achievement, hence the Perfect Half Year will be picked.

Question – How do we achieve the above in T-SQL?
The answer is to use the Recursive Common Table Expressions (CTE). Recursive is the process in which the query executes itself. An example to calculate the perfect year reward is shown below. The base record for the CTE is derived by the first SQL query before UNION ALL. As the reward is calculated based on the last incident date, the Reward Date is set to the Last Incident date in the first SQL query before UNION ALL. Second query after UNION ALL is executed repeatedly to get results and it will continue until it has executed the number of times as you specified in the query. So the second query will return with the Reward Date of:
2014-04-07 00:00:00.000
2015-04-02 00:00:00.000
2016-03-27 00:00:00.000

Recursive CTE

DECLARE @Rewards AS TABLE
(
RewardCounter INT
,BusinessUnit NVARCHAR(100)
,Reward NVARCHAR(50)
,Achievement INT
,RewardDate DATETIME
,Num INT
);

WITH PerfectYearRewards AS
(
SELECT RewardCounter = 1
,BusinessUnit
,Reward = ‘Perfect Year’
,Achievement = 360
,RewardDate = LastIncidentDate
,Num = 1
FROM Incidents

UNION ALL

SELECT RewardCounter = RewardCounter + 1
,BusinessUnit
,Reward = ‘Perfect Year’
,Achievement = 360
,RewardDate = DATEADD(DAY, 360, RewardDate)
,Num = Num + 1
FROM PerfectYearRewards
WHERE RewardCounter < 4
— Number 4 here is to return 3 perfect year records (future)
— For Perfect Half Year you may want to set it to 5 and Perfect Quarters to 10
)

INSERT INTO @Rewards
SELECT * FROM PerfectYearRewards

The Num field in the @Rewards table is used to identify that this reward date in this record is the last incident date when it is equal to 1. So any records with Num equals to 1 have to be excluded. Also as mentioned above, when there is a tie, we pick the greater achievement. This can be achieved by ranking the records using the RANK function. The end query will look like below.

SELECT BusinessUnit
,Reward
,Achievement
,RewardDate
,Num
,RANK() OVER (PARTITION BY BusinessUnit, RewardDate ORDER BY Achievement DESC) AS Ranking
INTO #Ranking
FROM @Rewards
WHERE Num 1
ORDER BY BusinessUnit, Ranking

The ultimate output that the user will get to see in the email looks like below:

Target 365 results for 04-Oct-2014

Business A has achieved a Perfect Half Year, Business F has achieved a Perfect Half Year

Advertisements

Read Full Post »

My SSIS package failed to create staging tables on a copy of a production database. The error is:

Msg 208, Level 16, State 1, Procedure Log_Table_DDL, Line 16
Invalid object name ‘dbo.Audit’.

It says invalid object name, which could be any object. Not realising that it is a trigger, I went to query the sys.all_objects table, which of course didn’t get me any close to find that particular object.

When an attempt to create objects in a database fails, a database trigger already exists to prevent this from happening. This trigger is located under Programmability > Database Triggers. Dropping or deleting this trigger has resolved this issue.

Read Full Post »

%d bloggers like this: