Daily report with stored procedure

Question

I have this sp to make a report which needs to be daily. How can I implement the day part? It is OK like I wrote or that is some easy way?!

ALTER PROCEDURE [dbo].[pr_Report]
    @YearOfRegistration INT
AS 
    SELECT
        peCountryID,
        peCountryName as coName,
        ISNULL(SUM(CASE WHEN peIsSubmittedFL = 1 THEN 1 ELSE 0 END ),0) AS rdValue1,
        ISNULL(SUM(CASE WHEN peIsSubmittedFL = 0 THEN 1 ELSE 0 END ),0) AS  rdValue2,
        COUNT(*) AS Total
    FROM 
        vPerson
    WHERE 
        @YearOfRegistration = 0 
        OR peYearOfRegistration = @YearOfRegistration
        AND (DATEPART(dd, peSubmitDate) = DATEPART(dd, GETDATE()) 
        AND DATEPART(MM, peSubmitDate) = DATEPART(MM, GETDATE()) 
        AND DATEPART(yy, peSubmitDate) = DATEPART(YY, GETDATE()))
    GROUP BY 
        peCountryofResidencyID, peCountryOfResidencyName

GarethD · Accepted Answer · 2017-05-22T14:54:00.553

The logic is correct, but it is a really bad way to do it. Wherever possible to you should avoid calling functions on your data, especially in the where clause, because it means that any indexes on the underlying columns can not be used.

Your predicate would be better written as:

WHERE   peSubmitDate >= CAST(GETDATE() AS DATE)
AND     peSubmitDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE));

This way indexes can be used, and your query is sargable

As it happens, converting DATETIME to DATE (and vice versa) is actually an exception to the rule of not using functions, so you can shorten this to:

WHERE   CONVERT(DATE, peSubmitDate) = CONVERT(DATE, GETDATE())

Another point is that although it might look better to use OR to accommodate both your options (of filtering by year or returning all records), you will find that having two separate queries will perform better. so your final SP might be:

ALTER PROC [dbo].[pr_Report] @YearOfRegistration INT
AS
BEGIN
    IF (@YearOfRegistration = 0)
    BEGIN
        SELECT  peCountryID,
                peCountryName as coName,
                ISNULL(SUM(CASE WHEN peIsSubmittedFL = 1 THEN 1 ELSE 0 END ),0) AS rdValue1,
                ISNULL(SUM(CASE WHEN peIsSubmittedFL = 0 THEN 1 ELSE 0 END ),0) AS  rdValue2,
                COUNT(*) AS Total
        FROM    vPerson
        WHERE   CONVERT(DATE, peSubmitDate) = CONVERT(DATE, GETDATE())  
        GROUP BY peCountryofResidencyID,peCountryOfResidencyName
    END
    ELSE
    BEGIN
        SELECT  peCountryID,
                peCountryName as coName,
                ISNULL(SUM(CASE WHEN peIsSubmittedFL = 1 THEN 1 ELSE 0 END ),0) AS rdValue1,
                ISNULL(SUM(CASE WHEN peIsSubmittedFL = 0 THEN 1 ELSE 0 END ),0) AS  rdValue2,
                COUNT(*) AS Total
        FROM    vPerson
        WHERE   CONVERT(DATE, peSubmitDate) = CONVERT(DATE, GETDATE())  
        AND     peYearOfRegistration = @YearOfRegistration
        GROUP BY peCountryofResidencyID,peCountryOfResidencyName;

    END
END

"Wherever possible to you should avoid calling functions,especially in the where clause, because it means that any indexes on the underlying columns can not be used" is in direct contradiction to your suggestion of using `CAST()`, `DATEADD()` and `GETDATE()` in a `WHERE` clause. Perhaps you can clarify it for the few who might be perplexed. — HABO, May 22 '17 at 14:33
I have already stated that cast and convert between datetime and date are exceptions to this rule, and `GETDATE()` is a runtime constant, so it is calculated once at runtime, and not for every row returned. So `DATEADD(DAY, 1, Column) is evaluated once for every column, but `DATEADD(DAY, 1, GETDATE())` is evaluated once per query so does not adversely affect performance. — GarethD, May 22 '17 at 14:53
My concern was that some people will come away hearing only "**NEVER** use functions in a `where` clause." Functions applied to _columns_ may affect sargability. As for `GetDate()`, each _instance_ is a separate runtime constant, hence the two calls in your first example can return different values. (Some discussion of the feature may be found [here](https://stackoverflow.com/q/12078202/92546).) Woe to the lackey trying to debug a report run near midnight when the two calls returned different dates. — HABO, May 22 '17 at 16:27

score 0 · Answer 2 · answered May 22 '17 at 10:31

0

getdate returns a datetime, so if you want to compare just the date and not the time of day, you could use cast. If peSubmitDate is of datatype Date , use this comparison:

peSubmitDate  = cast(GetDate() as date)

If it is a datatime then use it like this: cast(peSubmitDate as date) = cast(GetDate() as date)

The latter one gives worse performance, so only use this if is a datetime

answered May 22 '17 at 10:31

Søren Kongstad

1,405
9
14

Thanks to both of you, one more question - how I can merge in one sp this select statement with peSubmitDate clause and another one in general (without Date clause)? – koda5 May 22 '17 at 12:43

Daily report with stored procedure

2 Answers2