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 RDateFROM DLDT d,SCHDjoin (select DEALNO, min(INTENDDTE) as INTENDDTE from SCHD where INTENDDTE >= dateadd(day,-1,getdate()) group by DEALNO) as son 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 son 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 thisThanks |
|
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 RDateFROM 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
|
|