Updates from March, 2008 Toggle Comment Threads | Keyboard Shortcuts

  • tech0x20 11:56 am on March 1, 2008 Permalink | Reply  

    Importing a runningahead log.xml into SQL Server 2005 using OPENXML 

    I don’t currently have a work-based use for 2005, so I thought I’d use the only readily available collection of data that I have at my disposal… my running log. This is an export file from RunningAhead.com. I don’t think I’m completely loading the XML data yet, and I don’t have database tables to load to yet, either. However, this is my implementation of some OPENXML examples.


    DECLARE @hdoc int
    DECLARE @xmlDoc XML
    SET @xmlDoc = (
      SELECT * FROM OPENROWSET (
        BULK ‘C:projectsrunningaheadlog.1.321C02log.xml’, SINGLE_CLOB
      ) AS xmlData
    )
    SELECT @xmlDoc

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc

    SELECT * FROM
    OPENXML(@hdoc, ‘/RunningAHEADLog/CourseCollection/Course’, 2)
    WITH
    (
            ID varchar(32),
            Name varchar(255) ,
            Surface varchar(255),
            City varchar(255),
            State char(3),
            Notes varchar(255)
    )

    SELECT * FROM
    OPENXML(@hdoc, ‘/RunningAHEADLog/EquipmentCollection/Shoe’, 2)
    WITH
    (

            ID varchar(32),
            Make varchar(255) ,
            Model varchar(255),
            Serial char(20),
            InitDistance decimal(7,2),
            PurchaseDate datetime,
            Retired char(3),
            Size char(10),
            SizeSystem char(40)
    )

    SELECT * FROM
    OPENXML(@hdoc, ‘/RunningAHEADLog/EventCollection/Run’, 2)
    WITH
    (
            ID char(32),
            Date datetime,
            Time datetime,
            Type char(15),
            Weight decimal(4,1),
            WeightUnit char(12),
            AvgHR tinyint,
            MaxHR tinyint,
            Quality tinyint,
            Effort tinyint,
            CourseID char(32),
            Distance decimal(8,2),
            DistanceUnit char(12),
            Duration char(20)
    )

    EXEC sp_xml_removedocument @hdoc

     
  • tech0x20 1:31 pm on February 22, 2008 Permalink | Reply  

    Sql Date Time formats. 

    Fromhttp://lists.evolt.org/archive/Week-of-Mon-20020617/115646.html

    without
    century (yy)
    with
    century
    (yyyy)
    standard outputformat
    0 or 100 (*) default mon dd yyyy hh:miAM (or PM)
    1 101 USA mm/dd/yy
    2 102 ANSI yy.mm.dd
    3 103 British/French dd/mm/yy
    4 104 German dd.mm.yy
    5 105 Italian dd-mm-yy
    6 106 dd mon yy
    7 107 mon dd, yy
    8 108 hh:mm:ss
    9 or 109 (*) default
    milliseconds
    mon dd yyyy hh:mi:ss:mmmAM (or PM)
    10 110 USA mm-dd-yy
    11 111 Japan yy/mm/dd
    12 112 ISO yymmdd
    13 or 113 (*) Europe default
    milliseconds
    dd mon yyyy hh:mm:ss:mmm(24h)
    14 114 hh:mi:ss:mmm(24h)
    20 120 yyyy-mm-dd hh:mi:ss(24h)
    21 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
    126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
    130 dd mon yyyy hh:mi:ss:mmmAM
    131 dd/mm/yy hh:mi:ss:mmmAM

    (*) the default values (style 0 or 100, 9 or 109, and
    13 or 113) always return the century (yyyy)

     
  • tech0x20 10:23 am on September 11, 2007 Permalink | Reply  

    Calculate average daily mileage required to make yearly goal 

    This might be useful if you’re programming a running site. For me, however, I wanted to write this calculation in SQL Server to expand my knowledge some.


    --Calculates the average daily mileage needed to meet a target yearly mileage goal, based on current mileage.
    CREATE FUNCTION averageDistanceNeeded (@targetMileage decimal(10,4), @currentMileage decimal(10,4))
    RETURNS decimal(10,4)
    AS
    BEGIN
    Declare @daysLeft decimal(10,4)
    Declare @dt datetime

    SELECT @dt = dt from openquery
    (
    "my-server",
    'select dt = getdate()'
    )
    select @daysLeft = datediff(day, @dt, CAST (CAST (YEAR(@dt) AS VARCHAR(4)) + '-12-31' AS DATETIME))
    return CONVERT(decimal(10,4), (@targetMileage - @currentMileage) / @daysLeft)
    END
    GO

    --Get average daily mileage needed to reach 2000 miles, based on a current mileage
    -- of 960.2
    select dbo.averageDistanceNeeded(2000.0, 960.2)
     
c
Compose new post
j
Next post/Next comment
k
Previous post/Previous comment
r
Reply
e
Edit
o
Show/Hide comments
t
Go to top
l
Go to login
h
Show/Hide help
shift + esc
Cancel
%d bloggers like this: