Trying to use "LAST_VALUE" Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to use "LAST_VALUE" Statement

Author

Topic

cobbdj01
Starting Member

5 Posts

Posted - 2013-09-20 : 12:22:54

I am attempting to select the last claims ID

SELECT
dbo.CMC_GRGR_GROUP.GRGR_CK,
dbo.CMC_GRGR_GROUP.GRGR_ID,
dbo.CMC_GRGR_GROUP.GRGR_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_ID,
dbo.CMC_SBSB_SUBSC.SBSB_LAST_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_FIRST_NAME,
dbo.CMC_CLCL_CLAIM.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
--dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
GETDATE() AS today,
#temp1112b.Age,

CASE
WHEN #temp1112b.Age <= 15 THEN 15
WHEN (#temp1112b.Age > 15 AND #temp1112b.Age <= 30) THEN 30
WHEN (#temp1112b.Age > 30 AND #temp1112b.Age <= 45) THEN 45
WHEN (#temp1112b.Age > 45 AND #temp1112b.Age <= 60) THEN 60
WHEN (#temp1112b.Age > 60 AND #temp1112b.Age <= 90) THEN 90
WHEN (#temp1112b.Age > 90 AND #temp1112b.Age <= 120) THEN 120
WHEN (#temp1112b.Age > 120 AND #temp1112b.Age <= 180) THEN 180
ELSE 181
END AS AgeRange,
dbo.CMC_PRPR_PROV.PRPR_ID,
dbo.CMC_PRPR_PROV.PRPR_NAME,
CASE
WHEN (dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS = '') THEN CAST (dbo.CER_SYML_MSG_LOG.SYMD_MSG_CD as CHAR)
ELSE dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS
END AS CLST_MCTR_REAS
INTO #temp1112C
FROM #temp1112b
INNER JOIN dbo.CMC_CLCL_CLAIM
ON #temp1112b.CLCL_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
INNER JOIN dbo.CER_SYML_MSG_LOG
ON dbo.CER_SYML_MSG_LOG.SYML_CONTEXT_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
INNER JOIN dbo.CER_SYMD_MSG_DEF
ON dbo.CER_SYMD_MSG_DEF.SYMD_ID = dbo.CER_SYML_MSG_LOG.SYMD_ID
AND dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD = dbo.CER_SYML_MSG_LOG.SYMD_MSG_CD
INNER JOIN dbo.CMC_GRGR_GROUP
ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
INNER JOIN dbo.CMC_PRPR_PROV
ON dbo.CMC_CLCL_CLAIM.PRPR_ID = dbo.CMC_PRPR_PROV.PRPR_ID
INNER JOIN dbo.CMC_SBSB_SUBSC
ON dbo.CMC_CLCL_CLAIM.SBSB_CK = dbo.CMC_SBSB_SUBSC.SBSB_CK
AND dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_SBSB_SUBSC.GRGR_CK
--LEFT OUTER JOIN dbo.CMC_MCTR_CD_TRANS
-- ON dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS = dbo.CMC_MCTR_CD_TRANS.MCTR_VALUE

SELECT * , LAST_VALUE(dbo.CMC_CLCL_CLAIM.CLCL_ID) OVER (PARTITION BY dbo.CMC_GRGR_GROUP.GRGR_NAME ORDER BY dbo.CMC_GRGR_GROUP.GRGR_ID) AS CLCL_ID

FROM #temp1112C

Does anyone know what I am doing wrong?
Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-20 : 12:24:49

It depends on what error message you get.
LAST_VALUE is a SQL Server 2012 feature.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

cobbdj01
Starting Member

5 Posts

Posted - 2013-09-20 : 12:27:21

Oh, I am receiving the following error message: "LAST_VALUE" is not a recognized built-in function name. Do you know how I can accomplish this task using SQL Server 2008 R2?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-20 : 12:59:42

Not sure, but this might work for you:


select a.*
      ,b.last_clcl_id
  from #temp112c as a
       inner join (select a.clcl_id
                         ,max(b.clcl_id) as last_clcl_id
                     from #temp1112c as a
                          left outer join #temp1112c as b
                                       on b.grgr_name=a.grgr_name
                                      and b.clcl_id<a.clcl_id
                    group by a.grgr_name
                            ,a.clcl_id
                  ) as b
               on b.clcl_id=a.clcl_id

The sql has not been tested, so there is most likely syntax "hickups".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-20 : 15:32:18

[code]SELECT w.*,
(SELECT TOP(1) x.CLCL_ID FROM #Temp1112C AS x WHERE x.GRGR_NAME = w.GRGR_NAME ORDER BY x.GRGR_ID DESC) AS CLCL_ID
FROM #Temp1112C AS w;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 12:18:56

you can use APPLY operator also for achieving this

See Scenario 2
http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources