Need to pull the forth record only 1

[thorny00]

Have SSN and StartDate fields. I need to pull only the 4th record when the count of records per SSN >= 4.
Example:
SSN StartDate
123456789 7/1/05
7/2/05
7/3/05
7/4/05
7/5/05

I only want to show the 7/4/05.
Some SSN's have less than 4 dates and some have more than 4. How is this done? I did this in Crystal reports but need to link the results to obtain more data.
Thanks for your help and time!!

 

[gmmastros]

This is kinda ugly, and sorta slow. I'm sure there must be a better way.

Basically, I create a table variable, with a couple of helper columns. I create a column that numbers each record, starting at 1 for each ssn. Then, I delete records where the helper column is greater than 4, and finally select the max start date. To better understand this code, I recommend you put "select * from @Temp" in various places so you can understand what's going on.

I tested this on a table that had about 200 "ssn's" and approximately 200 dates per ssn. The query took about 6 seconds. If you have a relatively small amount of data, this method should be ok.

Declare @Temp
Table 	(
		RowId Integer Identity(1,1),
		TempId Integer,
		SSN Integer,
		StartDate DateTime
		)

Insert 
Into 	@Temp(SSN, StartDate)
Select 	SSN, StartDate
From 	MyTable
Order By SSN, StartDate

Declare @Mins Table (SSN Integer, MinRowId Integer)

Insert Into @Mins(SSN, MinRowId)
Select 	SSN, Min(RowId) As MinRowId
From 	@Temp 
Group By SSN

Update	A
Set		A.TempId = B.MinRowId
From	@Temp A
		Inner Join @Mins B On A.SSN = B.SSN

Update	@Temp Set TempId = RowId - TempId + 1

Delete 
From 	@Temp
Where	TempId > 4

Select 	SSN, Max(StartDate) from @Temp Group By SSN

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zhavic]

has StartDate distinct values for each SSN ?

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[thorny00]

Yes, StartDate contains the date/time someone was called, so it would be distinct.

 

[thorny00]

gmmastros/George,

I had to change the last where statement to <4, but it works fine - Thanks!
Could you please explain how and what the code is doing?

Thanks again!

 

[maswien]

A little bit ugly, but still it's a SQL statement,

select ssn, 
case when (select count(*) from myTable where ssn = t0.ssn ) > 4 
     then
        (
         select min(startdate) from myTable where ssn = t0.ssn 
         and startdate >
         ( 
         select min(startdate) from myTable where ssn = t0.ssn
         and startdate >
         (
         select min(startdate) from myTable where ssn = t0.ssn
         and startdate > ( select min(startdate) from myTable where ssn = t0.ssn  )
          )
          ))
     else
         ( select max(startdate) from myTable where ssn = t0.ssn  )
     end
from (select distinct ssn from myTable) t0

 

[gmmastros]

Thorny00,

Here's the code again, but with comments.

Create a table variable to hold the data. We do this so we can add an identity column and a helper column.

Declare @Temp
Table     (
        RowId Integer Identity(1,1),
        TempId Integer,
        SSN Integer,
        StartDate DateTime
        )

Insert 
Into     @Temp(SSN, StartDate)
Select     SSN, StartDate
From     MyTable
Order By SSN, StartDate

Create another table variable to hold the minimum row id for each ssn.

Declare @Mins Table (SSN Integer, MinRowId Integer)

Insert Into @Mins(SSN, MinRowId)
Select     SSN, Min(RowId) As MinRowId
From     @Temp 
Group By SSN

Update the first table variable to contain the min row id in the tempid field. Ex. data will be...

1 1 111-11-1111 7/1/2005
2 1 111-11-1111 7/2/2005
3 3 222-22-2222 7/1/2005
4 3 222-22-2222 7/2/2005
5 3 222-22-2222 7/3/2005

Update    A
Set        A.TempId = B.MinRowId
From    @Temp A
        Inner Join @Mins B On A.SSN = B.SSN

Create a sequential number in the TempId column. Sample data will be...

1 1 111-11-1111 7/1/2005
2 2 111-11-1111 7/2/2005
3 1 222-22-2222 7/1/2005
4 2 222-22-2222 7/2/2005
5 3 222-22-2222 7/3/2005

Update    @Temp Set TempId = RowId - TempId + 1

Now we will delete the records where TempId is greater than 4 to satisfy your requirements. If there are ssn's with only 2 records, then originally 3 and 4 don't exist so no record will be deleted for the ssn.

Delete 
From     @Temp
Where    TempId > 4

Finally, we get a list of ssn's and the max date stored in the temp table. If ssn's exist with only 2 records, then it will still be included in the output but will pull the one with the latest date.

Select     SSN, Max(StartDate) from @Temp Group By SSN

I hope this explanation helps. If not, ask more questions.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jasen]

Many people hate subqueries, but this is the simplest solution yet:

select top 1 s.StartDate from (select top 4 * 
			     from ssn_startdate 
		 	     where SSN = 1234 
			     ORDER BY StartDate ASC) as s
ORDER BY StartDate DESC

________
Remember, you're unique... just like everyone else.

 

[Jasen]

er, well that's not a total solution now that I look at what you wanted again. That doesn't check to see if the SSN has < than 4 dates with it. I think I'd setup a cursor to get that list then cycle through it.

________
Remember, you're unique... just like everyone else.

 

[vongrunt]

This also works only for single SSN...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

That said, here is another way - with UDF:

create function  get4thDate(@SSN int)
returns smalldatetime
begin
	declare @ret datetime
	select top 4 @ret=StartDate from blah where SSN=@SSN order by StartDate
	if @@rowcount < 4 set @ret = null
	return @ret
end
go

select B.SSN, B.[4thDate]
from
(	select A.SSN, dbo.get4thDate(A.SSN) as [4thDate]
	from
	(	select distinct SSN from blah
	) A
) B
where B.[4thDate] is not null

And another one - with (kind of) theta join:

select A.SSN, A.StartDate
from blah A 
inner join blah B on A.SSN=B.SSN and A.StartDate >= B.StartDate
group by A.SSN, A.StartDate
having count(*) = 4

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[TJRTech]

Wait until SQL Server 2005 and use the RANKING and WINDOWING functions...<grin>.

Find out more about them below:

Stephen Forte presenting Ranking and Windowing Functions in SQL Server 2005

http://mediaserver.aspsoft.com/blog...kingAndWindowingFunctionsInSQLServer2005.aspx

TJR