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 DATE function need help

Author  Topic 

dipesh.ccsu
Starting Member

7 Posts

Posted - 2013-03-26 : 15:06:42
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-26 : 15:18:04
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

5072 Posts

Posted - 2013-03-26 : 15:21:23
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

7 Posts

Posted - 2013-03-26 : 16:29:30
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

7 Posts

Posted - 2013-03-26 : 16:31:01
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

7 Posts

Posted - 2013-03-26 : 16:31:01
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

5072 Posts

Posted - 2013-03-26 : 17:29:23
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-26 : 17:43:10
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
   

- Advertisement -