getting value based on data in other fields

Question

getting value based on data in other fields

lbrady

SSC Eights!

Points: 883
More actions
May 2, 2011 at 7:25 am

#250139

I have a table with three columns
DATA varchar(255)
VDate varchar (50)
ID Int (Identity)
Sample data:
'xyz', 'Jan 1, 1990', '1'
'pdq','','2'
'str','','3'
'drtg','','4'
'5534','','5'
'ytsf','','6'
'hhd','Jan 2, 1990, '7'
'ggge','','8'
Need to fill in the VDate with the value from the most previous record with data in the VDate field.
Any ideas on how to do this?

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

So given your example what do you want the output to be? Read the link in my signature for details about how to ask a question that is more likely to get you answers.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

lbrady SSC Eights! Points: 883 More actions · Answer 2

Sean Lange (5/2/2011)
So given your example what do you want the output to be? Read the link in my signature for details about how to ask a question that is more likely to get you answers.

'xyz', 'Jan 1, 1990', '1'

'pdq','Jan 1, 1990','2'

'str','Jan 1, 1990','3'

'drtg','Jan 1, 1990','4'

'5534','Jan 1, 1990','5'

'ytsf','Jan 1, 1990','6'

'hhd','Jan 2, 1990, '7'

'ggge','Jan 2, 1990','8'

Thank you

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

ok so here is what I have for some sample ddl and data...

create table dummy

(

data varchar(25),

VDate varchar(25),

ID int

)

insert dummy select 'xyz', 'Jan 1, 1990', '1'

union all

select 'pdq','','2'

union all

select 'str','','3'

union all

select 'drtg','','4'

union all

select '5534','','5'

union all

select 'ytsf','','6'

union all

select 'hhd','Jan 2, 1990', '7'

union all

select 'ggge','','8'

The first thing I would strongly recommend is to make your date field use the datetime datatype for a number or reasons (but that really is another topic entirely 🙂 ). What have you tried so far to get your desire output?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

lbrady SSC Eights! Points: 883 More actions · Answer 4

Sean Lange (5/2/2011)
ok so here is what I have for some sample ddl and data...
create table dummy
(
data varchar(25),
VDate varchar(25),
ID int
)
insert dummy select 'xyz', 'Jan 1, 1990', '1'
union all
select 'pdq','','2'
union all
select 'str','','3'
union all
select 'drtg','','4'
union all
select '5534','','5'
union all
select 'ytsf','','6'
union all
select 'hhd','Jan 2, 1990', '7'
union all
select 'ggge','','8'
The first thing I would strongly recommend is to make your date field use the datetime datatype for a number or reasons (but that really is another topic entirely 🙂 ). What have you tried so far to get your desire output?

Yep, that would create the table of sample data. My table has 200+ char in the data field and 17M rows.

The vdate field in my real data cannot be changed readily to datetime. There are extraneous characters as I am importing this data from a text log. I realize it would be easier to work with as datetime - IF it actually contained a date.

This is a one-shot deal so I am being very rudimentary with my technique. I have created another identical table and am parsing through each assigning the vdate value from table2.id -1 where table1.vdate is null. VERY rudimentary.

Figured there's an easier way.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Didn't really get what you have tried on your own but something like this should get you in the right direction...

select ID, data, (select top 1 VDate from dummy where VDate <> '' and ID <= d.ID order by ID desc) as VDate

from dummy d

order by ID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/