March 19, 2007 at 1:14 pm
I have an issue whereby I need to split records with a duration > 30 mins, creating a new record for all time over the 30 mins.
All records have a date field attached so that would need to be reflected in the new record, could anyone give me a starting point for the code?.
Thanks.
Nick
March 19, 2007 at 2:35 pm
Can you give us a starting point?
Can you show us some sample data and the required results after the insert statement has run?
March 20, 2007 at 1:50 am
Yes, table structure and sample data would be nice... also, please specify what to do with records longer than 1 hour - should they be split into as many 30-minute records as necessary, or do you want to make the split at first 30 minutes and then the rest, no matter how long it is?
March 20, 2007 at 7:48 am
You can use DATEDIFF to calculate time intervals. Look it up in BOL for the parameters for minutes and ordering of the dates.
You could use an insert select() where your select is the query (no parens) that finds the split records.
March 20, 2007 at 9:29 am
Hi,
To further explain the issue I've created a dummy table below. Where the duration field is > 0.30 the record will need to be split the first record containing everything before 0.30 and the second record with the duration after 0.30 Eg Duration - 0.30. The duplicated EventID fields, Mach_Desc, Type should all match the previous records.
Thanks,
CREATE TABLE [SFDC] (
[EventIDCrt] [datetime] NOT NULL ,
[EventIDAdd] [datetime] NOT NULL ,
[Machn_DESC] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TYPE] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DURATION] [numeric](10, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO SFDC VALUES('2007-03-13 09:00:55','2007-03-13 09:00:55','MACHINE1', 'GOODPRODUCT', '0.30')
INSERT INTO SFDC VALUES('2007-03-13 09:00:55','2007-03-13 09:00:55','MACHINE1','GOODPRODUCT','0.28')
INSERT INTO SFDC VALUES('2007-03-13 09:01:55','2007-03-13 09:010:55','MACHINE2','GOODPRODUCT','0.37')
INSERT INTO SFDC VALUES('2007-03-13 09:01:55','2007-03-13 09:01:55','MACHINE2','GOODPRODUCT','0.53')
INSERT INTO SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','GOODPRODUCT','0.10')
INSERT INTO SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','DOWNTIME','0.55')
INSERT INTO SFDC VALUES('2007-03-13 09:01:55','2007-03-13 09:01:55','MACHINE2','GOODPRODUCT','0.43')
INSERT INTO SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','GOODPRODUCT','0.10')
INSERT INTO SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','GOODPRODUCT','0.35')
INSERT INTO SFDC VALUES('2007-03-13 09:03:55','2007-03-13 09:03:55','MACHINE1','DOWNTIME','1.33')
INSERT INTO SFDC VALUES('2007-03-13 09:03:55','2007-03-13 09:03:55','MACHINE3','PREPARATION','0.10')
INSERT INTO SFDC VALUES('2007-03-13 09:04:55','2007-03-13 09:04:55','MACHINE4','GOODPRODUCT','0.50')
Nick
March 20, 2007 at 10:22 am
We still need the required results from that sample data.
>> Does 1.33 mean 1 minutes 33 seconds or 1 min 19.9999 seconds?
Just to confirm what you said :
0.37 should be split like so ?
0.30 (first row)
0.07 (second row)
March 20, 2007 at 11:13 am
Hi,
Yes are correct :
0.37 should be split like this :-
0.30 (first row)
0.07 (second row)
1.33 means 1 min 19.9999 seconds
Thanks,
Nick
March 20, 2007 at 11:21 am
Oops, I meant
1.333 means =
1 hour 20 minutes
Also what do you want us to do?
Straight select?
Insert the data into a new table?
Insert in the main table + delete the rows with 30+ minutes?
March 20, 2007 at 11:28 am
March 20, 2007 at 11:36 am
What if the duration is longer than 1 hour?
March 20, 2007 at 1:14 pm
Hi,
All fields are decimal values, but I still need the decimal value of > 0.30 as the flag.
All values from the source table should be inserted into a new table, and the records with a value > 0.30 should end up as two records. Eg 0.45 will end up as two records one of 0.30 and one of 0.15. Both records should match.
Thanks,
Nick
March 20, 2007 at 1:21 pm
insert into [tableA] --assuming TableA is already created
(EventIDCrt,EventIDAdd,Machn_DESC,TYPE,DURATION)
(select EventIDCrt,EventIDAdd,Machn_DESC,TYPE,
case when DURATION <= .3 then duration else .3 end as duration from sfdc
union
select EventIDCrt,EventIDAdd,Machn_DESC,TYPE,DURATION - .3 as duration from sfdc where duration >.3)
March 20, 2007 at 1:27 pm
You still didn't answer my question. What happens if you have more than 0.6?
From the sample data using your query :
2007-03-13 09:03:55.000 2007-03-13 09:03:55.000 MACHINE1 DOWNTIME 0.30
2007-03-13 09:03:55.000 2007-03-13 09:03:55.000 MACHINE1 DOWNTIME 1.03
Instead of 0.30, 0.30, 0.30, 0.30, 0.13.
March 20, 2007 at 1:40 pm
For security all drop tables commands have been commented. Use at your own risk :
USE SSC
GO
--Numbers table script
IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')
--DROP TABLE Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'SFDC' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
--DROP TABLE dbo.SFDC
GO
CREATE TABLE dbo.SFDC (
[EventIDCrt] [datetime] NOT NULL ,
[EventIDAdd] [datetime] NOT NULL ,
[Machn_DESC] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TYPE] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DURATION] [numeric](10, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:00:55','2007-03-13 09:00:55','MACHINE1', 'GOODPRODUCT', '0.30')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:00:55','2007-03-13 09:00:55','MACHINE1','GOODPRODUCT','0.28')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:01:55','2007-03-13 09:10:55','MACHINE2','GOODPRODUCT','0.37')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:01:55','2007-03-13 09:01:55','MACHINE2','GOODPRODUCT','0.53')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','GOODPRODUCT','0.10')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','DOWNTIME','0.55')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:01:55','2007-03-13 09:01:55','MACHINE2','GOODPRODUCT','0.43')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','GOODPRODUCT','0.10')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:02:55','2007-03-13 09:02:55','MACHINE3','GOODPRODUCT','0.35')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:03:55','2007-03-13 09:03:55','MACHINE1','DOWNTIME','1.33')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:03:55','2007-03-13 09:03:55','MACHINE3','PREPARATION','0.10')
INSERT INTO dbo.SFDC VALUES('2007-03-13 09:04:55','2007-03-13 09:04:55','MACHINE4','GOODPRODUCT','0.50')
SELECT
EventIDCrt
, EventIDAdd
, Machn_DESC
, TYPE, DURATION AS Old_Duration
, CEILING(DURATION/0.3) AS Multiplier
, N.PkNumber AS Rowid
, CASE WHEN DURATION - N.PkNumber * 0.3 >= 0.0
THEN 0.3
ELSE DURATION - (N.PkNumber - 1) * 0.3 END
AS New_Duration
FROM dbo.SFDC
INNER JOIN dbo.Numbers N
ON N.PkNumber <= CEILING(DURATION/0.3)
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'SFDC' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
--DROP TABLE dbo.SFDC
GO
March 20, 2007 at 1:54 pm
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply