Count reports

Question

User1309273214 posted

Hi,

I am using this SQL query to count number of reports filed per instructor, on year back. I need to alter it so no reports filed prior to 01-01-2015 will be counted – how do I achive that ?

WITH CTE_C AS (SELECT DISTINCT inst, orep, DATEDIFF(dd, 0, dato) AS DayNum FROM orep WHERE (dato > DATEADD(DAY, - 365, GETDATE()))) SELECT inst, SUM(CASE WHEN orep IN ('A' , 'C' , 'E') THEN 2 ELSE 1 END) AS Total FROM CTE_C AS CTE_C_1 GROUP BY inst ORDER BY Total DESC

Best regards,

Answer 1

User-271186128 posted

Hi ricas,

From your description, if you want to count the number of the reports, you could use the Count function. If you want to get the count for per instructor, you could use group by statement. If you want to select the data by date, you could use where statement. Here is a sample using Northwind database, you could refer to it.

select EmployeeID, COUNT(*) as OrderCount from Orders where OrderDate>'1997-1-1' group by EmployeeID order by OrderCount 

Best Regards,
Dillion

Answer 2

User1309273214 posted

Hi Dillion,

Thanks ! The query is using the count function and works fine. I need it to filter out any reports filed prior to 01-01-2015; I have tried this:

WITH CTE_C AS (SELECT DISTINCT inst, orep, DATEDIFF(dd, 0, dato) AS DayNum
                                     FROM            orep
                                     WHERE        (dato > DATEADD(DAY, - 365, GETDATE())))
    SELECT        inst, SUM(CASE WHEN orep IN ('A', 'C', 'E') THEN 2 ELSE 1 END) AS Total
     FROM            CTE_C AS CTE_C_1
     WHERE        (dato > 01 - 01 - 2014)
     GROUP BY inst
     ORDER BY Total DESC

.....the where statement does not work for some reason: "Unknown column name dato" - cannot understand why ?

Best regards

Answer 3

User-271186128 posted

Hi ricas,

As for this issue, I suppose the reason is that the temporary table doesn't contains the dato column. So, please try to modify your code as below:

WITH CTE_C AS (SELECT DISTINCT inst, orep, DATEDIFF(dd, 0, dato) AS DayNum, dato
                                      FROM            orep
                                      WHERE        (dato > DATEADD(DAY, - 365, GETDATE())))
     SELECT        inst, SUM(CASE WHEN orep IN ('A', 'C', 'E') THEN 2 ELSE 1 END) AS Total
      FROM            CTE_C AS CTE_C_1
      WHERE        (dato > 01 - 01 - 2014)
      GROUP BY inst
      ORDER BY Total DESC

Best Regards,
Dillion

Answer 4

User1309273214 posted

Hi Dillion,

Right you are - now it works :-)

Thanks a lot, and best regards

Answer 5

User1309273214 posted

Hi ricas,

As for this issue, I suppose the reason is that the temporary table doesn't contains the dato column. So, please try to modify your code as below:

WITH CTE_C AS (SELECT DISTINCT inst, orep, DATEDIFF(dd, 0, dato) AS DayNum, dato
                                      FROM            orep
                                      WHERE        (dato > DATEADD(DAY, - 365, GETDATE())))
     SELECT        inst, SUM(CASE WHEN orep IN ('A', 'C', 'E') THEN 2 ELSE 1 END) AS Total
      FROM            CTE_C AS CTE_C_1
      WHERE        (dato > 01 - 01 - 2014)
      GROUP BY inst
      ORDER BY Total DESC

Best Regards,
Dillion

Hi,

I have changed the WHERE clause to read (dato>01-01-2015), but reports filed after this date is not counted ? why ?

Best regards