dimanche 3 avril 2016

how to update a table with 2 random values, check in and check out dates/columns

In MS Sql Server how can I update a LibraryTtable with 3 random values, that follow a sequence of books in a lib.

The library system has to calculate fines, based on 3 factors checkout, allowed duration (books 30 days, mags 7 days), and finally checkin date.

For e.g. First a Col1 -> Check-Out Date then based on a random NEWID()) % 30 days col2 -> Check-in Date and Col3 -> Originally allowed Duration (can be 7 to 30)

I can do this, UPDATE [dbo].LibraryTtable SET Check-Out = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 370), '2015-02-01')

but how do I hold that in a temp variable and add a randonm range within a span of 30 days for the check-in date

I can get the random values idependently, but I dont know how to store and use them sequentially together.




Aucun commentaire:

Enregistrer un commentaire