I have two tables like this having the structure below:
Create table #activities
(activityCode nvarchar(40), activityName nvarchar(100), modifiedOn datetime, activityCategoryIncId int, activityCategorySqlId int)
insert into #activities values ('S16-03269-01','Trial 01','2016-05-09 06:51:32.850', 2436, 1504)
insert into #activities values ('S16-03269-01/001','Phase plan','2016-04-29 10:36:08.077', 2443, 1504)
insert into #activities values ('S16-03269-01/002','Phase run','2016-05-09 06:51:32.853', 2472, 1504)
insert into #activities values ('S16-03269-01/003','A1 Beginning','2016-04-29 10:36:08.083', 2473, 1504)
insert into #activities values ('S16-03269-01/004','A2 Beginning','2016-04-28 06:51:32.863', 2473, 1504)
insert into #activities values ('S16-03269-01/005','EV1 0DA-A','2016-04-29 10:36:08.093', 2474, 1504)
Create table #activitiescategories
(activityCategoryIncId int, activityCategorySqlId int, activityCategoryCode nvarchar(40), activityCategoryName nvarchar(100))
INSERT INTO #activitiescategories values( 2436, 1504, 'EAS-1F', 'Exp. phase')
INSERT INTO #activitiescategories values( 2443, 1504, 'EAS-2', 'Phase plan')
INSERT INTO #activitiescategories values( 2472, 1504, 'EAS-31', 'Phase run')
INSERT INTO #activitiescategories values( 2473, 1504, 'EAS-31A1', 'Application')
INSERT INTO #activitiescategories values( 2474, 1504, 'EAS-31A2', 'Assessment')
The activities have a hierarchy like this
Grand Parent--First Level---Trial01
Parent --Second level--Phase run
Child --Third Level---A1 Beginning
A2 Beginning
EV1 0DA-A
I need to show the activity (only one) in the third level (ie. child) which are in
activity category in EAS-31A1 or EAS-31A2.
which was last modified.
a) if modifiedOn dates are same, time should be considered.
b) If time is also same, alaphabetical order ie. here for example EV1 0DA-A should be dispalyed if time is also same.
I need help to include these conditions also in the query.
The desired output is something like:
'S16-03269-01/005', 'EV1 0DA-A', '2016-04-29 10:36:08.093'
Since the activities 'A1 Beginning' and EV1 are having same modifiedon dates and times, it should consider alphabetical order in descending.
ie. E comes after A. So 'EV1 0DA-A'.
One doubt is that in the time part of EV1, there is a difference from A1. ie. 093.
I am not sure that can be taken into consideration to get the latest modified date result as 'EV1 0DA-A'.