SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MAX DATE function need help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dipesh.ccsu
Starting Member

USA
7 Posts

Posted - 03/26/2013 :  15:06:42  Show Profile  Reply with Quote
Acct# Act Dt number
123 6/21/2012 2
123 6/21/2012 3
123 6/15/2011 5


I am trying to get the MAX "ACT DT" for thousands of accounts. Above IS JUST AN EXAMPLE FOR ONE ACCOUNT. So, I used the max function for the act dt and above is the data it return but as one can see the max function did not work. Could anyone suggest how I can get the max date for an account but also if the account has two or more max date I would want to retrieve that as well?

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/26/2013 :  15:18:04  Show Profile  Reply with Quote
Why do you say it did not work? What is the query you are using? What is the data type of ACT DT column? In the example that you showed, there are two rows with the same date. Do you want to get both those rows for Acct# 123, or just one of those rows? If only one which one do you want to get?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/26/2013 :  15:21:23  Show Profile  Visit russell's Homepage  Reply with Quote
The MAX function works fine. Your query is bad.

Should show us what you've tried so far. Here's one way to do what you're after.
SELECT	t.[Acct#], t.[Act Dt], t.[number]
FROM	yourTable t
JOIN	(
	SELECT	[Acct#], max([Act Dt]) [Act Dt]
	FROM	yourTable
	GROUP BY [Acct#]
) x
On	x.[Acct#] = t.[Acct#]
And	x.[Act Dt] = t.[Act Dt]
Go to Top of Page

dipesh.ccsu
Starting Member

USA
7 Posts

Posted - 03/26/2013 :  16:29:30  Show Profile  Reply with Quote
quote:
Originally posted by James K

Why do you say it did not work? What is the query you are using? What is the data type of ACT DT column? In the example that you showed, there are two rows with the same date. Do you want to get both those rows for Acct# 123, or just one of those rows? If only one which one do you want to get?



Hi Jame,

yes i am trying to retrieve first two rows for that example.

below is my sql script.

CREATE TABLE OCAM_LINKINGS_UPDATES AS (

SELECT V.Acct_Bill_Sys,
V.Acct_Bill_Subs,
V.Acct_Uniq,
V.Acct_Dim_,
V.Cust_No,
CAST(NULL AS VARCHAR (10)) AS New_Cust_#,
CAST(NULL AS VARCHAR (10)) AS Cust_Compare,
V.Acct_Stat_Cd,
V.Acct_Cust_Type_Cd,
V.Acct_Cust_End_Dt,
MAX(V.Acct_Activation_Dt) AS ACT_DT



FROM eACCT_HIST_V V,CUST A


WHERE V.Acct_Uniq_Cust_No = A.Acct_Uniq_Cust_No

GROUP BY 1,2,3,4,5,6,7,8,9,10

)
WITH DATA


PRIMARY INDEX (Acct_Uniq,Acct_Dim_Start)
Go to Top of Page

dipesh.ccsu
Starting Member

USA
7 Posts

Posted - 03/26/2013 :  16:31:01  Show Profile  Reply with Quote
Thanks for your input i posted what i have done so far.

quote:
Originally posted by russell

The MAX function works fine. Your query is bad.

Should show us what you've tried so far. Here's one way to do what you're after.
SELECT	t.[Acct#], t.[Act Dt], t.[number]
FROM	yourTable t
JOIN	(
	SELECT	[Acct#], max([Act Dt]) [Act Dt]
	FROM	yourTable
	GROUP BY [Acct#]
) x
On	x.[Acct#] = t.[Acct#]
And	x.[Act Dt] = t.[Act Dt]


Go to Top of Page

dipesh.ccsu
Starting Member

USA
7 Posts

Posted - 03/26/2013 :  16:31:01  Show Profile  Reply with Quote
Thanks for your input i posted what i have done so far.

quote:
Originally posted by russell

The MAX function works fine. Your query is bad.

Should show us what you've tried so far. Here's one way to do what you're after.
SELECT	t.[Acct#], t.[Act Dt], t.[number]
FROM	yourTable t
JOIN	(
	SELECT	[Acct#], max([Act Dt]) [Act Dt]
	FROM	yourTable
	GROUP BY [Acct#]
) x
On	x.[Acct#] = t.[Acct#]
And	x.[Act Dt] = t.[Act Dt]


Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/26/2013 :  17:29:23  Show Profile  Visit russell's Homepage  Reply with Quote
Where did you get syntax like that? What RDBMS are you using?

The query I gave you works, you can modify it for your column/table names.

If you want to create a table in MS SQL Server with the output of a SELECT statement, then the syntax is

SELECT col1, col2, col3...etc
INTO NewTable
FROM Tables
WHERE etc.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/26/2013 :  17:43:10  Show Profile  Reply with Quote
This is Microsoft SQL Server code, so I don't know if this will help, but here's another way to do it in t-sql


DECLARE @Table TABLE(AcctNum tinyint,ActDt date,Number tinyint)

INSERT INTO @Table
VALUES (123,'6/21/2012',2),
(123,'6/21/2012', 3),
(123,'6/15/2011',5)
SELECT t1.AcctNum,t1.ActDt,t1.Number
FROM
(select t.AcctNum,t.ActDt,t.Number
,RANK() over(partition by t.AcctNum order by Actdt DESC)as rnk
from @Table t
) t1
WHERE t1.rnk = 1

Jim


Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000