Flags on a table

Hi all

Been thrown a bit in the deep end and need some advice..... new to db design.

I have been emailed the lay out of the table which is not a problem, they then go on to talk about flags on columns if data is inserted , updated... ect.

How do i set them up ?

The data is going to be imported in via DTS from a excel spread sheet

Thanks in advance

Rich

No clue. Can you post the exact request (without any kind of private information of course :) If they wanted the date and time of updates and inserts, that would be easy enough, but a flag (generally thought of as a imitation boolean (often using a bit) that is set to TRUE/ON when some condition is met (like disabledFlag might indicate that an account was disabled.)

This wouldn't make sense to have an updatedFlag, since it could be updated many times.

|||

Could it be that they are talking about triggers.

In a trigger you can do things like:

If UPDATE(column name)

BEGIN

END

|||

Thanks for the responses, I now have more details and I don't explain the problem properly.

What is happening is the I will have to import data via DTS each night which will be located as a file on a FTP server, so for example the customer table has a change i.e update or insert, the documentation talks about flags so that it can do the necessary change or ignore the row and move to the next one.

Does this make more sense?

Rich

|||

Do you mean that the flags should function as some kind of indicators whether a certain row should be updated or not..?

(still a bit confused about what those flags are supposed to do..)

/Kenneth

|||

Richie_C wrote:

What is happening is the I will have to import data via DTS each night which will be located as a file on a FTP server, so for example the customer table has a change i.e update or insert, the documentation talks about flags so that it can do the necessary change or ignore the row and move to the next one.

Does this make more sense?

Getting there. What I am starting to think is that they want you to put flags on rows so you can see if the row has been changed since some last export perhaps? This can be done with triggers:

--untested
create trigger tableName$update
on tableName
after update
as

begin

update tableName
set updateFlag = 1
from tableName
join inserted
on tableName.key = inserted.key

end

|||

gents

thanks for all you responses, the flags are going to be programmed in xml for checking.

sorry for wasting everybodys time!!

|||

No time wasted here. I am just interested in how you are using XML? How did this come about? Can you explain?

fixed little bit

select
a.idfield ,
a.datafield + '/' + b.datafield
from #t1 a, #t1 b
where a.idfield = b.idfield
and
a.datafield <> b.datafield
and
b.datafield <> a.datafield
Murali Pathivada
"pratclif@.co.alameda.ca.us" wrote:

> Hello All,
> My situation is as follows:
> I have a temp table similar to the one below.
> #tmp1
> idfield datafield
> 001 abcd
> 001 efgh
> 002 aabb
> 003 zzyy
> 003 pppp
> 003 qwrty
> what I want to end up with is:
> #tmp2
> idfield datafield
> 001 abcd/efgh
> 002 aabb
> 003 zzyy/pppp/qwrty
> Any suggestions on how to do this without using a cursor?
> Thanks for all help in advance,
> Cheers,
> P Ratcliff
>Thanks for the quick responses. Unfortunately, I'm still having a
problem. The second suggestion only works if I have a known number of
datafields to insert into the string.
The first suggestion, using the replace, I can't get to work. I am
using SQL 2000 not 2005 and it doesn't appear to recognise the FOR XML.
However, after elminating that, I still get an error on the sub-query
as it returns more than one value.
Any other thoughts? I really (really) don't want to use a cursor here.
Thanks again and cheers,
P Ratcliff|||If you're using SQL Server 2000, then I think
you're out of luck, unless you can do this on the client.
CREATE TABLE mytable(SomePK INT NOT NULL PRIMARY KEY,
idfield CHAR(3),
datafield VARCHAR(5))
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(1,'001','abcd')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(2,'001','efgh')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(3,'002','aabb')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(4,'003','zzyy')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(5,'003','pppp')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(6,'003','qwrty')
GO
CREATE FUNCTION dbo.ConcatDataFields(@.idfield CHAR(3))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.ConcatDataFields VARCHAR(8000)
DECLARE @.DataField VARCHAR(5)
DECLARE myCur CURSOR FOR
SELECT datafield
FROM mytable
WHERE idfield=@.idfield
ORDER BY SomePK
OPEN myCur
FETCH NEXT FROM myCur INTO @.DataField
WHILE @.@.FETCH_STATUS<>-1
BEGIN
SET @.ConcatDataFields = COALESCE(@.ConcatDataFields + '/','') +
@.DataField
FETCH NEXT FROM myCur INTO @.DataField
END
CLOSE myCur
DEALLOCATE myCur
RETURN @.ConcatDataFields
END
GO
SELECT idfield,
dbo.ConcatDataFields(idfield)
FROM mytable
GROUP BY idfield|||Dang, I was afraid of that. I don't have access to the client
application - hence the hoop-jumping. I guess I'll just have to live
with the cursor for the moment.
Thanks again for all your help.
Cheers,
P Ratcliff|||Hi Again,
If you guys are still out there, I came up with a query which will work
-it's REALLY ugly and I was wondering if y'all could help me streamline
it a bit. It does do exactly what I need without using a cursor - but
it's horribly convoluted. In fact, it's so awful, I'm ashamed to post
the code, but here it is:
--sample from original table
create table #realtable(data1 varchar(2), data2 varchar(50))
insert into #realtable(data1, data2) values ('a',NULL)
insert into #realtable(data1, data2) values ('b',NULL)
insert into #realtable(data1, data2) values ('c',NULL)
insert into #realtable(data1, data2) values ('d',NULL)
insert into #realtable(data1, data2) values ('e',NULL)
--data that needs to update original table
create table #tmp(data1 varchar(2), data2 varchar(50))
insert into #tmp(data1, data2) values('a','00001')
insert into #tmp(data1, data2) values('a','00002')
insert into #tmp(data1, data2) values('a','00003')
insert into #tmp(data1, data2) values('b','00001')
insert into #tmp(data1, data2) values('c','00001')
insert into #tmp(data1, data2) values('d','00001')
insert into #tmp(data1, data2) values('d','00002')
insert into #tmp(data1, data2) values('e','00001')
create table #test(data1 varchar(2), data2 varchar(50))
insert into #test (data1)
select distinct data1
from #tmp
declare @.data2string varchar(80),
@.data1string varchar(2),
@.count int
set @.data2string = ''
set @.count = (select distinct count(*) from #test)
while @.count > 0
begin
select @.data1string = tmp.data1
from (select data1 from #tmp) as tmp
select @.data2string = @.data2string + subq.d2 + '/'
from (select t.data1 as d1, t.data2 as d2
from #tmp t
join #test ts
on t.data1 = ts.data1
where t.data1 = @.data1string) as subq
update #test
set data2 = @.data2string
where data1 = @.data1string
set @.data2string = ''
delete from #tmp where #tmp.data1 = @.data1string
set @.count = @.count-1
end
update #realtable
set #realtable.data2 = #test.data2
from #test
where #test.data1 = #realtable.data1
select * from #realtable
drop table #test
drop table #tmp
select * from #realtable
data1 data2
a 00001/00002/00003/
b 00001/
c 00001/
d 00001/00002/
e 00001/
drop table #realtable
I'm wondering at this point if the cursor isn't a better choice. Not to
mention I've recursed myself into a singularity.
Thanks for all your help,
P Ratcliff|||Look mom, without cursors!
CREATE TABLE mytable(SomePK INT NOT NULL PRIMARY KEY,
idfield CHAR(3),
datafield VARCHAR(5))
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(1,'001','abcd')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(2,'001','efgh')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(3,'002','aabb')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(4,'003','zzyy')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(5,'003','pppp')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(6,'003','qwrty')
go
CREATE FUNCTION dbo.ConcatDataFields
(
@.idfield CHAR(3)
)
RETURNS VARCHAR(8000)
AS
BEGIN
-- something to hold the return value
declare @.ConcatDataFields VARCHAR(8000)
-- do a funky select
select @.ConcatDataFields = isnull((@.ConcatDataFields + '/'),'') +
datafield
FROM Mytable
where Mytable.idfield = @.idfield
-- return the value
return @.ConcatDataFields
END
go
SELECT idfield,
dbo.ConcatDataFields(idfield)
FROM mytable
GROUP BY idfield
hth,
Bart Holthuijsen|||Bart,
You are my hero! Thanks so much for the pointers. I've been stuck with
an application I can't change where the programmer (for reasons best
known to himself) designed the code to expect a string from the query.
Your solution allows me to remove the cursor (oh, so slow) until we can
get the app. changed.
Thanks again!
Cheers,
P Ratcliff

first day of week by Week Number

I was looking for a solution for this problem - but none of what I
found in Internet helped - so finally I made something myself ( based a
little bit on someone's idea). I hope it will be useful to someone - or
maybe u'll see some bugs in it? please report.
there 2 assumptions
1. before executing this function u SET DATEFIRST 1
2. u have already an IsoW function implemented ( found in Books
Online as example for CREATE FUNCTION)
create function fdaywk (@.year int, @.w int )
returns datetime
as
begin
declare @.date datetime
begin
set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
if datepart(dw,@.date)>4
set @.date = @.date+8-datepart(dw,@.date)
else
set @.date= @.date-datepart(dw,@.date)+1
set @.date = dateadd(wk, @.w-1, @.date)
end
return(@.date)
endHi,
your solution wont work on other servers than US or english one, using
US regional settings.
The code in cause is :
set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
You must use the short ISO date encode wich is SQL Server World Wide
portable solution :
YYYYMMDD HH:MM:SS.nnn
Try it :
SELECT CAST('20050225 11:23:17.852' AS DATETIME) AS DT
This datetime format is corresponding to CONVERT(... 121)
A +
tomek a crit :
> I was looking for a solution for this problem - but none of what I
> found in Internet helped - so finally I made something myself ( based a
> little bit on someone's idea). I hope it will be useful to someone - or
> maybe u'll see some bugs in it? please report.
> there 2 assumptions
> 1. before executing this function u SET DATEFIRST 1
> 2. u have already an IsoW function implemented ( found in Books
> Online as example for CREATE FUNCTION)
> create function fdaywk (@.year int, @.w int )
> returns datetime
> as
> begin
> declare @.date datetime
> begin
> set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
> if datepart(dw,@.date)>4
> set @.date = @.date+8-datepart(dw,@.date)
> else
> set @.date= @.date-datepart(dw,@.date)+1
> set @.date = dateadd(wk, @.w-1, @.date)
> end
> return(@.date)
> end
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************