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 ««123»»

column value balancing (copy previous value <> NULL) Expand / Collapse
Author
Message
Posted Wednesday, November 17, 2010 12:43 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
Dick Herberts-320552 (11/17/2010)


I would say , run the script and see yourself.



That would be great if I had a SS2008 instance to run it on. This fails on SS2005. The code to create the solution for you should be portable between versions, but it's too much work for someone to create your test data in that format.

Since you're new, I'll point you to the forum thread in my signature line. This thread outlines posting sample data for best (and faster) responses and help.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #1022410
Posted Wednesday, November 17, 2010 1:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:04 AM
Points: 11, Visits: 62
Hi John,

Ok i understand. I can send another smaller testfile with some data. I will create something for you.

Do you think you have a working solution for my problem?

Regards,

Dick Herberts

i will send you another testfile in 10 minutes
Post #1022431
Posted Wednesday, November 17, 2010 1:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:04 AM
Points: 11, Visits: 62
Hi John,

Here's the new testtable with data (works also on SQL Server 2005)

as you can see there are 3 different category,class location combinations in this testfile

category=111
class=5010
location=210

category=111
class=5010
location=230

category=111
class=5010
location=240

for every category,class,location combination RowNum starts with 1 (row 1 has always values)

success

Regards,

Dick Herberts


CREATE TABLE [dbo].[TestTable](
[RowNo] [int] NULL,
[Category] [char](3) NULL,
[Class] [char](4) NULL,
[Location] [char](3) NULL,
[Datum] [datetime] NULL,
[Owned] [int] NULL,
[Beschikbaar] [int] NULL,
[OnRent] [int] NULL
) ON [PRIMARY]

INSERT INTO TestTable
VALUES
(1,'111','5010','210','20100101', 12,1,0),
(2,'111','5010','210','20100102', NULL,NULL,NULL),
(3,'111','5010','210','20100103', 13,8,NULL),
(4,'111','5010','210','20100104', 12,NULL,3),
(5,'111','5010','210','20100105', 12,5,NULL),
(6,'111','5010','210','20100106', 15,6,4),
(7,'111','5010','210','20100107', NULL,NULL,3),
(8,'111','5010','210','20100108', NULL,17,0),
(9,'111','5010','210','20100109', 7,16,1),
(10,'111','5010','210','20100110', NULL,18,NULL),
(11,'111','5010','210','20100111', 12,1,2),
(12,'111','5010','210','20100112', NULL,6,6),
(13,'111','5010','210','20100113', NULL,NULL,NULL),
(14,'111','5010','210','20100114', NULL,NULL,NULL),
(1,'111','5010','230','20100101', 1,17,2),
(2,'111','5010','230','20100102', NULL,1,NULL),
(3,'111','5010','230','20100103', 13,NULL,1),
(1,'111','5010','240','20100101', 2,0,2),
(2,'111','5010','240','20100102', NULL,3,NULL),
(3,'111','5010','240','20100103', 12,5,10),
(4,'111','5010','240','20100104', 1,NULL,NULL),
(5,'111','5010','240','20100105', NULL,6,5),
(6,'111','5010','240','20100106', 7,NULL,35),
(7,'111','5010','240','20100107', NULL,NULL,36),
(8,'111','5010','240','20100108', NULL,1,NULL),
(9,'111','5010','240','20100109', 4,3,7),
(10,'111','5010','240','20100110', NULL,8,NULL),
(11,'111','5010','240','20100111', NULL,NULL,NULL);





Post #1022434
Posted Wednesday, November 17, 2010 2:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 6,582, Visits: 8,861
This form of the update statement has special rules for running in this manner. They MUST be followed, or your data can be made wrong. See comments in code for more information.

DECLARE @Owned INT = 0,
@OnRent INT = 0,
@Sequence INT = 0,
@Category CHAR(3);

-- This form of the UPDATE statement has some particular rules.
-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
-- for a complete discussion of how this works, and all of the rules for utilizing it.

WITH SafetyCheck AS
(
SELECT Category,
Class,
Location,
RowNo,
Owned,
OnRent,
Sequence = ROW_NUMBER() OVER (ORDER BY Category,Class,Location,RowNo)
FROM dbo.TestTable
)
UPDATE t
SET @Sequence = CASE WHEN Sequence = @Sequence + 1 THEN @Sequence + 1
ELSE 1/0 END,
@Owned = Owned = IsNull(Owned, @Owned),
@OnRent = OnRent = ISNULL(OnRent, @OnRent),
@Category = Category -- ANCHOR COLUMN
FROM SafetyCheck t WITH (TABLOCKX)
OPTION (MAXDOP 1);

select * from dbo.TestTable;



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1022471
Posted Wednesday, November 17, 2010 2:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
Good job Wayne....you beat me to it. I know a cte could do the trick, I was just working through the update logic.

Great solution!




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #1022479
Posted Wednesday, November 17, 2010 2:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:04 AM
Points: 11, Visits: 62
Hi Wayne,

WOW !!!!!!!!!, I have tested it and add an extra column and it works greatly , I'am very impressed with your skills !!!!

Thank you soooo much.

How do you think performance will on a file with 26 milj records ?? I will test it anyway to see myself but i like to here your comments on this

Regards

Dick Herberts
Post #1022482
Posted Wednesday, November 17, 2010 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 7,040, Visits: 12,965
Great job, Wayne!
That's the solution I had in mind...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1022484
Posted Wednesday, November 17, 2010 3:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 7,040, Visits: 12,965
@Dick: VERY IMPORTANT!!!
Based on your previous note "4. No clustered index yet " Do NOT run the code until you added a clustered index that supports the order the data need to be updated!!

I'd probably go with
CREATE UNIQUE CLUSTERED INDEX CX_testtable_ImportantForQuirkyUpdate
ON testtable (Category,Class,Location,RowNo)

Regarding performance:
I duplicated your data to build a Million-Row table (increasing Category value, actually 1.4Mill rows). Waynes code run in 3 seconds (excluding the final SELECT * which took another 17 sec). My system: Home PC with WinXP and SQL2K5.
Range as expected




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1022504
Posted Wednesday, November 17, 2010 3:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 6,582, Visits: 8,861
LutzM (11/17/2010)
@Dick: VERY IMPORTANT!!!
Based on your previous note "4. No clustered index yet " Do NOT run the code until you added a clustered index that supports the order the data need to be updated!!

I'd probably go with
CREATE UNIQUE CLUSTERED INDEX CX_testtable_ImportantForQuirkyUpdate
ON testtable (Category,Class,Location,RowNo)



I knew there was something I was going to add to my post - THANKS LUTZ!
Since you said that the PK was these same four columns - I just make the PK clustered. Since you have a multi-million row table, you will have to consider whether this is what you want to do or not.

Regarding performance:
I duplicated your data to build a Million-Row table (increasing Category value, actually 1.4Mill rows). Waynes code run in 3 seconds (excluding the final SELECT * which took another 17 sec). My system: Home PC with WinXP and SQL2K5.
Range as expected

It took 3 whole seconds on your system? What'd you do, kick off a full system virus scan at the same time?

Seriously, that's about the expected time for this to run.

Sorry I forgot to mention the clustered index - and again, thank you Lutz for picking up on that. Dick, that clustered index is supremely important for this to work correctly - otherwise, you'll end up with garbage.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1022511
Posted Wednesday, November 17, 2010 3:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:04 AM
Points: 11, Visits: 62
Hi guys,

I'am testing the routine right now on a table with 26.000.000 records, tomorrow i will give the performance results.

Thanks very much for all your knowledge input

Have a nice evening and till tomorrow

Post #1022519
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse