Question: I have a MS Access query which is running fine, but I don't know how to write this query in SQL Server.

MS Access query:-

```
SELECT lp.PortfolioID, 
       Count(IIf(lp.Data_Status = 'Prelim Collection', 1, Null)) AS Prelim_Collection_Cnt, 
       Count(IIf(lp.Data_Status = 'Final Collection', 1, Null)) AS Final_Collection_Cnt, 
       Count(IIf(lp.Data_Status = 'Prelim Entry', 1, Null)) AS Prelim_Entry_Cnt, 
       Count(IIf(lp.Data_Status = 'Final Entry', 1, Null)) AS Final_Entry_Cnt, 
       Count(IIf(lp.Data_Status = 'Prelim QC', 1, Null)) AS Prelim_QC_Cnt, 
       Count(IIf(lp.Data_Status = 'Final QC', 1, Null)) AS Final_QC_Cnt
FROM (
      SELECT DISTINCT p.PortfolioID, p.Data_Status 
      FROM Late_Portfolios AS p 
      WHERE p.TimeStamp BETWEEN #10/01/2018# AND #10/31/2018#)  AS lp
GROUP BY lp.PortfolioID;

```

Answer: 
Your best bet is switching over to CASE statements as those will work in nearly every RDBMS under the sun (except Access... I can't imagine why it still doesn't support this, but that's MS for you). Other than that, you want to switch your date literals to ISO8601 format `YYYY-MM-DD` and encapsulate them in single quotes instead of hashes. 

```
SELECT lp.PortfolioID,  
    COUNT(CASE WHEN lp.Data_Status = 'Prelim Collection' THEN 1 END) AS Prelim_Collection_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Final Collection' THEN 1 END) AS Final_Collection_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Prelim Entry' THEN 1 END) AS Prelim_Entry_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Final Entry' THEN 1 END) AS Final_Entry_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Prelim QC' THEN 1 END) AS Prelim_QC_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Final QC' THEN 1 END) AS Final_QC_Cnt
FROM
    (
        SELECT DISTINCT 
            p.PortfolioID, 
            p.Data_Status 
        FROM Late_Portfolios AS p 
        WHERE p.TimeStamp BETWEEN '2018-10-01' AND '2018-10-31'
    )  AS lp
GROUP BY lp.PortfolioID;

```
The logic should also work in `SQL Server`, if you have a lower version then you can do conditional aggregation instead :

```
select lp.PortfolioID,
       sum(case when lp.Data_Status = 'Prelim Collection' then 1 else 0 end),
from ( <query> )  AS lp
group by lp.PortfolioID;

```

Just pass your date in `20180110` format `ANSI SQL`:

```
where p.TimeStamp >= '20180110' AND p.TimeStamp >= '20181031'

```