SQL Linked tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Linked tables

Author

Topic

Blossomthe2nd
Starting Member

1 Post

Posted - 2011-02-09 : 10:24:00

Hi there,
I'm a first time poster!!!!
I've written an SQL query as below and it works !!!
<<<<<
SELECT d.BR, d.DEALNO, d.RATECODE, convert(varchar, d.VDATE,103) as vdate, convert(varchar,d.MDATE,103) as mdate,
d.CCY, d.CCYAMT, d.INTRATE, d.BASIS, d.DEALTEXT, d.PRODUCT, d.PRODTYPE, d.CNO,convert(varchar,SCHD.INTENDDTE,103) as RDate
FROM DLDT d,SCHD
join (select DEALNO, min(INTENDDTE) as INTENDDTE from SCHD where INTENDDTE >= dateadd(day,-1,getdate()) group by DEALNO) as s
on d.DEALNO = s.DEALNO
where CNO in ("12705",
"1750574",
"1800605",
"1800606",
"3206101",
"3206102",
"3256795")
and SCHD.INTENDDTE >= getdate() and SCHD.INTENDDTE < dateadd(day,50,getdate())
and D.REVDATE is Null
>>>>

My issue is although it is returning the correct data stream I would like to make another column from the below portion of the query.
<<
join (select DEALNO, min(INTENDDTE) as INTENDDTE from SCHD where INTENDDTE >= dateadd(day,-1,getdate()) group by DEALNO) as s
on d.DEALNO = s.DEALNO
>>
I would like the result of this portion be called Mdate to replace
<<
convert(varchar,d.MDATE,103) as mdate
>>
that part of the query.

Can anyone help ? I'm so new to this

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-14 : 05:51:42

Simply this?


SELECT
	d.BR
	, d.DEALNO
	, d.RATECODE
	, CONVERT(VARCHAR, d.VDATE,103) AS vdate
	-- , CONVERT(VARCHAR, d.MDATE,103) AS mdate
	, s.[INTENDDTE] AS [mdate]
	, d.CCY
	, d.CCYAMT
	, d.INTRATE
	, d.BASIS
	, d.DEALTEXT
	, d.PRODUCT
	, d.PRODTYPE
	, d.CNO
	, CONVERT(VARCHAR,SCHD.INTENDDTE,103) AS RDate
FROM
	DLDT d,SCHD
	JOIN (
		SELECT
			DEALNO, MIN(INTENDDTE) AS INTENDDTE
		FROM
			SCHD
		WHERE
			INTENDDTE >= DATEADD(DAY,-1,GETDATE())
		GROUP BY
			DEALNO
		)
		AS s ON d.DEALNO = s.DEALNO 
WHERE
	CNO IN (
		"12705"
		, "1750574"
		, "1800605"
		, "1800606"
		, "3206101"
		, "3206102"
		, "3256795"
		)
		
	AND SCHD.INTENDDTE >= GETDATE()
	AND SCHD.INTENDDTE < DATEADD(DAY,50,GETDATE())
	AND D.REVDATE IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources