How to random dates

Question

Post reply

How to random dates

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

MD (5/14/2008)

Jeff

I'm not sure if I can use this because it took so long to run. I mean, I can think of better things to do with my seven seconds it took to produce 1M rows of data.

You know I'm kidding, like posted earlier, a new tool to add to the tool box.

Thanks

Heh... yeah, I know... I'm slippin' my gears, huh? 😀

Thanks for the great feedback and the "timing" results, Marvin.:)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vision59 SSCrazy Points: 2601 More actions · Answer 2

jeff thanks alot how to use this one in striaght update

SELECT SomeDate AS StartDate,

SomeDate+1 AS EndDate

FROM (

SELECT TOP 1000000

SomeDate = CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME)

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 )d

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Au4848 (5/16/2008)

jeff thanks alot how to use this one in striaght update

SELECT SomeDate AS StartDate,

SomeDate+1 AS EndDate

FROM (

SELECT TOP 1000000

SomeDate = CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME)

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 )d

Can you post the CREATE TABLE code for the table being updated along with a couple of non-private rows of data? I'll take a look... I need to know what the Primary Key of the table being updated is, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vision59 SSCrazy Points: 2601 More actions · Answer 4

Jeff thanks a lot again for your help here is create table

CREATE TABLE [dbo].[scholar] (

[scholar_id] [int] NOT NULL ,

[scholar_num] [nvarchar] (20)

[scholar_cur_stat] [tinyint] NOT NULL ,

[scholar_location] [tinyint] NULL ,

[checkin_date] [smalldatetime] NULL ,

[vacate_date] [smalldatetime] NULL ,

[carrier] [tinyint] NULL ,

[service] [tinyint] NULL ,

[room] [nvarchar] (20)

) ON [PRIMARY]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Ok... I'm going to assume that the Scholar_ID column is the Primary Key and that the start and end dates you want updated are the CheckIn_Date and Vacate_Date columns, respectively. It would have been nice to have some test data like I asked for, as well (see the URL in my signature).

Heh... whatever... here's the code... it's a little slow because of UPDATE... it takes a whopping 2,344 ms to do 102,400 rows 😀

;WITH



cteNewStartDate AS

(--==== Get the available Scholar_ID's and create a new "start date" for each

 SELECT Scholar_ID,

        CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME) AS CheckIn_Date

   FROM dbo.Scholar

)

--===== Do the update using the new "start date" and add 1 to that for new "end date"

 UPDATE s

    SET CheckIn_Date = nsd.CheckIn_Date,

        Vacate_Date  = nsd.CheckIn_Date + 1

   FROM dbo.Scholar s

  INNER JOIN

        cteNewStartDate nsd

ON s.Scholar_ID = nsd.Scholar_ID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vision59 SSCrazy Points: 2601 More actions · Answer 6

vision59

SSCrazy

Points: 2601

May 20, 2008 at 4:16 pm

#818268

Hi Jeff,

Thanks for you reply but update statement is not work is syntax correct or not

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

I test all code I post so the answer is, "YES", the code is correct. Please post the error you are getting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vision59 SSCrazy Points: 2601 More actions · Answer 8

vision59

SSCrazy

Points: 2601

May 20, 2008 at 6:09 pm

#818299

Thanks once again is the statment like this

:WITH

cteNewStartDate AS

(--==== Get the available Scholar_ID's and create a new "start date" for each

SELECT Scholar_ID,

CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME) AS CheckIn_Date

FROM dbo.Scholar

I mean syntax at with and quotes?//

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

You used a colon instead of a semi-colon... "Must look eye" 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vision59 SSCrazy Points: 2601 More actions · Answer 10

vision59

SSCrazy

Points: 2601

May 20, 2008 at 6:22 pm

#818305

sorry that is typo is the script starts with semi-colon

;with like that

vision59 SSCrazy Points: 2601 More actions · Answer 11

vision59

SSCrazy

Points: 2601

May 20, 2008 at 6:28 pm

#818307

it is giving me

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near ';'.

at ;with location

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Either you're not using SQL Server 2005 or your compatibility mode is set to "8.0".

Please check.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vision59 SSCrazy Points: 2601 More actions · Answer 13

vision59

SSCrazy

Points: 2601

May 20, 2008 at 7:12 pm

#818314

I'm using sql2000 for this code

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Heh... shouldn't have posted in the SQL Server 2005 forum then... 😉

Lemme see what I can do...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

CTE is nothing more than a "derived table"... try this...

--===== Do the update using the new "start date" and add 1 to that for new "end date"



 UPDATE s

    SET CheckIn_Date = nsd.CheckIn_Date,

        Vacate_Date  = nsd.CheckIn_Date + 1

   FROM dbo.Scholar s

  INNER JOIN

        (--==== Get the available Scholar_ID's and create a new "start date" for each

         SELECT Scholar_ID,

                CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME) AS CheckIn_Date

           FROM dbo.Scholar)nsd

ON s.Scholar_ID = nsd.Scholar_ID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions