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.
Author |
Topic |
meetabhilash
Starting Member
10 Posts |
Posted - 2008-04-02 : 05:08:18
|
Here is the access querySELECT 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 StatusFROM tbl1 LEFT JOIN tbl2 ON tbl1.evenID=tbl2.evpeEvenIDGROUP 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 statementsIIf(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 endProbably 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. |
|
|
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 |
|
|
|
|
|
|
|