May 14, 2008 at 1:44 pm
MD (5/14/2008)
JeffI'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
Change is inevitable... Change for the better is not.
May 16, 2008 at 5:28 pm
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
May 16, 2008 at 5:50 pm
Au4848 (5/16/2008)
jeff thanks alot how to use this one in striaght updateSELECT 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
Change is inevitable... Change for the better is not.
May 16, 2008 at 7:13 pm
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]
May 16, 2008 at 9:21 pm
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
Change is inevitable... Change for the better is not.
May 20, 2008 at 4:16 pm
Hi Jeff,
Thanks for you reply but update statement is not work is syntax correct or not
May 20, 2008 at 5:33 pm
I test all code I post so the answer is, "YES", the code is correct. Please post the error you are getting.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 6:09 pm
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?//
May 20, 2008 at 6:19 pm
You used a colon instead of a semi-colon... "Must look eye"
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 6:22 pm
sorry that is typo is the script starts with semi-colon
;with like that
May 20, 2008 at 6:28 pm
it is giving me
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
at ;with location
May 20, 2008 at 6:56 pm
Either you're not using SQL Server 2005 or your compatibility mode is set to "8.0".
Please check.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 7:12 pm
I'm using sql2000 for this code
May 20, 2008 at 7:28 pm
Heh... shouldn't have posted in the SQL Server 2005 forum then...
Lemme see what I can do...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 7:31 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy