MS Access LAST equivalent in SQL Server

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 2000 Forums

Transact-SQL (2000)

MS Access LAST equivalent in SQL Server

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-09-25 : 11:54:05

They took my floaties off and shoved me in the deep end, so my posts won't just be in the YAK coral anymore!

I am trying to write a SQL query that won't pull duplicates. However, I don't think DISTICT will work since I need the most recent date.

SELECT customer_id, current_balance, last(active_date) actv_date
FROM aging_balance_table
WHERE current_balance >0

Any help you can offer, will be greatly appreciated!

Thanks,

*************************
Got some code from Rob. Can anyone help?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-25 : 12:07:48

LECT customer_id, current_balance, max(active_date) actv_date
FROM aging_balance_table
WHERE current_balance >0
group by customer_id, current_balance

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-09-25 : 12:14:47

So (specifically with dates) MAX would pull the most recent value and MIN would pull the oldest?

What about the current_balance? If there happen to be two balances on the same day, will it pull the largest current_balance associated with the date? If there is only one balance per day, do even need to put MAX on current_balance?

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 12:50:48

Wouldn't you want to add them together?

SUM(Current_Balance), COUNT(*) AS Number_of_Rows

Shows how many rows made up the balance...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-09-25 : 13:05:00

No. The current balance reflects all credits and debits on the account <I didn't create the logic on this table, I only have to pull the data>. The time is set to 00:00:000, so when I select MAX(date) and there are two dates the same, I want to pull the largest balance.

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 13:19:54

Use MAX again on the balance....but what if I paid DOWN my balance?

Can you post the DDL of the table with some sample data?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-25 : 13:26:55

SELECT a.customer_id,

(select top 1 b.current_balance from aging_balance_table b
where b.customer_id=a.customer_id and b.current_balance>0
order by b.active_date desc, b.current_balance desc),

(select top 1 b.active_date from aging_balance_table b
where b.customer_id=a.customer_id and b.current_balance>0
order by b.active_date desc, b.current_balance desc) actv_date

FROM aging_balance_table a

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-25 : 13:36:40

LOL Forgot to add the grouping:

group by a.customer_id

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-09-25 : 14:08:41

quote:
Originally posted by X002548

Use MAX again on the balance....but what if I paid DOWN my balance?

Can you post the DDL of the table with some sample data?

If the balance is on the same day and I cannot differentiate which came first because there is no time value, then I want to take the worst-case scenario.

I am embarrassed to say, I have no idea how to post the DDL of the table with sample data.

*************************
Got some code from Rob. Can anyone help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-25 : 14:19:55

Ledell, see the third post in this link for how to post the DDL and sample data:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29085[/url]

Tara

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-09-25 : 15:22:13

Thanks for all your help ... I won't be posting the DDL and sample data, because I got it figured out. But I did want to say Thanks!

*************************
Got some code from Rob. Can anyone help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources