Query help needed

Transact-SQL
,
1

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'.

2

I don't understand your hierarchy, but this might get you a bit closer to a solution:

with cte
  as (select activitycode
            ,activityname
            ,modifiedon
            ,row_number() over(order by cast(datediff(minute
                                                     ,0
                                                     ,modifiedon
                                                     )
                                             as bigint
                                            )
                                       *60
                                       +datepart(second
                                                ,modifiedon
                                                )
                                        desc
                                       ,activityname desc
                              )
             as rn
        from #activities
       where activityname in ('A1 Beginning'
                             ,'A2 Beginning'
                             ,'EV1 0DA-A'
                             )
     )
select activitycode
      ,activityname
      ,modifiedon
  from cte
 where rn=1
;
3

Thanks bitsmed for your help. It worked. Appreciating your time and effort.

I have modified the query which suits better.

;with cte
  as (select activitycode
            ,activityname
            ,modifiedon
            ,row_number() over(order by cast(datediff(minute,0,modifiedon)
                                             as bigint) *60 +datepart(second,modifiedon) desc ,activityname desc )
             as rn
        FROM #activities AC
INNER JOIN #activitiescategories ACG
ON Ac.activityCategoryIncId = ACG.activityCategoryIncId AND Ac.activityCategorySqlId = ACG.activityCategorySqlId 
       where activityCategoryCode in ('EAS-31A1'
                             ,'EAS-31A2'
                             )
     )
select activitycode
      ,activityname
      ,modifiedon
  from cte
 where rn=1

I have a doubt now.

SELECT LEFT(AC.activityCode,12) AS TrialCode from #Activities AC

How can I put this field TrialCode in PARTITION BY clause like this below:

row_number() over(PARTITION BY TrialCode order by cast(datediff(minute ,0 ,AC.modifiedon) as bigint) * 60 + datepart(second ,AC.modifiedon) desc ,AC.activityname desc ) AS RowNumber,

Thanks

4

Something like:

;
with cte
  as (select ac.activitycode
            ,ac.activityname
            ,ac.modifiedon
            ,row_number() over(partition by left(ac.activitycode)
                               order by cast(datediff(minute,0,ac.modifiedon)
                                             as bigint
                                            )
                                            *60
                                            +datepart(second,ac.modifiedon) desc
                                       ,ac.activityname desc
                              )
             as rn
        from #activities as ac
             inner join #activitiescategories as acg
                     on acg.activitycategoryincid=ac.activitycategoryincid
                    and acg.activitycategorysqlid=ac.activitycategorysqlid
       where ac.activityCategoryCode in ('EAS-31A1'
                                        ,'EAS-31A2'
                                        )
     )
select activitycode
      ,activityname
      ,modifiedon
      ,left(activitycode,12) as trialcode
  from cte
 where rn=1
;