Inserting whole numbers - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting whole numbers

Author

Topic

jmaikido
Starting Member

2 Posts

Posted - 2008-09-08 : 00:50:44

Hi Listers,

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 00:51:31

You can use the ROUND function for this.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-08 : 14:24:32

[code]SELECT t.HoleID,
CASE WHEN FLOOR(mFrom)+v.number > mFrom THEN FLOOR(mFrom)+v.number ELSE t.mFrom END,
CASE WHEN FLOOR(mFrom)+v.number > mTo THEN mTo ELSE FLOOR(mFrom)+v.number+1 END,t.Code
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND FLOOR(mFrom)+v.number<CIELING(mTo)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources