LAST_VALUE in T-SQLdoes not always return the same value

LAST_VALUE does not always return with the same value. The workaround is to use FIRST_VALUE but order the records descendingly.

 

Read Full Post »

Using DENSE_RANK function

I have a dataset as shown below. When the OP_CODE is null and PRIORITY = 1, I need to shift the OP_CODE of the Priority = 2 to become 1 and 3 to become 2 and 4 to become 3.

The dataset also has the history of the changes made to the records.

At first I ranked the records by using the RANK function but it didn’t work at all. It set the ranking incorrectly as shown below. It didn’t work because it ranks every record instead of every distinct record in the partition.

I then used DENSE_RANK function as it ranks the records within the partition of a result set, without any gaps in the ranking. When there is a tie in the partition, it ranks it to the same number. In this example, there are 3 records with the Priority_Shifted of 30000. These 3 records should all be ranked to 4. The DENSE_RANK function has done it! 🙂

 

 

Read Full Post »

T-SQL – Inserting 1002-01-01 resulting in “the conversion of a varchar data type to a datetime data type resulted in an out-of-range value” error.

I am having issue with setting a date to 1002-01-01 when null in SQL Server because it’s out of range.

To get around it, use the DATEFROMPARTS function (available from SQL Server 2012) and it must use datetime2 data type (datetime won’t work). An example is shown below.

CAST(DATEFROMPARTS(1002, 01, 01) AS DATETIME2)

 

Read Full Post »

CRM 2013 – stores date in UTC format

This is rather annoying as all dates stored into CRM database need to be converted to the local time.

There are two options:

1. Use the CRM standard views prefixed with Filtered
2. Use the CRM standard function calleddbo.fn_UTCToTzCodeSpecificLocalTime

Using option 1 with the Filtered standard view is slow.

I ended up with option 2 as it is quicker, but I need to get the time zone code from the UserSettingsBase table.

Example code:

DECLARE @TimeZoneCode INT

= ( SELECT TOP 1 TimeZoneCode

FROM UserSettingsBase

WHERE SystemUserId = dbo.fn_FindUserGuid()

)

 

SELECT DateOfAssessment = CAST(dbo.fn_UTCToTzCodeSpecificLocalTime(DateofAssessment, @TimeZoneCode) AS DATE)

 

Reference:

https://community.dynamics.com/crm/b/develop1/archive/2011/12/06/dynamics-crm-datetimes-the-last-word

 

 

 

 

 

Read Full Post »

Case study – Calculate reward for each business unit when they have achieved 360/180/90 days of injury and incident free

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.

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

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

Read Full Post »

Failure in creating a staging table with an error of Invalid object name ‘dbo.Audit’

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 »

How to get the first day and last day of the Australian Financial Year (T-SQL)

DECLARE @Date AS DATETIME = ‘2014-05-01’

DECLARE @FirstDayAUFinancialYear AS DATETIME =  

   DATEADD( MONTH,  (MONTH(@Date)– 1)/ 6 * 12 – 6,  CAST(CAST(YEAR(@Date) AS VARCHAR) ASDATETIME) )

 

DECLARE @LastDayAUFinancialYear AS  DATETIME =

    DATEADD(DAY,–1,DATEADD(MONTH, 12, @FirstDayAUFinancialYear))   

 

SELECT @FirstDayAUFinancialYear AS FirstDay, @LastDayAUFinancialYear AS LastDay

 

Output:

FirstDay                                        LastDay
2013-07-01 00:00:00.000          2014-06-30 00:00:00.000

Read Full Post »

SQL Server 2012 – using PERCENTILE_CONT window T-SQL function to calculate a median value! :-)

I have had to use OLAP cube MEDIAN function to calculate a median value in the SSRS report. I can’t believe how you can now use this new function in SQL Server 2012 to achieve the same! 🙂 This new function is not available in versions before SQL Server 2012.

An example using AdventureWorks2012:

SELECT d.Name as DepartmentName, Rate
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)  OVER (PARTITION BY Name) AS MedianCont
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh
ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL
AND d.Name in (‘Engineering’, ‘Document Control’, ‘Executive’, ‘Human Resources’)
ORDER BY  DepartmentName, Rate

The following shows Rate for each Department in an ascending order:

The result of using the PERCENTILE_CONT to get a median Rate value for each department is shown below:

 

 

 

 

 

 

 

 

 

Read Full Post »

SQL Server 2012 – the new T-SQL analytic functions are awesome!

These new windowing functions are awesome. There are 8 of them i.e. LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_CONT, and PERCENTILE_DISC. I have tried lag, lead, first_value and last_value and could see the advantages of using them straight away.  🙂

http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx

An example using AdventureWorks sample database:

select * from HumanResources.EmployeePayHistory where BusinessEntityID = 174

select BusinessEntityID, RateChangeDate, Rate, Lag(Rate) OVER (partition by BusinessEntityID order by RateChangeDate) as PreviousRateChange
,LEAD(Rate) OVER (partition by BusinessEntityID order by RateChangeDate) as NextRateChange
from HumanResources.EmployeePayHistory
where BusinessEntityID = 174

Read Full Post »

Inserting string containing apostrophes (SQL)

If you are migrating data from one source to a SQL Server database, you would most likely bump into string containing apostrophes such as Jan ’10 etc. As you know, you would need to add another apostrophe in T-SQL such as ” in order for the data to pass through.

SELECT ‘Jan ”10’

To deal with this, use REPLACE() function. Replace one apostrophe with 2 apostrophes.

SELECT REPLACE(‘Jan ”10’,””,”””)

 

Read Full Post »