October 1, 2007 at 10:31 am
Folks,
this is a silly little problem.. and there's lot of ways I could do it.. butI was hoping to take some elegant sort of approach to it.
Basically I have a table that contains customer info.. and when any change happens to any recored in the table I need to output an xml file to send to another system to inform it of the updated data.
The file name needs to be of the form
SENDER is just a string like "MySystem"
FILENUMBER needs to be a 5 digit number that is unique on the day that the file is sent...
JDATE is a three digit date, kinda like a julian date.. the number of days since the 1st of Jan this year...
I'm not sure of a neat way to get the JDATE... and rather than setting up a table with a counter to use to generate the FILENUMBER I was hoping to come up with something a little bit less clumsy...
I'd be recording the files send in a table..
(Filename, Filenumber, CreationDateTime)
so I was thinking I could get the FILENUMBER with a select clause inside the insert...
Insert into FileLog
Select "MySystem_" +
((Select max(filenumber)) +1
Where
CreationDateTime > CAST(FLOOR(CAST(getdate() AS float))AS datetime) ) +
'.' +
(some way to get the three digits),
(Select max(filenumber)) +1, getdate()
So, I was wondering if anyone could tell me what they thought of that approach? (I know I'd probably have to watch out for nulls too, I can add that)
And maybe gimme a hint as to how I'd get this JDATE thing worked out.....
I would have thought that this kind of thing is done all the time...
Any thoughts greatly appreciated...
Vida...
October 1, 2007 at 10:41 am
How about something simple like adding a ident column into a table? Insert into that table. Fetch scope_identity() and user for filename. Delete the row.
I doubt you'll get something elegant... because this is just some dirty stuff that needs to get done sometimes.
October 1, 2007 at 11:36 am
Ok, being as smooth as I could, here is a table and a function that will auto-generate files for you.
You then just need to insert a default value and return the row you just added.
I think you could use OUTPUT, but I made this very quickly in SQL 2000 so I cannot try it.
CREATE TABLE [dbo].[FileUpload] (
[FileUploadID] [int] IDENTITY (1, 1) NOT NULL ,
[UploadDate] [datetime] NOT NULL ,
[FileNumber] [int] NOT NULL ,
[FileName] AS ('MySystem_' + convert(varchar,[FileNumber]))
) ON [PRIMARY]
GO
CREATE FUNCTION dbo.NextFileNum(@Date DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @Ret INT
SET @ret = (SELECT COALESCE(MAX(FileNumber),0) + 1 FROM FileUpload WHERE UploadDate = @Date)
RETURN(@Ret)
END
GO
ALTER TABLE [dbo].[FileUpload] ADD
CONSTRAINT [DF__FileUploa__Uploa__2E9BCA86] DEFAULT (convert(datetime,floor(convert(float,getdate())))) FOR [UploadDate],
CONSTRAINT [DF_FileUpload_FileNumber] DEFAULT ([dbo].[NextFileNum](convert(datetime,floor(convert(float,getdate()))))) FOR [FileNumber],
CONSTRAINT [PK_FileUpload] PRIMARY KEY CLUSTERED
(
[FileUploadID]
) ON [PRIMARY]
GO
--Test It
INSERT FileUpload (UploadDate) VALUES (Default)
SELECT * FROM FileUpload WHERE FileUploadID = @@Identity
GO
October 1, 2007 at 6:57 pm
Use datepart to get the day of year:
SELECT datepart(dy, getdate())
Eddie Wuerch
MCM: SQL
October 1, 2007 at 8:08 pm
Doesn't work if there are more than 1 export in the same day. And I would plan for that possibility, regardless of the current situation and requirements.
Datetime has the same problem. Could have 2 exports at the same time (however unlikely that can be). Identity solves it all.
October 2, 2007 at 6:10 am
Jeez Guys,
This is a much better response than I thought I'd get for this question. Thanks for taking the time.
I'm afraid that its as Ninja's_RGR'us said...
I'll have more than one export in a day... and I can't use like HHMMS (hour, min, sec) becuase there could indeed be a load of exports in one go...
This would happen when I get a batch of 300 customers to come into the system.. from a file say...
I'd be hoping to end up with a progression like
MySystem_0001.123
MySystem_0002.123
MySystem_0003.123
MySystem_0001.124
MySystem_0002.124
Vida
October 2, 2007 at 6:59 am
Ok, so I;ve come up with a really simple way of doing this.. but would appreciate some critiques.. is it too simpple?
Table is like
CREATE TABLE [dbo].[Way4Files](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FileDate] [datetime] NULL,
[daynumber] [varchar](50) NULL,
[filenumber] [int] NULL
) ON [PRIMARY]
And then the insert is:
insert into way4files
select getdate(), (SELECT datepart(dy, getdate())) ,
(select isnull(max(filenumber),0)+1
from way4files
where daynumber = (SELECT datepart(dy, getdate())))
select 'MyCompany_' +CONVERT(varchar(5),filenumber ) + '.' + CONVERT(varchar(3),daynumber) from way4files where id = (select scope_identity())
It works kinda nice.. you can insert a few records.. then go into the table and change the daynumber to a lower number (to make is took like it was from a few days ago) and insert some more to see that the counter resets per day etc...
So, anyone see any holes in this please?
Thanks
Vida
October 2, 2007 at 8:22 am
I think that's a pretty good solution and it was about what I'd suggest. Use a unique number of reach one, and include the datetime.
We used to do something similar, but we were tracking inserts, not exports. Use date and time and a unique number for each.
October 2, 2007 at 8:34 am
As Steve said, use the datetime or whatever is usefull to recognize that file from a human standpoint, then use identity to insure uniqueness. Sometimes there are very simple solutions to a problem. We don't have to reinvent the wheel everytime we code something!
October 2, 2007 at 8:43 am
Thanks very much for the advice there guys,
Its great to have some people to bounce things off once in a while...
Much appreciated...
Vida.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply