procedure problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

procedure problem

Author

Topic

dominikm86
Starting Member

5 Posts

Posted - 2007-05-17 : 15:15:23

hi all. here is my problem. i have to make a procedure that accept and month, year and employee number. then i want it too return the employee number, month day and hours. The hours is supposed to equal to the maximum value of the latest day of that month minus the maximum value of the latst day of the previous month. i have been working on it for a long time and here is what i got and it is not quite giving me the right result. it is only supposed to return one row of data and in one case i am getting results and in another case it returns a null in the hours but only one row of data. any help would be appreciated. thanks

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[BankHoursHistory]
(
@YEAR INT,
@MONTH INT,
@EMPID INT
)
AS
DECLARE @CURMONTH INT

SET @CURMONTH = @MONTH
SET @MONTH = @MONTH - 1

IF (@MONTH = 0) BEGIN
SET @MONTH = 12
END

SELECT EMPID,
YEAR(WEEKSTARTDATE) AS STARTYEAR,
MONTH(WEEKSTARTDATE) AS STARTMONTH,
MAX(HOURS) -

(SELECT MAX(HOURS)
FROM BANKHOURS_HISTORY
WHERE EMPID = @EMPID
AND YEAR(WEEKSTARTDATE) = @YEAR
AND MONTH(WEEKSTARTDATE) = @MONTH
AND DAY(WEEKSTARTDATE) =

(SELECT MAX(DAY(WEEKSTARTDATE))
FROM BANKHOURS_HISTORY
WHERE EMPID = @EMPID
AND YEAR(WEEKSTARTDATE) = @YEAR
AND MONTH(WEEKSTARTDATE) = @MONTH)) AS HOURS

FROM BANKHOURS_HISTORY
WHERE EMPID = @EMPID
AND YEAR(WEEKSTARTDATE) = @YEAR
AND MONTH(WEEKSTARTDATE) = @CURMONTH
AND DAY(WEEKSTARTDATE) =

(SELECT MAX(DAY(WEEKSTARTDATE))
FROM BANKHOURS_HISTORY
WHERE EMPID = @EMPID
AND YEAR(WEEKSTARTDATE) = @YEAR
AND MONTH(WEEKSTARTDATE) = @CURMONTH)

GROUP BY YEAR(WEEKSTARTDATE), MONTH(WEEKSTARTDATE), EMPID, HOURS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-17 : 15:28:57

Please provide some sample data and expected output so we can see why you are not getting the expected result.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dominikm86
Starting Member

5 Posts

Posted - 2007-05-17 : 15:47:06

i just realised that it only does not work when i input the 1st month and the 12th month but the 12 month might not be working because there are 2 values that were for the same day. below is a example of some information. when i input year = 2006, month = 12, empid = 1131 i get both the hour values.(btw there is not information for the month before this one.) but if i put year = 2006, month = 1and empid = 1131 i will get all the information but hours = null. i want it too equal -14.50. how this helps.

EMPID_____WEEKSTARTDATE __________HOURS

1131 _____12/23/2006 12:00:00 AM_____15.50
1131 _____12/23/2006 12:00:00 AM_____ 9.00
1131 _____1/27/2007 12:00:00 AM _____ 1.00

dominikm86
Starting Member

5 Posts

Posted - 2007-05-17 : 16:02:35

k, the reason it did not work when i inputted 1 as the month was because when i was iputting the previous month it did not also go to the previous year. but i still do not know why it returned 2 rows when i entered 12 as the month.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources