SQL Server Performance Forum – Threads Archive
how to convert to SQL query
USE testatabase<br />SELECT * FROM <br />(SELECT t.PartnerKEY, d.DocumentName, d.ReferenceData, <br /> d.FunctionalGroupID, t.InterchangeControlNumber,<br /> t.GroupControlNumber, i.TimeSent, d.ComplianceStatus,<br /> sysdate – to_date(trunc(i.TimeSent/86400, 0) + 2440588, ‘J’) AS DAYS<br /> FROM Track_tb t, Document_tb d, Interchange_tb i<br /> WHERE t.DocumentKEY = d.DocumentKEY<br />AND t.InterchangeKEY = i.InterchangeKEY<br />AND d.ComplianceStatus != 2<br />AND d.ComplianceStatus != 4<br />AND d.ComplianceStatus != 7 <br />AND d. FunctionalGroupID != ‘FA’) temptable<br />WHERE DAYS <= 14 <br /> ORDER BY PartnerKEY, InterchangeControlNumber, DocumentName<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
1. What is your question. Be more specific<br />2. What are the functinalities of trunc and to_date<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by bazeemuddin</i><br /><br />USE testatabase<br />SELECT * FROM <br />(SELECT t.PartnerKEY, d.DocumentName, d.ReferenceData, <br /> d.FunctionalGroupID, t.InterchangeControlNumber,<br /> t.GroupControlNumber, i.TimeSent, d.ComplianceStatus,<br /> sysdate – to_date(trunc(i.TimeSent/86400, 0) + 2440588, ‘J’) AS DAYS<br /> FROM Track_tb t, Document_tb d, Interchange_tb i<br /> WHERE t.DocumentKEY = d.DocumentKEY<br />AND t.InterchangeKEY = i.InterchangeKEY<br />AND d.ComplianceStatus != 2<br />AND d.ComplianceStatus != 4<br />AND d.ComplianceStatus != 7 <br />AND d. FunctionalGroupID != ‘FA’) temptable<br />WHERE DAYS <= 14 <br /> ORDER BY PartnerKEY, InterchangeControlNumber, DocumentName<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
Hi Dinesh Thanks That script was written for ORACLE, works OK in SQLPLUS. I am moving that script from ORACLE to SQL. So What changes I need to make that query run in SQL Server. Thanks
Most of this would work as is, except the date calculation. Not knowing for sure how your dates are stored, I’m not sure totally how to do it. If timesent is in seconds and you want to convert to days, then what I put in below should do it. If its something else, let us know. USE testatabase SELECT t.PartnerKEY, d.DocumentName, d.ReferenceData,
d.FunctionalGroupID, t.InterchangeControlNumber,
t.GroupControlNumber, i.TimeSent, d.ComplianceStatus,
–sysdate – to_date(trunc(i.TimeSent/86400, 0) + 2440588, ‘J’) AS DAYS
int(i.timesent/86400) AS DAYS
FROM Track_tb t join Document_tb d on t.DocumentKEY = d.DocumentKEY
join Interchange_tb i on t.InterchangeKEY = i.InterchangeKEY
WHERE d.ComplianceStatus not in (2,4,7)
AND d. FunctionalGroupID != ‘FA’
AND int(i.timesent/86400) > 14
ORDER BY PartnerKEY, InterchangeControlNumber, DocumentName
]]>