May 12, 2016 at 8:20 am
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.
May 12, 2016 at 8:31 am
Can you post what the desired output should look like?
-- Itzik Ben-Gan 2001
May 12, 2016 at 8:44 am
The desired output is 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'.
May 12, 2016 at 10:16 am
bit of a guess .....maybe something along these lines?
WITH CTE as (
SELECT activityCode, activityName, modifiedOn,
ROW_NUMBER() OVER (PARTITION BY
LEFT(activityCode, LEN(activityCode) - CHARINDEX('/', REVERSE(activityCode))) ORDER BY modifiedOn Desc) rn
FROM activities
WHERE Activityname NOT IN ('Trial 01', 'Phase run')
)
SELECT * FROM CTE where rn = 1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 8:43 am
I have tried this query , which is working
;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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply