August 30, 2017 at 7:58 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 8:55 am
What about this?
select id ,Doctype, [DocName] , min(priority) PRIO,max(startdate) STDT , LAST_DOC.LAST_DOC
from #t
CROSS APPLY(
SELECT LAST_VALUE(FinalDOC) OVER(PARTITION BY id,Doctype, [DocName] ORDER BY [priority] DESC, startdate ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) LAST_DOC FROM #t GET_LAST
WHERE
GET_LAST.id = #t.id AND GET_LAST.DocType = #t.DocType AND GET_LAST.DocName = #t.DocName
) LAST_DOC
group by id,Doctype, [DocName], LAST_DOC.LAST_DOC
August 30, 2017 at 9:09 am
it's giving error'LAST_VALUE' is not a recognized built-in function name.
Thanks [/font]
August 30, 2017 at 9:14 am
Heh.. you posted this in the 2012 forum as well, i though i responded there. It looks like there's an answer there that'll work in 2008 as well.
August 30, 2017 at 9:16 am
select *
from (select ROW_NUMBER()over(partition by docname order by priority,startdate desc) rownumber,* from #t)a
where rownumber=1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply