question to make a sinlge record as two

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

General SQL Server Forums

New to SQL Server Programming

question to make a sinlge record as two

Author

Topic

aammi
Starting Member

1 Post

Posted - 2008-02-14 : 22:15:32

HI if I have a recrod in a plain text file , and it has some colums information including FromDate and ThruDates if the date range is over 30 days, Then the record should split and becomes 2 records rather then one.

for example:
if i have MemberID=255 FromDate 07/01/06 and ThruDate = 08/25/06

than
that single record should be saved as two records

like
Record#1 FromDate ThruDate MemberID
1 07/01/06 07/30/06 225
2 08/31/06 08/25/06 225

Well the file is a fixed length file.
On a web page. Page is uploaded.
when the fixed length file is uploaded it converted into the xml file.
After the xml file goes through the validation, it gets ready for the database.
Each field in the fixed length file was a xml node and sent to the database.

Now what I have to check from the sqlManagement studio. Insert a query with the test query as I decribed earlier, and instead of it get to the database it should be splited into two record instead of one and get in the database table. And if dates are in between 30 days, then the recrod go into table with out any other issue.

georgev
Posting Yak Master

122 Posts

Posted - 2008-02-15 : 08:17:37

[CODE]
CREATE TABLE gvTemp (
id int identity(1,1)
, from_date datetime NOT NULL
, thru_date datetime NOT NULL
)
GO

CREATE TRIGGER hereWeGo
ON gvTemp
INSTEAD OF INSERT
AS
BEGIN
DECLARE @fromDate datetime
, @thruDate datetime

SELECT @fromDate = from_date
, @thruDate = thru_date
FROM inserted

IF DateDiff(dd, @fromDate, @thruDate) > 30 BEGIN
INSERT INTO gvTemp(from_date, thru_date)
SELECT @fromDate
, DateAdd(dd, 30, @fromDate)
UNION
SELECT DateAdd(dd, 31, @fromDate)
, @thruDate
END
ELSE BEGIN
INSERT INTO gvTemp(from_date, thru_date)
VALUES(@fromDate, @thruDate)
END

END
GO

INSERT INTO gvTemp(from_date, thru_date)
VALUES ('20070101', '20070102')

SELECT * FROM gvTemp

INSERT INTO gvTemp(from_date, thru_date)
VALUES ('20070201', '20070401')

SELECT * FROM gvTemp

DROP TABLE gvTemp
[/CODE]

Please note that this only works for single valued inserts; you will have to modify it to deal with this yourself.

Also; what is supposed to happen when the dates are over 60, 90, even 120 days?

George
<3Engaged!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 08:34:20

try like this for any number of inserts:-


CREATE TABLE gvTemp (
   id int identity(1,1)
 , from_date datetime NOT NULL
 , thru_date datetime NOT NULL
)
GO

CREATE TRIGGER hereWeGo
  ON gvTemp
  INSTEAD OF INSERT
AS
BEGIN
  DECLARE @fromDate datetime
        , @thruDate datetime,
          @Count int

  SELECT @fromDate = from_date
       , @thruDate = thru_date
  FROM   inserted
 SELECT @Count=(DateDiff(dd, @fromDate, @thruDate)/30)+CASE WHEN (DateDiff(dd, @fromDate, @thruDate)%30) >0 THEN 1 ELSE 0 END

  WHILE @Count >0 AND DateAdd(dd, 30, @fromDate)<@thruDate
 BEGIN
    INSERT INTO gvTemp(from_date, thru_date)
    SELECT @fromDate
         , DateAdd(m, 1, @fromDate) - DAY(@fromDate)
    SELECT @fromDate= (DateAdd(m, 1, @fromDate) - DAY(@fromDate))+1,@Count=@Count-1
  END
 INSERT INTO gvTemp(from_date, thru_date)
 SELECT @fromDate, @thruDate
  

END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources