January 29, 2008 at 7:32 am
JMSM (1/28/2008)
Sorry, but now i'm feeling completely donkey :crying:I cant understand what changes do you want to make, the code that i changed give me errors when executing query. Was the code that i'm sending you in bold that i should change?
Once more i need help.
I'm so greatfull to all of you.
Regards,
Jorge Mendes:sick:
FROM Tbl_Tmp_Trace tb4 INNER JOIN
Tbl_Tmp_Trace tb5 ON tb4.ID = tb5.ID AND tb4.Tipo = tb5.Tipo INNER JOIN
Tbl_Tmp_Trace tb6 ON tb4.ID = tb6.ID AND tb4.Tipo = tb6.Tipo
LEFT JOIN (SELECT tb7.Estadoid FROM Tbl_Tmp_Trace WHERE tb7.Estadoid = 3094610094 AND tb7.Tipo = N'Chamadas' AND tb7.ID = tb4.ID) AS X ON tb4.ID = X.Estadoid
WHERE tb4.Tipo = N'Chamadas'AND tb4.Data < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND tb5.EstadoID = 309461009 AND X.Estadoid IS NULL
GROUP BY tb4.Tipo,
tb4.ID,
tb4.Data,
tb4.EquipaID,
ISNULL(tb4.PessoaID, 0),
tb4.EstadoID
go
In the Left Join part, you'll get an error message because you're using "tb7" as a table alias, but you don't set the alias in the From part of the subquery.
You're also going to get a problem from having "AND tb7.ID = tb4.ID" in the Where of the subquery. Why not have that in the Select of the subquery?
The join to the subquery also seems strange to me, because I don't see a relationship between EstadoID and the ID for "tb4" in the original query.
Try it the way I wrote it in my earlier suggestion, without a derived table, just a simple join with a couple of clauses, and see if that works better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2008 at 2:59 am
Thanks a lot everybody, you're always so kind.:cool:
(In attach i send you the situation before and the solution that i use with your help)
Regards,
Jorge Mendes 😉
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply