Bleh, back to drawing board - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Bleh, back to drawing board

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 14:00:47

select count(1) as record_count,Resource_entity_id,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time
from resource_data_profile_txt
group by Resource_entity_id,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time
order by count(1)

returns all records 1 record per group. So those columns combined make it each record in _txt unique

select
Resource_entity_id,
convert(int,'') as Resource_data_seq,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time
into
#resource_admin_temp
from resource_data_profile_txt
order by Resource_entity_id,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time

declare @new int, @last varchar(5000)
select @new = 1, @last = 0

update #resource_admin_temp
set
@new = Resource_data_seq = case when @last = Resource_entity_id then @new + 1 else 1 end,
@last = Resource_entity_id

select count(47),Resource_Data_Seq,Resource_Entity_Id
from #resource_admin_temp
group by Resource_Data_Seq,Resource_Entity_Id
order by count(47) desc

the select statement gives me 139 incidents where 2 records are assigned the same Resource_Data_Seq,Resource_Entity_Id.

I've got no clue why it's doing that.

select * from #resource_admin_temp where
Resource_Data_Seq = 1
and Resource_Entity_Id = '0855182101090'

gives back 2 records that have different Applicable_Strat_Unit values. when they should have each been assigned a unique resource_data_seq for that record.

Any ideas? Bleh, lunch time

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 14:11:24

quote:

select count(1) as record_count,Resource_entity_id,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time
from resource_data_profile_txt
group by Resource_entity_id,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time
order by count(1)

does not mean

quote:
returns all records 1 record per group. So those columns combined make it each record in _txt unique

try


select
    count(*),
    Resource_entity_id, 
    Resource_data_type, 
    New_Interpretation_Date_Time, 
    Applicable_Strat_Unit, 
    New_Revised_Date_Time 
    from resource_data_profile_txt 
    group by Resource_entity_id, 
    Resource_data_type, 
    New_Interpretation_Date_Time, 
    Applicable_Strat_Unit, 
    New_Revised_Date_Time 
from
    resource_data_profile_txt
group by 
    Resource_entity_id, 
    Resource_data_type, 
    New_Interpretation_Date_Time, 
    Applicable_Strat_Unit, 
    New_Revised_Date_Time 
    from resource_data_profile_txt 
    group by Resource_entity_id, 
    Resource_data_type, 
    New_Interpretation_Date_Time, 
    Applicable_Strat_Unit, 
    New_Revised_Date_Time 
having
    count(*) > 1

This is why I asked you in the old thread what your PK was. The tricky update will only work if the temp table is ordered properly.

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 16:20:20

Hmm, ended up being a logical error in the data

drop table #resource_admin_temp
create table #resource_admin_temp
(idcol int identity(1,1),
Resource_entity_id varchar(15),
Resource_data_seq int,
Resource_data_type varchar(150),
New_Interpretation_Date_Time datetime,
Applicable_Strat_Unit varchar(150),
New_Revised_Date_Time datetime)
insert into #resource_admin_temp
select
Resource_entity_id,
convert(int,'') as Resource_data_seq,
Resource_data_type,
New_Interpretation_Date_Time,
Applicable_Strat_Unit,
New_Revised_Date_Time

from resource_data_profile_txt
order by Resource_entity_id asc,
Resource_data_type,
Applicable_Strat_Unit desc

declare @new int, @last varchar(5000)
select @new = 1, @last = 0

update #resource_admin_temp
set
@new = Resource_data_seq = case when @last = Resource_entity_id then @new + 1 else 1 end,
@last = Resource_entity_id

For some strange reason, which I'm still researching cause it just seems weird, the Applicable_Strat_Unit needed the keyword DESC for it to work.
[edit]I added the incrementing IDCOL just for trouble shooting pruposes.. let me look at exactly what order the data was in [/edit]

Problem solved... Thanks muchly for your help Page47

-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/02/2002 16:25:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources