Select max run id for each day

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

SQL Server 2012 Forums

Transact-SQL (2012)

Select max run id for each day

Author

Topic

ChristopherL
Starting Member

3 Posts

Posted - 2014-08-18 : 04:56:12

Hi this is my query..
Sometimes the "job" is ran twice a day and I need to select the highest run_id for each date as a criteria, I have now idea of how to set it up..

basically a requirement that says [CODE]for each arms_run.upload_date select max(arms_run.run_id) [/CODE]

My query where I want to add this criteria

[CODE]
SELECT
100*(pos.stressed_value-pos.present_value) as Delta1,sensi_test.*,
arms_run.*, port.*, calc_param.*

FROM [ARMS].[dbo].[arms_res_pf_sens] pos with (nolock)
inner join [ARMS].[dbo].[arms_run] arms_run with (nolock)
on pos.run_id=arms_run.run_id
inner join
[ARMS].[dbo].[arms_sensitivity_test_case] sensi_case with (nolock)
on pos.sensitivity_test_case=sensi_case.id
inner join
[ARMS].[dbo].[arms_sensitivity_test] sensi_test with (nolock)
on sensi_test.id=sensi_case.sensitivity_test
inner join arms_portfolio port
on pos.portfolio_id=port.id
inner join arms_calc_param calc_param
on arms_run.calc_param=calc_param.id
where arms_run.cust_id=1

and arms_run.upload_date > GETDATE()-10

and arms_run.job_name in ('04.3 Calculate Delta1 for MB')
and port.name in ('FX_ACC Banking Book_#x#_ASIA',
'FX_ACC Banking Book_#x#_EU',
'FX_ACC Banking Book_#x#_GLOBAL',
'FX_ACC Banking Book_#x#_HEL',
'FX_ACC Banking Book_#x#_HK',
'FX_ACC Banking Book_#x#_LON',
'FX_ACC Banking Book_#x#_LUX',
'FX_ACC Banking Book_#x#_OSL',
'FX_ACC Banking Book_#x#_SHA',
'FX_ACC Banking Book_#x#_SING',
'FX_MTM_#x#_GLOBAL')
order by arms_run.upload_date asc

[/CODE]

Best Regards,
Christopher

People live their lives bound by what they accept as correct and true. That’s how they define Reality. But what does it mean to be “correct” or “true”? Merely vague concepts… Their Reality may all be a mirage. Can we consider them to simply be living in their own world, shaped by their beliefs?”

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-18 : 05:26:06

, MAX(arms_run.run_id) OVER (PARTITION BY arms_run.upload_date)

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

ChristopherL
Starting Member

3 Posts

Posted - 2014-08-18 : 06:08:23

quote:
Originally posted by SwePeso

, MAX(arms_run.run_id) OVER (PARTITION BY arms_run.upload_date)

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

Hey, thank you, so how do I implement this as a parameter?

[CODE]and (select MAX(arms_run.run_id) over (partition by arms_run.upload_date))[/CODE] something like that?

People live their lives bound by what they accept as correct and true. That’s how they define Reality. But what does it mean to be “correct” or “true”? Merely vague concepts… Their Reality may all be a mirage. Can we consider them to simply be living in their own world, shaped by their beliefs?”

ChristopherL
Starting Member

3 Posts

Posted - 2014-08-18 : 07:01:53

I tried doing this
Still doesnt work..
(note the BOLD)

[CODE]
SELECT
100*(pos.stressed_value-pos.present_value) as Delta1,sensi_test.*,
arms_run.*, port.*, calc_param.*
FROM [ARMS].[dbo].[arms_res_pf_sens] pos with (nolock)
inner join [ARMS].[dbo].[arms_run] arms_run with (nolock)
on pos.run_id=arms_run.run_id
inner join [ARMS].[dbo].[arms_sensitivity_test_case] sensi_case with (nolock)
on pos.sensitivity_test_case=sensi_case.id
inner join [ARMS].[dbo].[arms_sensitivity_test] sensi_test with (nolock)
on sensi_test.id=sensi_case.sensitivity_test
inner join arms_portfolio port
on pos.portfolio_id=port.id
inner join arms_calc_param calc_param on arms_run.calc_param=calc_param.id
where arms_run.cust_id=1 and arms_run.upload_date > GETDATE()-10
and arms_run.job_name in ('04.3 Calculate Delta1 for MB')

and arms_run.run_id = (SELECT MAX(arms_run.run_id) FROM arms_run x
WHERE x.run_id = arms_run.run_id AND x.upload_date = arms_run.upload_date)

and port.name in ('FX_ACC Banking Book_#x#_ASIA', 'FX_ACC Banking Book_#x#_EU',
'FX_ACC Banking Book_#x#_GLOBAL', 'FX_ACC Banking Book_#x#_HEL',
'FX_ACC Banking Book_#x#_HK', 'FX_ACC Banking Book_#x#_LON',
'FX_ACC Banking Book_#x#_LUX', 'FX_ACC Banking Book_#x#_OSL',
'FX_ACC Banking Book_#x#_SHA', 'FX_ACC Banking Book_#x#_SING', 'FX_MTM_#x#_GLOBAL')
order by arms_run.upload_date asc
[/code]

People live their lives bound by what they accept as correct and true. That’s how they define Reality. But what does it mean to be “correct” or “true”? Merely vague concepts… Their Reality may all be a mirage. Can we consider them to simply be living in their own world, shaped by their beliefs?”

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources