August 30, 2017 at 8:53 am
Hi all,
I have a question , I was trying to get a result based on 2 different conditions that I have to place in ORDER..
first test for MIN of priority ...then only if priorities are same go and check for max of startdate to get final doc value in result..
..
I have to get for each ID final doc value, based on 1 rule or the other
first check MIN(priority) and get finaldoc, if priority is same then check max(start date) and get the finaldoc
the grouping has to be done by docname..
Here is the SQL sample code:
CREATE TABLE #t(
[ID] [varchar](10) NULL,
[finalDoc] [int] NULL,
[DocType] [varchar](5) NULL,
[DocName] [varchar](50) NULL,
[Priority] [int] NULL,
[StartDate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO #t
SELECT 555, 12075 ,'BEN', 'Benefit doc' ,101, '2016-01-01 00:00:00.000'
UNION
SELECT 555, 12314 ,'BEN', 'Benefit doc' ,101, '2017-01-01 00:00:00.000'
UNION
SELECT 555, 12503 ,'BEN', 'Summary doc' ,100, '2016-01-01 00:00:00.000'
union
SELECT 555, 13391 ,'BEN', 'Coverage doc' ,200, '2015-01-01 00:00:00.000'
union
SELECT 555, 12075 ,'BEN', 'Coverage doc' ,300, '2017-01-01 00:00:00.000'
select * from #t order by docname
--Expected result
--id Doctype DocName PRIO STDT FinalDOC
--555 BEN Benefit doc 101 2017-01-01 00:00:00.000 12314
--555 BEN Coverage doc 200 2015-01-01 00:00:00.000 13391
--555 BEN Summary doc 100 2016-01-01 00:00:00.000 12503
I wrote this select using row number but it doesn't work for coverage doc for min priority:
SELECT * ,ROW_NUMBER() OVER (PARTITION BY [Priority]
ORDER BY [Priority], [StartDate] desc) RN into #u FROM #t
select * from #u
select * from #u where RN = 1
Any help on this..
Thanks in advance..
Thanks [/font]
August 30, 2017 at 9:11 am
You would need to do a sort of subquery to get the minimum priority value for each id and doc type, something like this
select u1.* from #u u1 inner join (SELECT min(priority) as minpriority, id from #u u2 GROUP BY u2.docname, u2.id) dev1 on u1.id = dev1.id and u1.Priority = dev1.minpriority where RN = 1
August 30, 2017 at 10:14 am
with cte
as
(
select id,Doctype,DocName,[Priority], StartDate,[finalDoc],RANK() over (partition by DocName order by [Priority] asc,StartDate desc) as r_no
from #t
) SELECT id,Doctype,DocName,[Priority], StartDate,[finalDoc] from cte where r_no=1[f
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply