sql server programming: Why do you have the dates as NCHAR(6) rather than a temporal datatype? It
makes it much more harder to do conversions for every date related
calculations. Based on the DDLs posted, try:

SELECT i1.id, i1.val, CAST( i1.start_date AS DATETIME),
COALESCE( ( SELECT DATEADD( d, -1, MIN( CAST( i2.start_date AS
DATETIME) ) )
FROM tbl_input i2
WHERE i2.id = i1.id
AND CAST( i2.start_date AS DATETIME)
[quoted text, click to view]
CURRENT_TIMESTAMP )
FROM tbl_input i1 ;

--
Anith

Hello,
I have the following table:
CREATE TABLE [dbo].[TBL_INPUT](
[ID] [nchar](10) COLLATE Latin1_General_CI_AI NULL,
[VAL] [real] NULL,
[START_DATE] [nchar](6) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]


INSERT TBL_INPUT(ID,VAL,START_DATE) VALUES('A1 ','0.1','980618')
INSERT TBL_INPUT(ID,VAL,START_DATE) VALUES('A1 ','0.3','991216')
INSERT TBL_INPUT(ID,VAL,START_DATE) VALUES('A1 ','0.2','000120')
INSERT TBL_INPUT(ID,VAL,START_DATE) VALUES('A2 ','4.1','970531')
INSERT TBL_INPUT(ID,VAL,START_DATE) VALUES('A2 ','3.2','000628')
INSERT TBL_INPUT(ID,VAL,START_DATE) VALUES('A2 ','1','060331')

I need a query to get the following result dataset:

CREATE TABLE [dbo].[TBL_OUTPUT](
[ID] [nchar](10) COLLATE Latin1_General_CI_AI NULL,
[VAL] [real] NULL,
[START_DATE] [nchar](6) COLLATE Latin1_General_CI_AI NULL,
[END_DATE] [nchar](6) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]


INSERT TBL_OUTPUT(ID,VAL,START_DATE,END_DATE) VALUES('A1
','0.1','980618','991215')
INSERT TBL_OUTPUT(ID,VAL,START_DATE,END_DATE) VALUES('A1
','0.3','991216','000119')
INSERT TBL_OUTPUT(ID,VAL,START_DATE,END_DATE) VALUES('A1
','0.2','000120','070912')<-----Now date
INSERT TBL_OUTPUT(ID,VAL,START_DATE,END_DATE) VALUES('A2
','4.1','970531','000627')
INSERT TBL_OUTPUT(ID,VAL,START_DATE,END_DATE) VALUES('A2
','3.2','000628','060330')
INSERT TBL_OUTPUT(ID,VAL,START_DATE,END_DATE) VALUES('A2
','1','060331','070912')<---------Now date


Thanks,
GB

Thanks,
GB

[quoted text, click to view]