October 28, 2008 at 12:00 pm
I need to return for each cuslink in the table created below the record with the most recent date where the site does not = sites (31,30,32)
returning ...
CUSLINK SEQUENCE EFFDATE SITE
2233 2 2007-11-11 00:00:00.000 4
3333 11 2007-02-02 00:00:00.000 16
5432 21 2007-06-06 00:00:00.000 9
6788 5 2007-11-25 00:00:00.000 14
2299 2 2007-11-11 00:00:00.000 4
3389 11 2007-02-02 00:00:00.000 16
5932 3 2007-08-22 00:00:00.000 16
6578 5 2007-11-25 00:00:00.000 14
and then in a seperate process ....
for each cuslink return the record with the highest sequence where the site does not include a group of sites (31,30,32)
returning ...
CUSLINK SEQUENCE EFFDATE SITE
2233 3 2008-10-02 00:00:00.000 22
3333 11 2007-02-02 00:00:00.000 16
5432 21 2007-06-06 00:00:00.000 9
6788 5 2007-11-25 00:00:00.000 14
2299 3 2008-10-02 00:00:00.000 22
3389 11 2007-02-02 00:00:00.000 16
5932 21 2007-06-06 00:00:00.000 9
6578 5 2007-11-25 00:00:00.000 14
create table #list(
[cuslink] int,
[sequence] int,
[effdate] datetime,
[site] varchar(3))
select * from #list
insert into #list values (2233, 1,'12/12/2006',004)
insert into #list values (2233, 2,'11/11/2007',004)
insert into #list values (2233, 3,'10/2/2008',022)
insert into #list values (3333, 9,'09/1/2007',021)
insert into #list values (3333, 10,'1/1/2007',013)
insert into #list values (3333, 11,'2/2/2007',016)
insert into #list values (5432, 21,'6/6/2007',009)
insert into #list values (5432, 22,'7/1/2007',032)
insert into #list values (5432, 23,'9/3/2007',031)
insert into #list values (5432, 3,'8/22/2007',016)
insert into #list values (6788, 4,'5/8/2007',012)
insert into #list values (6788, 5,'11/25/2007',014)
insert into #list values (6788, 6,'12/25/2007',030)
insert into #list values (2299, 1,'12/12/2006',004)
insert into #list values (2299, 2,'11/11/2007',004)
insert into #list values (2299, 3,'10/2/2008',022)
insert into #list values (3389, 9,'09/1/2007',021)
insert into #list values (3389, 10,'1/1/2007',013)
insert into #list values (3389, 11,'2/2/2007',016)
insert into #list values (5932, 21,'6/6/2007',009)
insert into #list values (5932, 22,'7/1/2007',032)
insert into #list values (5932, 23,'9/3/2007',031)
insert into #list values (5932, 3,'8/22/2007',016)
insert into #list values (6578, 4,'5/8/2007',012)
insert into #list values (6578, 5,'11/25/2007',014)
insert into #list values (6578, 6,'12/25/2007',030)
I've been working with the MAX command, but had no success.
Any thoughts would be appreciated..
October 28, 2008 at 12:08 pm
What would the output be for the sample data given?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 1:20 pm
I've added the needed output to original post.. thanks!
October 28, 2008 at 1:36 pm
Try this code:
select
a.cuslink,
a.sequence,
a.effdate,
a.site
from
#list a
inner join (
select
cuslink,
max(effdate) as maxdate
from
#list
where
site not in (31,32,33)
group by
cuslink) df
on (a.cuslink = df.cuslink
and a.effdate = df.maxdate)
order by
a.cuslink
October 28, 2008 at 3:25 pm
Thanks.. your code worked great. However when I tried to adapt it to look at another field (included in the table building script below)to apply different criteria using an additional field ([entercd]varchar(3)) which contains both numbers and some text data, I get the error
"Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'LLW' to a column of data type int.
"LLW" is one of the non-numeric values found occassionally in the "entercd" field.
Is there a "cast" or "convert" process to work around this problem?
create table #list1(
[cuslink] int,
[sequence] int,
[effdate] datetime,
[site] varchar(3),
[entercd]varchar(3))
select * from #list1
delete from #list1
insert into #list1 values (2233, 1,'12/12/2006',004,'LLW')
insert into #list1 values (2233, 2,'11/11/2007',004,'002')
insert into #list1 values (2233, 3,'10/2/2008',022,'003')
insert into #list1 values (3333, 9,'09/1/2007',021,'006')
insert into #list1 values (3333, 10,'1/1/2007',013,009)
insert into #list1 values (3333, 11,'2/2/2007',016,013)
insert into #list1 values (5432, 21,'6/6/2007',009,014)
insert into #list1 values (5432, 22,'7/1/2007',032,016)
insert into #list1 values (5432, 23,'9/3/2007',031,015)
insert into #list1 values (5432, 3,'8/22/2007',016,'WEN')
insert into #list1 values (6788, 4,'5/8/2007',012,'WEN')
insert into #list1 values (6788, 5,'11/25/2007',014,'TEN')
insert into #list1 values (6788, 6,'12/25/2007',030,032)
insert into #list1 values (2299, 1,'12/12/2006',004,'TEN')
insert into #list1 values (2299, 2,'11/11/2007',004,032)
insert into #list1 values (2299, 3,'10/2/2008',022,030)
insert into #list1 values (3389, 9,'09/1/2007',021,030)
insert into #list1 values (3389, 10,'1/1/2007',013,'TEN')
insert into #list1 values (3389, 11,'2/2/2007',016,'TER')
insert into #list1 values (5932, 21,'6/6/2007',009,'REL')
insert into #list1 values (5932, 22,'7/1/2007',032,022)
insert into #list1 values (5932, 23,'9/3/2007',031,022)
insert into #list1 values (5932, 3,'8/22/2007',016,023)
insert into #list1 values (6578, 4,'5/8/2007',012,032)
insert into #list1 values (6578, 5,'11/25/2007',014,034)
insert into #list1 values (6578, 6,'12/25/2007',030,033)
select
a.cuslink,
a.sequence,
a.effdate,
a.site,
a.entercd
from
#list1 a
inner join (
select
cuslink,
max(sequence) as maxsequence
from
#list1
where
entercd not in (031,032,033)
group by
cuslink) df
on (a.cuslink = df.cuslink
and a.sequence = df.maxsequence)
order by
a.cuslink
October 28, 2008 at 3:58 pm
I think the issue is that if you're looking at a char() or varchar() column, you always want to specify the data in quotes.
For example:
entercd not in ('031','032','033')
October 28, 2008 at 4:06 pm
Easy fix:
select
a.cuslink,
a.sequence,
a.effdate,
a.site,
a.entercd
from
#list1 a
inner join (
select
cuslink,
max(sequence) as maxsequence
from
#list1
where
entercd not in ('031','032','033') -- put single quotes around the "numbers" to make them strings
group by
cuslink) df
on (a.cuslink = df.cuslink
and a.sequence = df.maxsequence)
order by
a.cuslink
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply