I have a column called Transaction Date. I'd like to populate a new date that falls within 12 days from the Transaction Date that falls on a weekday.
TransactionID: TransactionDate: RandomDates:
1 05/01/2014 05/05/2014
2 05/01/2014 05/07/2014
3 07/24/2014 07/29/2014
This would be a lot easier if you could provide ddl and sample data in a consumable format in the future.
If I understand your requirements you want a random date that is at least 1 but not more than 12 days later than TransactionDate?
Jeff Moden wrote an excellent article about generating test data. http://www.sqlservercentral.com/articles/Data+Generation/87901/
Using the logic in there to generate a random integer within a given range we can do something like this.
with SomeData as
select 1 as TransactionID, cast('20140501' as datetime) as TransactionDate union all
select 2, '20140501' union all
select 3, '20140724'
select *, DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 12 + 1, TransactionDate) as RandomDate
Make sure you read that article and understand what is going on here before you use this.
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)