Need help to write sql statement

Question

Need help to write sql statement

william_do2000

SSC Enthusiast

Points: 102
More actions
January 16, 2009 at 8:22 am

#72327

I need your help from expert SQL. I have table name tblCalcHeader have about 25000 records count that I import from excel. I create the table has uid is unique id how you write sql statment = value in column = Value_I_want that I manual enter when DocumentId = H then VALUE_I_WANT =SerialNumber1
For example
uid = 9607 and DocumentId = H and SerialNumber1=0001 then column value_I_Want
Then UID 9608 ,9609 ,9610 value_I_Want column =0001
Note: DocumentId only have in the file (H,1,2,3 and 4)
Thank you for your help
LD
uid DocumentId BatchNumber SerialNumber1 VALUE_I_WANT
----------- ---------- ----------- ------------- ----
9607 H 001 0001 0001
9608 1 001 0002 0001
9609 1 001 0003 0001
9610 1 001 0004 0001
9615 H 001 0009 0009
9616 3 001 0010 0009
9617 3 001 0011 0009
9618 3 001 0012 0009
9631 H 001 0025 0025
9632 2 001 0026 0025
9633 2 001 0027 0025
10015 H 006 0001 0001
10016 3 006 0002 0001
10017 3 006 0003 0001
10018 3 006 0004 0001
10019 H 006 0005 0005
10020 4 006 0006 0005

Viewing 6 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not at all clear on what you're asking for.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Right there with you. I couldn't figure out what the OP is asking either.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Your case statement appears to do what you say you want. What problem are you running into?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323546 More actions · Answer 4

must be an incomplete example...all your code that is commented out in your case statement returns c.SerialNumber1 NO Matter what....maybe you could show us a bit more?

Case

when a.DocumentId= 'H' then c.SerialNumber1

when a.DocumentId= '1' or a.uid=9608 then c.SerialNumber1

-- when a.DocumentId= '1' and c.uid =9607 then c.SerialNumber

-- when (a.DocumentId= '2' and a.uid=c.uid )then c.SerialNumber1

-- when (a.DocumentId= '3' and a.uid=c.uid )then c.SerialNumber1

-- when (a.DocumentId= '4' and a.uid=c.uid )then c.SerialNumber1

-- when (a.DocumentId= '5' and a.uid=c.uid )then c.SerialNumber1

-- when (a.DocumentId= '6' and a.uid=c.uid )then c.SerialNumber1

-- when (a.DocumentId= '7' and a.uid=c.uid )then c.SerialNumber1

-- else c.SerialNumber1

end test

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Garadin One Orange Chip Points: 29613 More actions · Answer 5

He's just carrying forward the serial number whenever the DocumentID is 'H'. Easily solved by Running Totals.

William, please observe the way I posted your sample data. Please duplicate this for future posts. Re-coding your sample data to be usable took 5x as long as writing the solution to your problem. An article on how you can easily do this is in my signature.

The clustered index on your table is vital to making this solution work. If this is not your clustered index, select your data into a temp table before running this and create your clustered index on that.

[font="Courier New"]

CREATE TABLE #Test(

uID INT,

DocumentID VARCHAR(10),

BatchNumber VARCHAR(10),

SerialNumber VARCHAR(10),

NumberIWant VARCHAR(10),

PRIMARY KEY CLUSTERED(UID),

RT VARCHAR(10))

INSERT INTO #Test(uID, DocumentID, BatchNumber, SerialNumber, NumberIWant)

SELECT 9607,'H','001','0001','0001' UNION ALL

SELECT 9608,'1','001','0002','0001' UNION ALL

SELECT 9609,'1','001','0003','0001' UNION ALL

SELECT 9610,'1','001','0004','0001' UNION ALL

SELECT 9615,'H','001','0009','0009' UNION ALL

SELECT 9616,'3','001','0010','0009' UNION ALL

SELECT 9617,'3','001','0011','0009' UNION ALL

SELECT 9618,'3','001','0012','0009' UNION ALL

SELECT 9631,'H','001','0025','0025' UNION ALL

SELECT 9632,'2','001','0026','0025' UNION ALL

SELECT 9633,'2','001','0027','0025' UNION ALL

SELECT 10015,'H','006','0001','0001' UNION ALL

SELECT 10016,'3','006','0002','0001' UNION ALL

SELECT 10017,'3','006','0003','0001' UNION ALL

SELECT 10018,'3','006','0004','0001' UNION ALL

SELECT 10019,'H','006','0005','0005' UNION ALL

SELECT 10020,'4','006','0006','0005'

DECLARE @RT VARCHAR(10),

@uID INT

SET @RT = ''

UPDATE #Test

SET @RT = RT = CASE WHEN DocumentID = 'H' THEN SerialNumber ELSE @RT END,

@uID = uID

FROM #Test WITH (INDEX(0))

SELECT * FROM #Test

DROP TABLE #Test[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]