Convert ANSI-SQL to T-SQL

[fredong]

I have a script below that I need to convert to a T-SQL. Anybody knows how to convert it? Thanks.

declare
@Date varchar(10), @programid int

set @Date = '03/05/2006'
set @ProgramId = 31

--Select @Date = convert(char(10), getdate()-1,101)

select isnull(a.[first name],agdet.[agent id]) agent, *
from
(select [agent id], substring(transfernumber, 1,11) transfernumber, tr.[description] [description], convert(char(8), [start datetime],8) [transfertime], [talk time]
from OPT.DBO.Interactions I
INNER JOIN OPT.DBO.[Telephone Contacts] T
ON I.[Interaction ID] = T.[Interaction Id]
inner join [HRS].[dbo].[transfernumbers] TR
on TransferNumber = TR.DNIS
where [start datetime] between @Date + ' 00:00:00' and @Date + ' 23:59:59'
and [client code] in (select clientid from OPT.DBO.edwrptmapping where programid = @programid)
and [program code] in(select edwprogramid from OPT.DBO.edwrptmapping where programid = @programid)
and I.[interaction type] = 'T' and [final status] = 'C'
and [Center Code] = 'G' and [agent id] is not null and TR.Show = 1 and TR.CIC = 'CR'
) agdet

right join
(select [agent id], sum(case when tr.dnis is not null then 1 else 0 end) transfers, count(*) as calls
from OPT.DBO.Interactions I
INNER JOIN OPT.DBO.[Telephone Contacts] T
ON I.[Interaction ID] = T.[Interaction Id]
left join [HRS].[dbo].[transfernumbers] TR
on TransferNumber = TR.DNIS and TR.Show = 1 and TR.CIC = 'CR'
where [start datetime] between @Date + ' 00:00:00' and @Date + ' 23:59:59'
and [client code] in (select clientid from OPT.DBO.edwrptmapping where programid = @programid)
and [program code] in(select edwprogramid from OPT.DBO.edwrptmapping where programid = @programid)
and I.[interaction type] = 'T' and [final status] = 'C'
and [Center Code] = 'G' and [agent id] is not null
group by [agent id]) agtot
on agdet.[agent id] = agtot.[agent id]
left join OPT.DBO.agents a on agtot.[agent id] = a.[agent id]

order by isnull( a.[first name],agdet.[agent id]), [transfertime]

 

[r937]

that sure looks like T-SQL to me

what parts did you need converted?

you know, you might get better responses in the microsoft sql server forum

r937.com | rudy.ca

 

[fredong]

T-SQL do not use inner join and right outer syntax it should have something like = for inner join or =* for right join. Anybody?

 

[PHV]

WRONG FORUM (as already stated)

 

[r937]

T-SQL do use JOIN syntax, and have did since at least version 7



r937.com | rudy.ca

 

[r937]

i wanna meet the guy who decided to write

sum(case when tr.dnis is not null then 1 else 0 end)

instead of

count(tr.dnis)

r937.com | rudy.ca

 

[fredong]

the dnis colum contained "null" value and you cannot sum with a null value so replace with 0 will be appropriate. By the way question for you is LEFT JOIN and LEFT OUTER JOIN produce the same result are they the same meaing? Same to RIGHT JOIN and RIGHT OUTER JOIN, and FULL JOIN and FULL OUTER JOIN ?

 

[r937]

yes, you can sum with a null, that's how aggregate functions work, they ignore nulls -- try it!

OUTER is an optional keyword

< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
[ < join_hint > ]
JOIN

r937.com | rudy.ca

 

[Golom]

... but you will run into problems with

Select (fld1 + fld2) As SumOfFields ...

If one or both contain NULL.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[r937]

yes, but golom, you know as well as i do that the + is a scalar operator, so that's why COALESCE was invented



r937.com | rudy.ca

 

[Golom]

Rudy

It's been my observation that there's not much that I know as well as you ... but yes, COALESCE does deal with the problem.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[r937]

i disagree, i've seen your posts, there was one in the access forum the other day, a beautiful, long, informative and clear reply

what counts is not what we know, but how we convey it to others

r937.com | rudy.ca

 

[Golom]

Really???

Usually when I go back and read something that I posted that's not just code, my first thought is "I can't believe I wrote such confused C***. Instead I should have said ..."

But thanks for the thought.



[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]