Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 PLEASE Help to convert an access Query to sql quer

Author  Topic 

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-02 : 05:08:18
Here is the access query

SELECT tbl1.evenID, Sum(IIf(IsNull([evpeDateCompleted]),0,1)) AS Completions, Count(tbl2.evpeID) AS Attendees, IIf(Count([evpeID])=0,"No Bookings",IIf(Count([evpeID])=Sum(IIf(IsNull([evpeDateCompleted]),0,1)),"Closed",IIf(Sum(IIf(IsNull([evpeDateCompleted]),0,1))>0,"Closing","In Progress"))) AS Status
FROM tbl1 LEFT JOIN tbl2 ON tbl1.evenID=tbl2.evpeEvenID
GROUP BY tbl1.evenID;

coding is my passion

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 05:22:54
Think you just need to chage the iif's to case statements
IIf(Count([evpeID])=0,"No Bookings",IIf(Count([evpeID])=Sum(IIf(IsNull([evpeDateCompleted]),0,1)),"Closed",IIf(Sum(IIf(IsNull([evpeDateCompleted]),0,1))>0,"Closing","In Progress")))

case when Count([evpeID])=0 then "No Bookings" else case when Count([evpeID])=Sum(case when IsNull([evpeDateCompleted]) then 0 else 1 end end then "Closed" else case when Sum(case when IsNull([evpeDateCompleted]) then 0 else 1 end end >0 then "Closing" else "In Progress" end end end

Probably not right but I'm sure you'll get the idea.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-03 : 23:56:36
yeah, i also use the case when statement but didn't get the correct result;

probanbly due to the incorrect nesting of conditions.

coding is my passion
Go to Top of Page
   

- Advertisement -