Convert ANSI-SQL to T-SQL
-
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]
-
My initial reaction is two-fold. One, are you jumping between databases? Two, is the BETWEEN @Date + '00:00:00' working?
What error are you getting?
I wasn't born stupid - I had to study.
-
I am not getting error.in fact I have return result of 50 rows. I just want to know how can this be done in T-SQL. Any ideas?
-
Not sure what it is you want to change about this query? The direction of the joins?
This might help:
I wasn't born stupid - I had to study.
-
I am trying to replace the inner join, with = , right join with =* , and left join with *=. Any ideas?
-
I am trying to replace the inner join, with = , right join with =* , and left join with *=. Any ideas?
-
You do NOT want to do that! That style of join “results in an ambiguous query that can be interpreted in more than one way.” (Microsoft SQL Server Books On Line : Transact_SQL Joins)
http://www.microsoft.com/sql/techinfo/tips/development/July23.asp
“One thing to be aware of is that you should not expect to be able to mechanically convert an old-style OUTER JOIN to an ANSI-style JOIN and receive the same output. You might not replicate the results, because the SQL Server old-style JOIN executes the filtering conditions before executing the joins, whereas the ANSI-style JOIN reverses this procedure (join logic precedes filtering).”
I wasn't born stupid - I had to study.
-
@Date + ' 23:59:59' performs date arithmentic calculations: adds to the @date 23 h, 59 min and 59 secs...
------------
When you 've got a hammer, everything starts to look like a nail... -
question for you is LEFT JOIN and LEFT OUTER JOIN produce the same result are the same meaing? Same to RIGHT JOIN and RIGHT OUTER JOIN, and FULL JOIN and FULL OUTER JOIN ?
-
I know. I just thought there was an error happening with this and thought that might be the location.
Thanks
I wasn't born stupid - I had to study.
-
ok... Farrel...Now, for the JOIN syntax question... the syntax is:
< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
[ < join_hint > ]
JOIN
where:
< join_type >Specifies the type of join operation.
INNER: Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
FULL or FULL OUTER: Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows usually returned by the INNER JOIN.
Note: It is possible to specify outer joins as specified here or by using the old nonstandard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement.
LEFT or LEFT OUTER:Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
RIGHT or RIGHT OUTER:Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.
I hope this gives u a clue.
PS: And a few words from BOL:
It is recommended that you remove all references of the left outer join (*=) and right outer join (=*) operators in all SELECT statement FROM clauses and replace with references to the SQL-92-standard syntax RIGHT OUTER JOIN and LEFT OUTER JOIN. Future versions of SQL Server will support only the SQL-92-standard syntax.
------------
When you 've got a hammer, everything starts to look like a nail...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply