It worked for me, but I realize I had to make it even more simple for you.

Check this out.

DECLARE @XML XML,

@NodeNumber INT,

@NewValue VARCHAR(20)

SET@XML = '<Accounts>

<subAcc>

<Name>EmpID</Name>

<Value>234</Value>

</subAcc>

<subAcc>

<Name>EmpName</Name>

<Value>Test</Value>

</subAcc>

<subAcc>

<Name>EmpDOJ</Name>

<Value>3/31/2011</Value>

</subAcc>

<subAcc>

<Name>EmpDept</Name>

<Value>IT</Value>

</subAcc>

</Accounts>'

SELECT@NewValue = '04/28/2011',

@NodeNumber = 3

SET@XML.modify('replace value...

Here is one way http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx

A bit cludgy, but it works

;WITH cteInterval(CustID, Interval, Bal)

AS (

SELECTc.CustID,

i.Interval,

COALESCE(p.Bal, n.Bal) AS Bal

FROM(

SELECTCustID

FROM@data

GROUP BYCustID

) AS c

CROSS JOIN(

SELECTNumber AS Interval

FROMmaster..spt_values

WHEREType = 'P'

AND Number BETWEEN (SELECT MIN(Interval) FROM @data) AND (SELECT MAX(Interval)...

That is what the transaction is for.

Something like this

BEGIN TRAN

DECLARE@RunnNum INT = (SELECT RunnNum1 FROM MyRunnNum WHERE Code = 'trnxNo');

INSERT@tItem

(

trnxNo,

payer,

amt

)

SELECT@RunnNum + ROW_NUMBER() OVER (ORDER BY a.b),

a.b.value('payer[1]', 'INT'),

a.b.value('amt[1]', 'DECIMAL(10, 2)')

FROM@data.nodes('/data/paymentItem') AS a(b)

UPDATEMyRunnNum

SETRunnNum1 += @@ROWCOUNT

WHERECode = 'trnxNo'

COMMIT...

This is also true for binary values, since LEN seem to convert them to varchar.

See http://weblogs.sqlteam.com/peterl/archive/2008/07/22/Why-LEN-differs-from-DATALENGTH-when-using-BINARY-data.aspx

Thank you.

The solution is simple. For each date, compute an auxiliary date that is 13 days later.

Then apply my 4 second solution found here http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851 on how to pack the date...

Thank you.

On the other hand, if all people did this I would be out of work 🙂

Deleted because code already posted.

You can always create a denormalised view to satisfy the third-party application.

But always keep the data normalised in the database.

There is a big differense between storing the data, and enabling...

No problem.

Your name sound Swedish. Have you considered joining you local PASS user group? In Stockholm there is SQLUG and in Malmö we have Scania.

Also see answers here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163395 and here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9572438c-cf3c-456c-8076-97e7a5e931b6.

No problem. Just UNPIVOT the data and use my suggestion earlier.

Using the same sample data and table as before...

-- User Supplied Parameters

DECLARE@UseOnlyTheLastMinutes SMALLINT = 60,

@LogonInterval TINYINT = 5,

@NumberOfLogons TINYINT = 3

-- SwePeso

;WITH cteSource(UserName, LogonTime, ComputerName, maxLogonTime)

AS (

SELECTs.UserName,

s.LogonTime,

f.ComputerName

FROM#Sample AS s

CROSS APPLY(

SELECTw.ComputerName

FROM#Sample...