October 26, 2007 at 7:44 am
Hi all,
I am trying to come up with a results set that will display the start and end date concat together.
The table structure is
tank_code varchar(5)
seq_no int
snapshot_time datetime
snapshot_type int
the data values are
"T-01", 1, 05/01/07, 2
"T-01", 1, 05/02/07, 4
type 2 means that it was the begin of a snapshot and 4 means it was the end of the snapshot.
what I need in a single statement if possible is to display the concat value to the snapshot time like below:
05/01/07 05/02/07 as the result when queried from the above table. Any help would be very appricatied.
October 26, 2007 at 7:53 am
Have a look at http://www.sqlservercentral.com/Forums/Topic391111-338-1.aspx
You can find here answers in forms of a user defined function (good for 2000), or a nice trick with xml path for 2005.
Regards,
Andras
October 26, 2007 at 8:05 am
Yes I saw that but can not use that in the context I will be using it. It needs to be a select statement with out the use of a UDF. The development tool will not allow it. Anyone know a way to get this result in a select statement. ??
October 26, 2007 at 8:18 am
If you do not need this on a per row group basis (so you do not need to use "group by"), for one group you can use coalesce, and self-updating select, have a look at http://www.simple-talk.com/community/blogs/philfactor/archive/2007/09/25/37593.aspx#comments
Basically it is along the lines of:
select @a = COALESCE( @a, @a + 'somedatafromtherow', '')
Regards,
Andras
October 26, 2007 at 8:20 am
You can build a select statement like....
select t1.snapshot_time, t2.snapshot_time, cast( varchar(10), t1.snapshot_time, 101) + '-' + cast( varchar(10), t2.snapshot_time, 101) as snapshot_period
from mytable t1 inner join mytable t2 on t1.tank_code = t2.tank_code and t1.seq_no = t2.seqno and t1.snapshot_type = 2 and t2.snapshot_type = 4
--Ramesh
October 26, 2007 at 8:32 am
Ramesh!! You are the man. Thank you very much. That worked perfectly. I owe you one.
tom
October 27, 2007 at 10:32 am
Thanks tom....
--Ramesh
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply