Update Question

16 Sep 2005 7:01 PM

TheWildDBA

A little background first. I import a csv file into SQL Server into a single
table in a junk database. I then stage data for import into a Sql database.
The file has an (prospect)id, flag field and a comments field. The refer
column is used as a flag to notify the sales team of a hot prospect and the
comments are related to the id. The structure is as follows:

id      flag    comments
1        T        xxxxxxx
1        T        yyyyyyy
1        T       zzzzzzz
2        F       abababab
3        T      11222222
4        T      uuuuuuuu
4        T      iiiiiiiiiiiiiiiiii

When I import this data into a 1-m table I will flag the sales rep 3 times
for a single contact (id = 1) and twice for the contact (id=4). Each record
needs to go into the database because the comments are different, but I don't
want the flag to be T for all records of the same id. Ideally I would like
to import the data as such:

id      flag    comments
1        T        xxxxxxx
1        F        yyyyyyy
1        F       zzzzzzz
2        F       abababab
3        T      11222222
4        T      uuuuuuuu
4        F      iiiiiiiiiiiiiiiiii

I created a function, which is below, but can't get it to do what I thought.
The function accepts the id and checks the table to see if that ID has
multiple records where the flag = 'T'. If true then return a 'F' else return
'T'. After running through the dataset the function updates all of id=1
flags to F and all id= 4 flags to F.

RETURNS varchar(1)
AS
BEGIN
declare @refer varchar(1)
declare @Count int
Begin
select @count = count(id) FROM [table]
    WHERE id = @id and flag = 'T'
if @count > 1
set @refer = 'F'
else
set @refer = 'T'
end
return(@count)
end

Sorry for the lengthy post. Any ideas or suggestions?

16 Sep 2005 8:37 PM

John Bell

Hi

You need some method of ranking the staged data for example an identity
column such as:

Create table mytab ( id int not null, flag char(1), comments varchar(50))
Create table mystage ( rank int not null identity, id int not null, flag
char(1), comments varchar(50))

INSERT INTO mystage ( id, flag , comments )
SELECT 1, 'T', 'xxxxxxx'
UNION ALL SELECT 1, 'T', 'yyyyyyy'
UNION ALL SELECT 1, 'T', 'zzzzzzz'
UNION ALL SELECT 2, 'F', 'abababab'
UNION ALL SELECT 3, 'T', '11222222'
UNION ALL SELECT 4, 'T', 'uuuuuuuu'
UNION ALL SELECT 4, 'T', 'iiiiiiiiiiiiiiiiii'

INSERT INTO mytab ( id, flag , comments )
SELECT s.id,
CASE WHEN EXISTS( SELECT * FROM mystage t where s.id = t.id and t.rank <
s.rank ) THEN 'F' ELSE 'T' END,
s.comments
FROM MyStage s

John

Show quote

"TheWildDBA" <TheWild***@discussions.microsoft.com> wrote in message
news:43AF08D9-8AE8-4425-BCD5-CCD93A20CD32@microsoft.com...
>A little background first. I import a csv file into SQL Server into a
>single
> table in a junk database. I then stage data for import into a Sql
> database.
> The file has an (prospect)id, flag field and a comments field. The refer
> column is used as a flag to notify the sales team of a hot prospect and
> the
> comments are related to the id. The structure is as follows:
>
> id      flag    comments
> 1        T        xxxxxxx
> 1        T        yyyyyyy
> 1        T       zzzzzzz
> 2        F       abababab
> 3        T      11222222
> 4        T      uuuuuuuu
> 4        T      iiiiiiiiiiiiiiiiii
>
> When I import this data into a 1-m table I will flag the sales rep 3 times
> for a single contact (id = 1) and twice for the contact (id=4). Each
> record
> needs to go into the database because the comments are different, but I
> don't
> want the flag to be T for all records of the same id. Ideally I would
> like
> to import the data as such:
>
> id      flag    comments
> 1        T        xxxxxxx
> 1        F        yyyyyyy
> 1        F       zzzzzzz
> 2        F       abababab
> 3        T      11222222
> 4        T      uuuuuuuu
> 4        F      iiiiiiiiiiiiiiiiii
>
> I created a function, which is below, but can't get it to do what I
> thought.
> The function accepts the id and checks the table to see if that ID has
> multiple records where the flag = 'T'. If true then return a 'F' else
> return
> 'T'. After running through the dataset the function updates all of id=1
> flags to F and all id= 4 flags to F.
>
> RETURNS varchar(1)
> AS
> BEGIN
> declare @refer varchar(1)
> declare @Count int
> Begin
> select @count = count(id) FROM [table]
> WHERE id = @id and flag = 'T'
> if @count > 1
> set @refer = 'F'
> else
> set @refer = 'T'
> end
> return(@count)
> end
>
> Sorry for the lengthy post. Any ideas or suggestions?
>
>
>

16 Sep 2005 8:45 PM

--CELKO--

>> The function accepts the id and checks the table to see if that id has multiple records [sic] where the flag = 'T'. If true then return a 'F' else return 'T'. <<

Your sample data showed (prospect_id = 1) having only one (flag = 'T'),
so I am not sure what you want. But you can use this to find the
mixture of flag values where there is more than one row per prospect:

SELECT prospect_id,
        CASE WHEN MIN(flag) = 'T' AND MAX(flag) = 'T'
                   THEN ' All TRUE'
                   WHEN MIN(flag) = 'F' AND MAX(flag) = 'F'
                   THEN ' All FALSE'
                   ELSE 'Mixed' END
' FROM StagingTable
GROUP BY prospect_id
HAVING COUNT(prospect_id) > 1;

The HAVING clause will skip (prospect_id = 2) and (prospect_id = 3)
because they have only one row.