Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output random dates that falls on a weekday Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 1, 2014 2:22 PM
Points: 24, Visits: 94
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
Post #1566845
Posted Thursday, May 1, 2014 2:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:10 AM
Points: 13,230, Visits: 12,709
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/

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 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)
Post #1566849
Posted Thursday, May 1, 2014 2:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 1, 2014 2:22 PM
Points: 24, Visits: 94
Thanks. I will read the article.
Post #1566853
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse