SQL Random number

[benchpolo]

How do I generate a random 10 digit number using the query below.

SELECT cd.proccode
, cd.diagcode
, CONVERT(varchar ,cm.datefrom ,112) as 'dos'
, cm.membid
, cm.membname
, cd.qty
, cd.net
, cd.copay
, RIGHT(cd.claimn o,12) + CASE WHEN LEN(cd.tblrowid ) < 2 then '0' + CONVERT(VARCHAR (2), cd.tblrowid) else CONVERT(VARCHAR (2), cd.tblrowid)end as 'claimno'
, [Random 10 digit]
FROM EZCAP1.ARCHP.db o.rvs_claim_mas ters cm
LEFT JOIN EZCAP1.ARCHP.db o.rv_claim_deta ils cd ON (cd.claimno = cm.claimno)
WHERE cm.phcode = 'P' and (cd.proccode LIKE 'J%' OR cd.proccode LIKE 'P%' OR cd.proccode LIKE 'Q%')
AND cm.datefrom BETWEEN '10/01/2007' AND '12/31/2007'
AND cm.status = '9'
AND cd.net > '0.00'

[ck9663]

How do I generate a random 10 digit number using the query below.

SELECT cd.proccode
, cd.diagcode
, CONVERT(varchar ,cm.datefrom ,112) as 'dos'
, cm.membid
, cm.membname
, cd.qty
, cd.net
, cd.copay
, RIGHT(cd.claimn o,12) + CASE WHEN LEN(cd.tblrowid ) < 2 then '0' + CONVERT(VARCHAR (2), cd.tblrowid) else CONVERT(VARCHAR (2), cd.tblrowid)end as 'claimno'
, [Random 10 digit]
FROM EZCAP1.ARCHP.db o.rvs_claim_mas ters cm
LEFT JOIN EZCAP1.ARCHP.db o.rv_claim_deta ils cd ON (cd.claimno = cm.claimno)
WHERE cm.phcode = 'P' and (cd.proccode LIKE 'J%' OR cd.proccode LIKE 'P%' OR cd.proccode LIKE 'Q%')
AND cm.datefrom BETWEEN '10/01/2007' AND '12/31/2007'
AND cm.status = '9'
AND cd.net > '0.00'

Use the RAND function. In your query, you have a tblrowid, use that as int (use cast if necessary), as the seed.

-- CK

[pdreyer]

How do I generate a random 10 digit number using the query....

select convert(numeric(10,0),rand(checksum(newid())) * 9999999999)
from ...

[bostonnole]

select convert(numeric (10,0),rand(che cksum(newid())) * 9999999999)

The above is close, but it does not guarantee the number will always be 10 digits.