CODEWITHSUNDEEP
sqlsql-serverfunctiondatetimeview
Aarón Henríquez
Aarón Henríquez

Reputation: 33

Create SQL Function view with User ID and Date range

As I title says, I need to create a Function View of two tables.

Below are the SQL Tables

CREATE TABLE User_Specialist(
        ID_User_Specialist      INT         NOT NULL,
        Name_User_Specialist    VARCHAR(50) NOT NULL,

CONSTRAINT PK_ID_User_Specialist    PRIMARY KEY(ID_User_Specialist),
GO

CREATE TABLE Incident(
    ID_Incident                 INT         IDENTITY(1,1)   NOT NULL,
    Incident_Creation_Date      DATETIME                    NULL,
    Assigned_Specialist         INT                         NULL,
    CONSTRAINT FK_Assigned_Specialist   FOREIGN KEY (Especialista_Asignado) REFERENCES Usuario_Especialista(ID_Usuario_Especialista),
GO

Based on the previous information, I need the function to display Assign Specialist and the dates that the Incident was created.

Right know this is what I got: 

CREATE FUNCTION View_Date (@ID_User_Incident INT)
    RETURNS INT
      AS
        BEGIN 
          DECLARE @Total_Incidents INT
            SELECT @Total_Incidents = COUNT(ID_Incident)
              FROM Incidents i, User_Specialist u
            WHERE i.ID_Incident = u.ID_User_Specialist AND u.ID_User_Specialist =@ID_User_Incident 
          RETURN (@Total_Incidents)
        END
GO

DECLARE @Specialist_ID int;
EXEC @Specialist_ID = [dbo].View_Date
    @ID_Incidentes_Usuarios = 5;
SELECT @Specialist_ID AS 'Assigned Specialist Incidents'
GO

The only thing missing is the dates range.

Upvotes: 0

Views: 281

Answers (2)

Zeki Gumus
Zeki Gumus

Reputation: 1484

I believe you changed your table and field names to English from Spanish. You have missed some of them and there were some missing parenthesis. As much as I understand I changed them to understand better. 

CREATE TABLE User_Specialist
(
        ID_User_Specialist      INT         NOT NULL,
        Name_User_Specialist    VARCHAR(50) NOT NULL
        CONSTRAINT PK_ID_User_Specialist  PRIMARY KEY(ID_User_Specialist)
)
GO

CREATE TABLE Incident
(
    ID_Incident                 INT         IDENTITY(1,1)   NOT NULL,
    Incident_Creation_Date      DATETIME                    NULL,
    Assigned_Specialist         INT                         NULL
    CONSTRAINT FK_Assigned_Specialist   FOREIGN KEY (Assigned_Specialist) REFERENCES User_Specialist(ID_User_Specialist)
)
GO

If you want to see multiple columns or rows as result you need to use Table-valued Function. I have created sample query from your table and prepared function. you can change it with what you want.

CREATE FUNCTION dbo.FN_ViewDate
(
    @ID_User_Incident INT
)
RETURNS @Result TABLE 
(
     TotalIncidents     INT
    ,FirstIncidentDate  DATETIME
    ,LastIncidentDate   DATETIME
)
AS
BEGIN
    INSERT INTO @Result
    SELECT   COUNT(ID_Incident)
            ,MIN(Incident_Creation_Date)
            ,MAX(Incident_Creation_Date)
    FROM Incident I
    LEFT JOIN User_Specialist U ON I.Assigned_Specialist = U.ID_User_Specialist
    WHERE I.Assigned_Specialist = @ID_User_Incident 

    RETURN 
END
GO

Upvotes: 1

Anson Aricatt
Anson Aricatt

Reputation: 393

I think this query can help you.

declare @count int = 0 , @dates varchar(200) ='' 
  SELECT  @count+=1 , @dates +=' '+ i.Incident_Creation_Date      
              FROM Incidents i, User_Specialist u
            WHERE i.ID_Incident = u.ID_User_Specialist AND u.ID_User_Specialist =@ID_User_Incident 

            select @count as [count] ,@dates [incidentDates]

Upvotes: 0

Related Questions