[SQL] Access => SQL Server

Bonjour,

Je suis actuellement en train de migrer une base Access vers SQL server.
Je dois donc réécrire une bonne partie de mes ordres SQL.
Je bute sur celui-là :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Select distinct Count(tblCCMAction.IdAction) AS [Nb of actions] , Count(IIF(tblCCMAction.ProcessTime <= tblCCMAction.Target, 1, NULL)) AS [Nb actions on time] , Count(IIF(tblCCMAction.ProcessTime <= tblCCMAction.Target, 1, NULL)) / iif(Count(1)=0, 1, Count(tblCCMAction.IdAction)) AS [% actions on timePOURCENTAGE], Count(IIF(tblCCMAction.ProcessTime BETWEEN 0 AND 120, 1, NULL)) AS [<2h] , Count(IIF(tblCCMAction.ProcessTime BETWEEN 121 AND 240, 1, NULL)) AS [<4hours] , Count(IIF(tblCCMAction.ProcessTime BETWEEN 241 AND 1440, 1, NULL)) AS [<1d] , Count(IIF(tblCCMAction.ProcessTime BETWEEN 1441 AND 2880, 1, NULL)) AS [<2d] , Count(IIF(tblCCMAction.ProcessTime > 2880, 1, NULL)) AS [>2 days] FROM (TblCCMDossier INNER JOIN TblCCMAction ON TblCCMDossier.IdDossier = TblCCMAction.IdDossier) where TblCCMDossier.CloseDate >= #05/01/2007# and TblCCMDossier.CloseDate <= #06/11/2007#

---

Comment réécrire les count ?
Merci.

ce n'est pas les count qui te bloque mais la fonction IIF

regardes ce lien

http://access.developpez.com/faq/?page=ExprEtCriteres

Effectivement, ce n'est pas le Count, mais le Iif qui n'existe pas sous SQL Server.
Je pensais remplacer

Code:

---

Count(IIF(tblCCMAction.ProcessTime > 2880, 1, NULL))

---

par

Code:

---

Count(IF (tblCCMAction.ProcessTime > 2880) begin 1 end else NULL)

---

Mais visiblement, c'est pas ça...
Quelqu'un aurait une idée ?

je pense plutot a

Citation:

---

Count(IIF(tblCCMAction.ProcessTime > 2880, 1, NULL))

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11

select case when count(tblCCMAction.ProcessTime)> 2880 then 1 else null end AS [Nb actions ON time], case when count(tblCCMAction.ProcessTime) BETWEEN 0 AND 120 then 1 else null end as [<2h], etc...

---

Merci.
C'était un Case qu'il fallait faire, pas un If.