eliminating dups 1

[wfd1bdb]

I have problem. Below is a set of data I am working with and I only need the record with the newest date. I used this script with MAX(assessdate1) but I am still getting 2 or more dates. I thought if MAX is used then you would only get the most recent date. Thanks ahead of time for for answering.

WFD1BDB

Select account, max(assessdate1), household, dependents, income from clcharity
where (income < 20420 and household = 1)-- or (income < 27380 and household =2)
group by account, assessdate1, household, dependents, income
order by account

199312 2001-07-01 00:00:00.000 1 0 5808
199312 2004-12-15 00:00:00.000 1 0 6768
199510 2001-07-01 00:00:00.000 1 NULL 6540
199510 2007-12-07 00:00:00.000 1 0 7476
199822 2001-07-01 00:00:00.000 1 NULL 6612
199860 2001-07-01 00:00:00.000 1 NULL 13000
199860 2004-08-02 00:00:00.000 1 0 17000
199860 2004-10-25 00:00:00.000 1 0 17000

 

[jbenson001]

The problem is you need some unique identifier for the rows, since the account number is not unique. Then you can try something like this:

select A.*
from clcharity A
inner join
(    select [b][red]SomeID[/red][/b], Max(assessdate1) as maxDate
    from clcharity
    group by [b][red]SomeID[/red][/b]
) B on A.SomeID=B.SomeID AND A.assessdate1=B.maxDate

 

[gmmastros]

The reason you are getting duplicates is because of your group by.

Look at this data...

[tt][blue]
199312 2001-07-01 00:00:00.000 1 0 [!]5808[/!]
199312 2004-12-15 00:00:00.000 1 0 [!]6768[/!]
[/blue][/tt]

That's the income column, right? But the value is different. Since you have this column in the group by, and the data is different, you get it returned in your output. Makes sense, so far?

You cannot simply remove this column from the group by because you will get a different error. But don't despair, there is a way to construct the query to return that data you are expecting.

The trick here is to create a query that returns the account number and the max date. This query will become a derived table for a larger query. You then link the derived table to the outer query based on the account AND the date. This will eliminate the record you don't want.

First, the inner query:

         Select account, Max(Assessdate1) As Date1
         From   clcharity
         Group By Account

When you run this query, you will get exactly 1 record for each account. This will also show you the max date for each account. Now, we simply make this a derived table and link it back to your original query, like this...

Select account, 
       [!]MaxDate.Date1 As assessdate1, [/!]
       household, 
       dependents, 
       income 
from   clcharity
       Inner Join [!]([/!]
         [blue]Select account, Max(Assessdate1) As Date1
         From   clcharity
         Group By Account[/blue]
         [!]) As MaxDate[/!]
         [green]On clcharity.account = MaxDate.account
         and clcharity.assessdate1 = MaxDate.Date1[/green]
where   (income < 20420 and household = 1)-- or (income < 27380 and household =2)
order by account

A couple things to notice.

1. Notice the part in blue. It's the same as the previous query.

2. Notice that I am returning MaxDate.Date1 MaxDate is the alias I used for the derived table. Date1 is the alias I used for the Max(assessdate1) within the derived table.

3. Notice the part in RED. The shows you how to make a derived table.

4. Notice the part in GREEN. That shows you how to join the original table to the derived table.

I know there is a lot to learn based on the information I provided here. If there is anything you don't understand, just ask and I will explain it to you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

WOW I was way off...

 

[LNBruno]

I was thinking maybe a self-join?

  SELECT a.account
       , MAX(a.assessdate1)
       , b.household
       , b.dependents
       , b.income
    FROM #clcharity a
    JOIN #clcharity b
      ON a.account = b.account
   WHERE (income < 20420 and household = 1)-- or (income < 27380 and household =2)
GROUP BY a.account, b.household, b.dependents, b.income, b.assessdate1
  HAVING MAX(a.assessdate1) = b.assessdate1
ORDER BY a.account

< M!ke >
[small]Don't believe everything you think.[/small]

 

[gmmastros]

WOW I was way off...

What do you mean? Our queries are very similar. The only real difference is in the explanation.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

That, and I forgot the extra stuff in the where clause..

 

[SQLSister]

Excellent explanation

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

There is a small problem with the query I posted above.

Select [!]clcharity.[/!]account,
       MaxDate.Date1 As assessdate1,
       household,
       dependents,
       income
from   clcharity
       Inner Join (
         Select account, Max(Assessdate1) As Date1
         From   clcharity
         Group By Account
         ) As MaxDate
         On clcharity.account = MaxDate.account
         and clcharity.assessdate1 = MaxDate.Date1
where   (income < 20420 and household = 1)-- or (income < 27380 and household =2)
order by account

Since the account column exists in the clcharity table AND the derived table, you must specify which one to return. In this case, we are inner joining the 2 tables, so it doesn't really matter which one you use.

Sorry for the confusion.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wfd1bdb]

George,

I have been analyzing your explanation and it is great. I went through and added the "clcharity." to the query to keep it straight in my mind so that eliminated the confusion. However, I am getting this error:

Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

And I am thinking it is derived from assessdate1 field in the inner query and the MaxDate.Date1 field in the outer query. any ideas on how to resolve. Using Convert.....maybe?

Wfd1bdb


--outer query
SELECT clcharity.account, MaxDate.Date1 as assessdate1, clcharity.household, clcharity.income
FROM clcharity
INNER Join
--inner query
(SELECT clcharity.account, Max(assessdate1) as Date1
FROM clcharity
Group By clcharity.account) as MaxDate
ON clcharity.account=MaxDate.Date1 and
clcharity.assessdate1=MaxDate.Date1
WHERE clcharity.income <= 20240
ORDER BY clcharity.account

 

[gmmastros]

ON clcharity.account=[!][s]MaxDate.Date1[/s][/!] [blue]MaxDate.Account[/blue] and
   clcharity.assessdate1=MaxDate.Date1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom