inserting whole number intervals

Last Post 09 Sep 2008 03:48 PM by jmaikido. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jmaikido
New Member
New Member

--
07 Sep 2008 11:20 PM
Hi Listers,

note ... member Pesomannen was kind enough to post a solution to a very similar problem. I'm hoping he could come to my assistance here.

I'm kinda stuck on providing a code solution to the following:
Any help wld b greatly appreciated.

I have a table with 4 columns:
HoleID, mFrom, mTo, Code
Essentially, where i have irregular intervals, ie. < 1m (between mfrom & the next mTo), i wish to insert a new record that pads-out the interval to the nearest whole number and also incorporates the same code.

eg. Existing data:
HoleId mFrom mTo Code
TWDD 103 103.7 Utc
TWDD 103.7 108.9 Utc
TWDD 108.9 109.3 Msc

Required result:

eg. Newly inserted data:

HoleId mFrom mTo Code
TWDD 103 103.7 Utc
TWDD 103.7 104 Utc <--new insertion
TWDD 104 105 Utc <--new insertion
TWDD 105 106 Utc <--new insertion
TWDD 106 107 Utc <--new insertion
TWDD 107 108 Utc <--new insertion
TWDD 108 108.9 Utc <--new insertion
TWDD 108.9 109 MSc <--new insertion
TWDD 109 109.3 Msc <--new insertion

Many TIA
SQLUSA
New Member
New Member

--
09 Sep 2008 07:12 AM
You can just create a #temptable with the desired range and fill it in from that table using JOINs.

Example:

select top (10000) SeqNo = identity (int, 3000, 1) into #SeqNo
from sys.objects a cross apply sys.objects b cross apply sys.objects c
go
select * from #SeqNo

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SwePeso
New Member
New Member

--
09 Sep 2008 07:35 AM
Interesting idea.
Would you care to explain how to deal with decimals and succeeding Code too?
SwePeso
New Member
New Member

--
09 Sep 2008 08:24 AM
No?

Ok, I will do it myself then.


<br>DECLARE    @Sample TABLE
<br>    (
<br>        HoleID CHAR(4),
<br>        mFrom SMALLMONEY,
<br>        mTo SMALLMONEY,
<br>        Code CHAR(3)
<br>    )
<br>
<br>INSERT    @Sample
<br>SELECT    'TWDD', 103  , 103.7, 'Utc' UNION ALL
<br>SELECT    'TWDD', 103.7, 108.9, 'Utc' UNION ALL
<br>SELECT    'TWDD', 108.9, 109.3, 'Msc'
<br>
<br>SELECT        s.HoleID,
<br>        CASE
<br>            WHEN f.pFrom > s.mFrom THEN f.pFrom
<br>            ELSE s.mFrom
<br>        END AS mFrom,
<br>        CASE
<br>            WHEN f.pTo < s.mTo THEN f.pTo
<br>            ELSE s.mTo
<br>        END AS mTo,
<br>        s.Code
<br>FROM        @Sample AS s
<br>OUTER APPLY    (
<br>            SELECT    v.Number - 1 AS pFrom,
<br>                v.Number AS pTo
<br>            FROM    master..spt_values AS v
<br>            WHERE    v.Type = 'P'
<br>                AND v.Number > s.mFrom
<br>                AND v.Number < s.mTo + 1.0
<br>        ) AS f
<br>
jmaikido
New Member
New Member

--
09 Sep 2008 03:48 PM
Hi Peter, thanks again for your excellent code solution. Your coding skills are truly an asset. Would you be so kind as to explain 'how' it works and what is the function of using spt_values? Also, could you break down your explanation so that I can digest the solution?
Regards,
John
jmaikido
New Member
New Member

--
09 Sep 2008 03:49 PM
Hi Kalman, thnx for your reply. I have used the solution provided by Pesommanen.
Rgds, John
SQLUSA
New Member
New Member

--
10 Sep 2008 04:30 AM
quote:

Originally posted by: jmaikido
Hi Peter, thanks again for your excellent code solution. Your coding skills are truly an asset. John


Affirmative!

Peter is a brilliant SQL coder.

Currently there is no built-in sequence (to fill in gaps) in SQL Server, so you need ad-hoc ways to deal with it.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/



SwePeso
New Member
New Member

--
12 Sep 2008 02:21 PM
I an just using spt_values as a tally table in order to insert the missing numbers.
You are not authorized to post a reply.

Acceptable Use Policy