May 1, 2014 at 1:47 pm
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:
1 05/01/2014
2 05/01/2014
3 07/24/2014
Desired output:
TransactionID: TransactionDate: RandomDates:
1 05/01/2014 05/05/2014
2 05/01/2014 05/07/2014
3 07/24/2014 07/29/2014
May 1, 2014 at 2:09 pm
JStevenson1 (5/1/2014)
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:
1 05/01/2014
2 05/01/2014
3 07/24/2014
Desired output:
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/[/url]
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
from SomeData
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2014 at 2:23 pm
Thanks. I will read the article.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply