Split single value into multiple rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Split single value into multiple rows?

Author

Topic

Windza
Yak Posting Veteran

61 Posts

Posted - 2010-09-26 : 22:00:33

Hi all...

I'm working on a plant availability report and need to split a single downtime value (i.e. downtime hours) into the corresponding months so that when I group the figures by month I get accurate figures.

The downtime table I'm working from is in the following format (DowntimeHours is pre-calculated calendar time between StartDateTime and FinishDateTime):

AssetID, StartDateTime, FinishDateTime, DowntimeHours, Comment
43, 2009-08-17 19:00:00.000, 2009-08-18 06:00:00.000, 11.0000000, Bearing Failure
58, 2008-10-02 06:00:00.687, 2008-10-15 14:00:00.000, 319.9800000, Engine Failure
62, 2008-09-26 06:00:00.673, 2008-10-08 07:20:02.000, 289.3300000, Hydraulic Cylinder Replaced
68, 2008-10-02 13:00:00.953, 2008-10-02 23:30:00.953, 10.5000000, Broken Shaft

What I'd like to do (if possible) is split/allocate the DowntimeHours value of any entries that span more than one month (i.e. WHERE MONTH(StartDateTime) <> MONTH(FinishDateTime)) proportionally to the months it spans.

An example is line 3 (AssetID 62) where the asset was down from the 26/09 to the 08/10...

The output I'd like would be similar to the following:

AssetID, StartDateTime, FinishDateTime, DowntimeHours, Comment
43, 2009-08-17 19:00:00.000, 2009-08-18 06:00:00.000, 11.0000000, Bearing Failure
58, 2008-10-02 06:00:00.687, 2008-10-15 14:00:00.000, 319.9800000, Engine Failure
62, 2008-09-26 06:00:00.673, 2008-09-30 24:00:00.000, 114.3300000, Hydraulic Cylinder Replaced
62, 2008-10-01 00:00:00.000, 2008-10-08 07:20:02.000, 175.0000000, Hydraulic Cylinder Replaced
68, 2008-10-02 13:00:00.953, 2008-10-02 23:30:00.953, 10.5000000, Broken Shaft

Once this has been achieved I can apply grouping to the effect:

Year, Month, TotalDowntimeHours
2008, 09, 114.33
2008, 10, 504.5
2009, 08, 11

If someone could provide some suggestions to this end it would be most appreciated...

Regards,
Windza

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 11:25:04

You can make a table-values function that returns the value(s) based on this.
declare @date datetime
declare @MonthStart datetime
declare @MonthEnd datetime
declare @EP numeric(5,3)

set @date = getdate()
set @MonthStart = dateadd(month,datediff(month,0,@date),0)
set @MonthEnd = dateadd(month,1,@MonthStart)
set @EP = 1.24

SELECT datediff(day,@MonthStart,@date)*@EP/datediff(day,@MonthStart,@monthEnd) as ThisMonth
, datediff(day,@date,@MonthEnd) *@EP/datediff(day,@MonthStart,@monthEnd) as NextMonth

Jim

Everyday I learn something that somebody else already knew

Windza
Yak Posting Veteran

61 Posts

Posted - 2010-09-27 : 17:20:07

quote:
Originally posted by jimf

You can make a table-values function that returns the value(s) based on this.
declare @date datetime
declare @MonthStart datetime
declare @MonthEnd datetime
declare @EP numeric(5,3)

set @date = getdate()
set @MonthStart = dateadd(month,datediff(month,0,@date),0)
set @MonthEnd = dateadd(month,1,@MonthStart)
set @EP = 1.24

SELECT datediff(day,@MonthStart,@date)*@EP/datediff(day,@MonthStart,@monthEnd) as ThisMonth
, datediff(day,@date,@MonthEnd) *@EP/datediff(day,@MonthStart,@monthEnd) as NextMonth

Jim

Jim...
Thanks for the reply, but I'm not sure I completely understand how I can tie this into a statement (very basic user of SQL). If you're able to put this into a more descriptive context it would be appreciated... keep in mind that some downtime entries are likely to span a number of months (not just one boundary).

Cheers,
Windza

