SQLTeam.com | Weblogs | Forums

Fetch the data only 2 updates from Column in Sql server


#1

Hi

I have a table in below format in sql server 2008.
no(int) description varchar(2000)
18 <start 01/jan/2016 ..... end 5/jan/2016>
<start 05/jan/2016 .......end 10/jan/2016>
<start 11/jan/2016//////update end 17/jan/2016>
19 <start 01/dec/2015,,,,,,, end 5/dec/2015>
<start 05/dec/2015,,,,,,,,,, update end 10/dec/2015>
20 <start 01/dec/2015 ,,,,,,,, end 5/dec/2015>
<start 05/dec/201 5.. update end 10/dec/2015>
<start 16/dec/2016 end 20/dec/2015>
<start 21/dec/2016...... update end 30/dec/2015>
data has existed in above manner.
i need to fetch the data in below format.
expected result is :
select description from table
18 <start 01/jan/2016 nd 5/jan/2016>
<start 05/jan/2016 this second usakl........... update end 10/jan/2016>
19 <start 01/dec/2015 this is first update end 5/dec/2015>
<start 05/dec/2015 this second usakl........... update end 10/dec/2015>
20 <start 01/dec/2015 this is first update end 5/dec/2015>
<start 05/dec/2015 this second usakl........... update end 10/dec/2015>

if anybody know pls help the same


#2

Your input and output look identical


#3

Hi
Hi britton,

thank you for your reply...
format has been not wrapped properly for above post. find the below format

I have a table in below format in sql server 2008.
no(int) description varchar(2000)
18 [start 01/jan/2016 ..... end 5/jan/2016] [start 05/jan/2016 .......end 10/jan/2016 ][start 11/jan/2016//////update end 17/jan/2016]
19 [start 01/dec/2015,,,,,,, end 5/dec/2015] [start 05/dec/2015,,,,,,,,,, update end 10/dec/2015]
20 [start 01/dec/2015 ,,,,,,,, end 5/dec/2015] [start 05/dec/201 5.. update end 10/dec/2015]
[start 16/dec/2016 end 20/dec/2015]
[start 21/dec/2016...... update end 30/dec/2015]
data has existed in above manner.
i need to fetch the data in below format.
expected result is :
select description from table
18 [start 01/jan/2016 nd 5/jan/2016][start 05/jan/2016 this second usakl........... update end 10/jan/2016]
19 [start 01/dec/2015 this is first update end 5/dec/2015] [start 05/dec/2015 this second usakl........... update end 10/dec/2015]
20 [start 01/dec/2015 this is first update end 5/dec/2015] [start 05/dec/2015 this second usakl........... update end 10/dec/2015]

if anybody know pls help the same


#4

Still looks identical. I cannot figure out what you want.


#5

Please post your data as:

  1. CREATE TABLE
  2. INSERT INTO

As it is, I cannot tell where each column starts and stops


#6

Hi

Create table test(no,int,description varchar(200))
insert into test(10,'[start 1/dec/2015.....end 6/dec/2015] [start 4/dec/2015 ...end 10/dec/2015] [start 4/dec/2015 ... end 10/dec/2015]')

insert into test(20,'[start 1/nov/2015.....end 6/nov/2015] [start 4/nov/2015 ..end .10/nov/2015] [start 14/nov/2015 ...end 20/nov/2015]')

thanks britton..

please find the below ...

insert into test(30,'[start 1/oct/2015.....end 6/oct/2015] [start 4/oct/2015 .. end 10/oct/2015. ]')

decription column can have multiple weeks (1 or many weeks) status .

based on the 'start' and 'end ' tags i need to fetch only last 2 weeks status only

example id 10 and 20 has 3weeks update status(based on start and end)

i need 2 weekks status from the description column


#7

Thanks for the data.

Unfortunately there is no easy query to get you what you want. Basically, the table is defined poorly. You need something more like:

CREATE TABLE test (no int, StartDate date EndDate date)
INSERT INTO test (no, StartDate, EndDate) values
(10, '20151201', 20151206'),
(10, '20151204, '20151210'),
...

Then it's easy,


#8

I agree. In general, for a RDBMS, if you have to parse data from a column to derive meaning for the data, the design of the table is flawed. It causes huge problems for that reason alone and it makes performance a disaster. If you have any control over the design of the tables and how the data is stored, fix this as soon as possible.