Stored procedure wrong output

user5448913

I have this stored procedure that returns data on a selected week, as in the Company, Name, Expected Work, and Work done between the 19/10/2015 and the 25/10/2015 (per example).

I have just recently added the Expected Work line and for some odd reason the output differs from one week to another, when the value should be the same.

Company1 - Christopher - 35 - 35 | On one week can give the following on another :

Company1 - Christopher - 350 - 35

I have just realized the value isn't correct when there is a value to Work done, if there is no work recorded the Expected Work has the right value.

Here is the procedure :

ALTER procedure [dbo].[spGetWeeklyActivityByEmployee]
      @startDate date
    , @endDate date
as
    set datefirst 1 -- Monday

    select 
        Company.Name as [Company]
        , Employee.FirstName + ' ' + Employee.LastName as [Name]        
        , sum(UserActivity.Cost) as [Recorder Time]
        , sum(Employee.ExpectedTime) as [Expected Time] // I have added this line, not sure if it's correct
    from 
        dbo.Employee
    inner join 
        dbo.Company on Company.CompanyId = Employee.CompanyId
    left join 
        dbo.UserActivity on UserActivity.Login = Employee.Login
                         and UserActivity.CalendarDate >= @startDate
                         and UserActivity.CalendarDate <= @endDate
    where 
        (Employee.EntranceDate is null
         or YEAR(Employee.EntranceDate) < YEAR(@startDate)
         or (YEAR(Employee.EntranceDate) = YEAR(@startDate) 
         and DATEPART(WEEK, Employee.EntranceDate) <= DATEPART(WEEK, @startDate)))
        and (Employee.ExitDate is null
             or YEAR(Employee.ExitDate) > YEAR(@endDate)
             or (YEAR(Employee.ExitDate) = YEAR(@endDate) 
             and DATEPART(WEEK, Employee.ExitDate) >= DATEPART(WEEK, @endDate)))
    group by 
        Company.Name, Employee.FirstName + ' ' + Employee.LastName

    return 0

Am I missing something? Is the way I retrieve Expected Time wrong?

EDIT :

Here is the part in the code where I save the information in an array :

// create and open a connection object
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
conn.Open();

// 1.  create a command object identifying
//     the stored procedure
SqlCommand cmd = new SqlCommand("spGetWeeklyActivityByEmployee", conn);

// 2. set the command object so it knows
//    to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

// 3. add parameter to command, which
//    will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@startDate", wk1));
cmd.Parameters.Add(new SqlParameter("@endDate", wk2));

// execute the command
rdr = cmd.ExecuteReader();         

string[] tab_company = new string[31]; // Don't mind the sizes
string[] tab_name = new string[31];
string[] tab_expectedtime = new string[31];
string[] tab_rectime = new string[31];

int counter;

counter = 0;

while (rdr.Read())
{
    tab_company[counter] = rdr["Company"].ToString();
    tab_name[counter] = rdr["Name"].ToString();
    tab_expectedtime[counter] = rdr["Expected Time"].ToString();
    tab_rectime[counter] = rdr["Recorder Time"].ToString();

    counter++;               
}

Perhaps the change in value comes from here?

Shnugo

Just take away the SUM() from Employee.ExpectedTime and add this to your GROUP BY

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Stored procedure and print output

From Dev

Stored procedure returning the wrong value?

From Dev

Stored procedure in mySQL going wrong

From Dev

How to get the output of a stored procedure?

From Dev

Selecting from stored procedure output

From Dev

Stored procedure VS query output

From Dev

Return output from stored procedure

From Dev

What's wrong with this SQL Server stored procedure?

From Dev

MySQL stored procedure returns wrong values

From Dev

What's wrong with this INSERT stored procedure

From Dev

stored procedure inserting values in wrong columns

From Dev

data insert using input output stored procedure

From Dev

How to read stored procedure output and return it as list

From Dev

tSQLt - Test that a column is output by a stored procedure

From Dev

Mocking stored procedure's output parameter

From Dev

How to pass output parameter to a Stored Procedure?

From Dev

NVARCHAR(MAX) - As SQL Stored Procedure Output Parameter

From Dev

Quick Help on stored procedure .adding headers to output

From Dev

Stored procedure returns null as output parameter

From Dev

Retrieving stored procedure output parameter via ADO

From Dev

How see the Output of stored Procedure in TOAD

From Dev

Dynamically execute a stored procedure with reference cursor output

From Dev

How to suppress mysql stored procedure output?

From Dev

stored procedure for select query not giving output

From Dev

How to save the output of a stored procedure to a log table?

From Dev

How to pass output parameter to a Stored Procedure?

From Dev

tSQLt - Test that a column is output by a stored procedure

From Dev

Stored procedure's output parameters not updating

From Dev

Inserting output parameters from stored procedure directly

Related Related

  1. 1

    Stored procedure and print output

  2. 2

    Stored procedure returning the wrong value?

  3. 3

    Stored procedure in mySQL going wrong

  4. 4

    How to get the output of a stored procedure?

  5. 5

    Selecting from stored procedure output

  6. 6

    Stored procedure VS query output

  7. 7

    Return output from stored procedure

  8. 8

    What's wrong with this SQL Server stored procedure?

  9. 9

    MySQL stored procedure returns wrong values

  10. 10

    What's wrong with this INSERT stored procedure

  11. 11

    stored procedure inserting values in wrong columns

  12. 12

    data insert using input output stored procedure

  13. 13

    How to read stored procedure output and return it as list

  14. 14

    tSQLt - Test that a column is output by a stored procedure

  15. 15

    Mocking stored procedure's output parameter

  16. 16

    How to pass output parameter to a Stored Procedure?

  17. 17

    NVARCHAR(MAX) - As SQL Stored Procedure Output Parameter

  18. 18

    Quick Help on stored procedure .adding headers to output

  19. 19

    Stored procedure returns null as output parameter

  20. 20

    Retrieving stored procedure output parameter via ADO

  21. 21

    How see the Output of stored Procedure in TOAD

  22. 22

    Dynamically execute a stored procedure with reference cursor output

  23. 23

    How to suppress mysql stored procedure output?

  24. 24

    stored procedure for select query not giving output

  25. 25

    How to save the output of a stored procedure to a log table?

  26. 26

    How to pass output parameter to a Stored Procedure?

  27. 27

    tSQLt - Test that a column is output by a stored procedure

  28. 28

    Stored procedure's output parameters not updating

  29. 29

    Inserting output parameters from stored procedure directly

HotTag

Archive