locked
Count reports RRS feed

  • 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,

    Sunday, December 28, 2014 8:32 AM

All replies

  • 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

    Monday, December 29, 2014 6:07 AM
  • 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

    Thursday, January 1, 2015 4:54 AM
  • 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

    Thursday, January 1, 2015 9:17 PM
  • User1309273214 posted

    Hi Dillion,

    Right you are - now it works :-)

    Thanks a lot, and best regards

    Friday, January 2, 2015 3:30 AM
  • 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

    Thursday, January 22, 2015 3:41 AM