(I'm not sure how I managed to place this post in the SQL 2000 forum, obviously had a brain fart -> Mod, can we please move this to the "New to SQL Programming" forum - thx...)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-27 : 17:31:32

Topic moved as requested.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-28 : 09:29:56

This would be much prettier in SQL 2005. I'm this code can be done a lot smarter, but this works. I would make a function out of all the dateadd, datediff calculations


declare @table table(AssetId tinyint,StartDateTime datetime,FinishDateTime datetime,DownTimeHours numeric(12,7),Comment varchar(100)) 


insert into @table
select 43, '2009-08-17 19:00:00.000', '2009-08-18 06:00:00.000', 11.0000000 , 'Bearing Failure'              UNION ALL
select 58, '2008-10-02 06:00:00.687', '2008-10-15 14:00:00.000', 319.9800000, 'Engine Failure'               UNION ALL
select 62, '2008-09-26 06:00:00.673', '2008-10-08 07:20:02.000', 289.3300000, 'Hydraulic Cylinder Replaced'  UNION ALL
select 68, '2008-10-02 13:00:00.953', '2008-10-02 23:30:00.953', 10.5000000 , 'Broken Shaft'
 
 
 
 select month(startDateTime) as MonthTime
        , assetid,startDateTime
        ,FinishDateTime
        ,DownTimeHours
        ,Comment 
from @table where month(StartDateTime)  =  month(FinishDateTime)
union
  select month(startDateTime) 
        ,  assetid
        ,startDateTime
        ,FinishDateTime 
        ,DownTimeHours *    datediff(day, dateadd(month,datediff(month,0,startDatetime) ,0),startDateTime)
                        /   datediff(day ,dateadd(month,datediff(month,0,startDatetime),0
                                                 )
                                         ,dateadd(month,datediff(month,0,startDatetime)+1 ,0) 
                                     ) as DownTimeHours
        ,Comment



from @table where month(StartDateTime) <   month(FinishDateTime)

 
union all
  select month(FinishDateTime) 
        ,assetid
        ,startDateTime
        ,FinishDateTime 
        , downtimehours -(downtimehours * datediff(day, dateadd(month,datediff(month,0,startDatetime) ,0),startDateTime) 
                        /   datediff(day ,dateadd(month,datediff(month,0,startDatetime),0
                                                 )
                                         ,dateadd(month,datediff(month,0,startDatetime)+1 ,0) 
                                     )
                            ) 
       ,Comment


from @table where month(StartDateTime) <   month(FinishDateTime)

 
 Jim

Everyday I learn something that somebody else already knew

Windza
Yak Posting Veteran

61 Posts

Posted - 2010-10-03 : 23:02:46

Jim,

Thanks heaps for the suggestions - I eventually decided to run a row-by-row type statement (only on downtime spanning more than 1 month) to get the clarity I needed...
Basically it counts the number of months spanned and counts back through them (allocating hours each time) before moving onto the next identified DowntimeID.

I've posted a portion of it below - keep in mind I'm a complete novice so it's probably considered rather ugly, but hey it works and I'm learning!
Performance won't be ideal but the table isn't huge and will only be run occasionally so I don't see there being any issue.

WHILE @Control = 1
BEGIN
--Row Operations --
INSERT INTO @MachineAvailability
SELECT
a.DowntimeID,
a.AssetID,
YEAR(DATEADD(mm, @BoundaryCount,a.StartDateTime)) AS Year,
MONTH(DATEADD(mm, @BoundaryCount,a.StartDateTime)) AS Month,
a.DowntimeCategoryID,
CASE
-- Allocates number of downtimes hours in the last month
WHEN MONTH(a.FinishDateTime) = MONTH(DATEADD(mm, @BoundaryCount,a.StartDateTime))
THEN ROUND((DATEDIFF(MINUTE, DATEADD(mm, DATEDIFF(mm,0,a.FinishDateTime), 0), a.FinishDateTime)/60.0)*4, 0)/4
-- Allocates number of downtime hours in the first month
WHEN MONTH(a.StartDateTime) = MONTH(DATEADD(mm, @BoundaryCount,a.StartDateTime))
THEN ROUND((DATEDIFF(MINUTE, a.StartDateTime, DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,a.StartDateTime)+1,0)))/60.0)*4, 0)/4
-- Allocates full month calendar hours for months completely spanned
ELSE ROUND(DATEDIFF(SS, DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm, @BoundaryCount,a.StartDateTime)),0)), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm, @BoundaryCount,a.StartDateTime))+1,0)))/3600.00,0)
END AS DowntimeHours,
a.ReasonCode1ID,
a.ReasonCode2ID,
a.Comment

FROM
dbo.Downtime AS a

WHERE a.DowntimeID = @DowntimeRow

--Reset loop variables--
SELECT @NextDowntimeRow = NULL
SELECT @NextBoundaryCount = NULL

--Count back through the months--
SELECT @BoundaryCount = @BoundaryCount -1

--Proceed to next DowntimeID once counted back through all months for previous ID--
IF @BoundaryCount = -1
BEGIN
SELECT @NextDowntimeRow = MIN(a.DowntimeID)

FROM @RowControl AS a
WHERE a.DowntimeID > @DowntimeRow
SELECT @BoundaryCount = a.BoundaryCount
FROM @RowControl AS a
WHERE a.DowntimeID = @NextDowntimeRow

--Check for end of table--
IF ISNULL(@NextDowntimeRow, 0) = 0
BEGIN
BREAK
END
--Set the next row--
SELECT @DowntimeRow = @NextDowntimeRow
--BREAK
END

END

I'm just in the process of error-checking (looks good so far) before I start reporting. Thanks again for the suggestions...

Cheers,

Windza

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources