Max() question - SQL Server Forums

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

Max() question

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2010-03-09 : 23:33:42

Hi guys,

I need to get the last DatePost and last Balance based on the given CutOff Date as the parameter. Here is my actual table below:

Table


AcctNo             Balance                 DatePost      RowID
123                1000                    01/13/2010    1
123                 700                    01/31/2010    2 
123                 300                    02/25/2010    3
123                 200                    03/15/2010    4
456                2500                    02/01/2010    5
456                2000                    02/14/2010    6
456                1200                    02/19/2010    7

@CutOff='02/28/2010'
Result Needed:


AcctNo             Balance                 DatePost     RowID
123                 300                    02/25/2010     3
456                1200                    02/19/2010     7

Please help. Thank you

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 00:01:43

quote:

Originally posted by rpc86

Hi guys,

I need to get the last DatePost and last Balance based on the given CutOff Date as the parameter. Here is my actual table below:

Table


AcctNo             Balance                 DatePost      RowID
123                1000                    01/13/2010    1
123                 700                    01/31/2010    2 
123                 300                    02/25/2010    3
123                 200                    03/15/2010    4
456                2500                    02/01/2010    5
456                2000                    02/14/2010    6
456                1200                    02/19/2010    7

@CutOff='02/28/2010'
Result Needed:


AcctNo             Balance                 DatePost     RowID
123                 300                    02/25/2010     3
456                1200                    02/19/2010     7

Please help. Thank you

Please try TOP 2 and Order By clause


declare @a table
(
acctno int,
Balance   int,
DatePost datetime,
RowID int                    

)
insert @a

select 123,1000,'01/13/2010' ,1 union all
select 123,700, '01/31/2010', 2  union all
select 123 ,300,  '02/25/2010',   3 union all
select 123 ,200, '03/15/2010' ,  4 union all
select 456 ,2500, '02/01/2010',  5 union all
select 456, 2000,  '02/14/2010',   6 union all
select 456 ,1200, '02/19/2010',   7

--select * from @a

select top 2 * from @a where '02/28/2010'  between DatePost and '02/28/2010' order by DatePost desc

output
acctno Balance DatePost RowID
----------- ----------- ----------------------- -----------
123 300 2010-02-25 00:00:00.000 3
456 1200 2010-02-19 00:00:00.000 7

(2 row(s) affected)

rpc86
Posting Yak Master

200 Posts

Posted - 2010-03-10 : 00:16:29

I don't need 2 records only, data above are only partial. I believe I need max here in relation with RowID, but I don't know how to do it.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 00:26:50

quote:
Originally posted by rpc86

I believe I need max here in relation with RowID, but I don't know how to do it.

Sorry, I really not getting You..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 01:20:53

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-03-10 : 01:53:38

Is this you are looking for..
declare @a table
(
acctno int,
Balance int,
DatePost datetime,
RowID int

)
insert @a

select 123,1000,'01/13/2010' ,1 union all
select 123,700, '01/31/2010', 2 union all
select 123 ,300, '02/25/2010', 3 union all
select 123 ,200, '03/15/2010' , 4 union all
select 456 ,2500, '02/01/2010', 5 union all
select 456, 2000, '02/14/2010', 6 union all
select 456 ,1200, '02/19/2010', 7

--select * from @a

SELECT * FROM @A as a where DATEPOST = (SELECT MAX(DATEPOST) FROM @A as b
WHERE DATEPOST < '02/28/2010' AND B.ACCTNO = A.ACCTNO GROUP BY ACCTNO )

Karthik
http://karthik4identity.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:02:41

SQL 2005 solution using cross apply

SELECT t.AcctNo ,
t.Balance,
t.DatePost,
t.RowID
FROM yourtable t
CROSS APPLY(SELECT TOP 1 DatePost
            FROM YourTable
            WHERE AcctNo=t.AcctNo
            AND DatePost< @CutOffDate
            ORDER BY DatePost DESC)t1
WHERE t.DatePost = t1.DatePost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-10 : 18:57:44

quote:
Originally posted by karthik_padbanaban

Is this you are looking for..
declare @a table
(
acctno int,
Balance int,
DatePost datetime,
RowID int

)
insert @a

select 123,1000,'01/13/2010' ,1 union all
select 123,700, '01/31/2010', 2 union all
select 123 ,300, '02/25/2010', 3 union all
select 123 ,200, '03/15/2010' , 4 union all
select 456 ,2500, '02/01/2010', 5 union all
select 456, 2000, '02/14/2010', 6 union all
select 456 ,1200, '02/19/2010', 7

--select * from @a

SELECT * FROM @A as a where DATEPOST = (SELECT MAX(DATEPOST) FROM @A as b
WHERE DATEPOST < '02/28/2010' AND B.ACCTNO = A.ACCTNO GROUP BY ACCTNO )

Karthik
http://karthik4identity.blogspot.com/

I am not sure if that is what the original poster was looking for but you halped me greatly. I was looking for a way to perform exactly this type of sub-query. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources