Create User count function

Question

I got this view for the table below:

SELECT count(Incident.ID_Incident) 
    FROM Incident WHERE Incident.End_User = 7
        AND Incident.Date BETWEEN '2018-12-01 07:54:44' AND '2018-12-18 00:00:00';
GO

Table:

CREATE TABLE Incident(
ID_Incident INT         IDENTITY(1,1)   NOT NULL,
Date        DATETIME                    NULL,
End_User    INT                         NOT NULL,

Here are some of the table values:

('2018-12-06 12:33:05', 003),
('2018-12-13 14:32:12', 010),
('2018-12-06 01:26:03', 015),
('2018-12-04 05:38:50', 012),
('2018-12-10 19:13:08', 011),
('2018-12-12 13:28:39', 005),
('2018-12-05 11:57:29', 006),
('2018-12-17 15:28:31', 007),
('2018-12-07 01:39:22', 007),
('2018-12-12 17:01:47', 009),
('2018-12-03 09:54:26', 010);

I need to turn that view into a Function. I dont know why it is not working.

Any advise would be great.

I wrote this:"

CREATE FUNCTION Total_case (@ID_End_User INT)
    RETURNS INT AS
        BEGIN
            DECLARE @ID_End_User_Search     INT

            SELECT @ID_End_User_Search = COUNT(ID_Incident)
                FROM Incident i
                    WHERE i.End_User = @ID_End_User_Search 
                        AND i.Date BETWEEN '2018-12-13 14:32:12' AND '2018-12-18 00:00:00'
                RETURN (@ID_End_User_Search)
            END

GO


DECLARE @SearchValue INT;

EXEC @SearchValue = [dbo].Total_case 
    @ID_End_User = 7;
SELECT @SearchValue AS 'Total case per user.'
GO

Edit: Changed tags and added tables

Not working isn't very descriptive, elaborate please. Please also tag the DBMS you're using SMSS is just a client, though commonly used with SQL Server. The table's structure and sample data might also help. — sticky bit
– sticky bit, Commented Dec 19, 2018 at 13:32
Edit done. I'm still getting the main idea on how to post correctly — Aarón Henríquez
– Aarón Henríquez, Commented Dec 19, 2018 at 13:48
SSMS is just a client application, it has nothing to do with how you write a SQL query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 19, 2018 at 14:06
Can you show in what way this is not working; a wrong result, an error (message) for example? Also look here for advice on how to post an SQL question — Peter Smith
– Peter Smith, Commented Dec 19, 2018 at 14:09
First, why turn the view into a function instead of selecting from it with a WHERE clause? This won't affect performance. If you have a valid reason, eg you want to simplify a complex query, just use that SELECT .. FROM thatView Where ... in the functions body. Use RETURNS TABLE RETURN SELECT ... to convert it into an inline function — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 19, 2018 at 14:11

Zeki Gumus · Accepted Answer · 2018-12-19 14:49:59Z

The problem is you used the variable which you created in function in WHERE clause for filter. It is already empty and because of that you receive 0 result. Change WHERE i.End_User = @ID_End_User_Search to WHERE i.End_User = @ID_End_User then you will see the results. Also is this because of sample you used static date range in the function? If no, this is not efficient function, you need to modify your function every time when you need to change the date range.

This is corrected function :

CREATE FUNCTION Total_case (@ID_End_User INT)
    RETURNS INT AS
        BEGIN
            DECLARE @ID_End_User_Search     INT

            SELECT @ID_End_User_Search = COUNT(ID_Incident)
                FROM Incident i
                    WHERE i.End_User = @ID_End_User
                        AND i.Date BETWEEN '2018-12-13 14:32:12' AND '2018-12-18 00:00:00'
                RETURN (@ID_End_User_Search)
            END

GO

This is better way to create function :

ALTER FUNCTION Total_case (@ID_End_User INT, @DateFrom DATETIME, @DateTo DATETIME)
    RETURNS INT AS
        BEGIN
            DECLARE @ID_End_User_Search     INT

            SELECT @ID_End_User_Search = COUNT(ID_Incident)
                FROM Incident i
                    WHERE i.End_User = @ID_End_User 
                        AND i.Date BETWEEN @DateFrom AND @DateTo
                RETURN (@ID_End_User_Search)
            END

GO

Select for one user only:

SELECT DBO.Total_case(7,'2018-12-01 14:32:12' ,'2018-12-19 00:00:00')

Select for all users:

SELECT *, DBO.Total_case(END_USER,'2018-12-01 14:32:12' ,'2018-12-19 00:00:00') TotalIncidents
FROM    (SELECT DISTINCT End_User 
        FROM Incident ) I

Hainan Zhao · Accepted Answer · 2018-12-19 14:52:28Z

0

There is one error in the WHERE clause.

WHERE i.End_User = @ID_End_User_Search

It should be:

WHERE i.End_User = @ID_End_User

answered Dec 19, 2018 at 14:52

Hainan Zhao

2,11020 silver badges19 bronze badges

Collectives™ on Stack Overflow

Create User count function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related