• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Improve query with different syntax ?

I have been working on this query for a while now, this is as short as I can make it but I am not sure it is the best approach, it takes about 3 min to return the results. Any advice on how to use a different approach or syntax is greatly appreciated

----

SELECT
  t0.activityid as id,
  t0.ActName as actdesc,
  t0.dateinitiated as datestarted,
  t0.lastmodified as datefinished,
  t0.processtep as repeat,
  t0.Partylist as apptype
FROM
(
  SELECT activityid,ActName,dateinitiated,lastmodified, processtep, Partylist, sent_by, CaseId  
  FROM dbo.Activities
  WHERE Activities.FirmId = 2
    AND  (Activities.ActType = 'Calendar')  
    AND sent_by = 1713
) t0
LEFT JOIN dbo.Cases
     ON t0.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts
  ON dbo.Atts.CaseId = dbo.Cases.Id    
INNER JOIN Users
  ON sent_by = Users.UserId

----
0
Aleks
Asked:
Aleks
  • 2
  • 2
1 Solution
 
Pratima PharandeCommented:
 SELECT
  t0.activityid as id,
  t0.ActName as actdesc,
  t0.dateinitiated as datestarted,
  t0.lastmodified as datefinished,
  t0.processtep as repeat,
  t0.Partylist as apptype
  FROM dbo.Activities t0
 INNER JOIN Users
  ON sent_by = Users.UserId
LEFT JOIN dbo.Cases
     ON t0.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts
  ON dbo.Atts.CaseId = dbo.Cases.Id  
 WHERE Activities.FirmId = 2
    AND  (Activities.ActType = 'Calendar')  
    AND sent_by = 1713
0
 
AleksAuthor Commented:
That query returns this errors:

Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "Activities.FirmId" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "Activities.ActType" could not be bound.
0
 
Pratima PharandeCommented:
 SELECT
  t0.activityid as id,
  t0.ActName as actdesc,
  t0.dateinitiated as datestarted,
  t0.lastmodified as datefinished,
  t0.processtep as repeat,
  t0.Partylist as apptype
  FROM dbo.Activities t0
 INNER JOIN Users
  ON sent_by = Users.UserId
LEFT JOIN dbo.Cases
     ON t0.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts
  ON dbo.Atts.CaseId = dbo.Cases.Id  
 WHERE t0.FirmId = 2
    AND  (t0.ActType = 'Calendar')  
    AND sent_by = 1713
0
 
AleksAuthor Commented:
Yeah, that works, thanks so much !
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now