I need help with this Date stored procedure. 1

[elmorro]

I have written a stored procedure that is suppose to return records from the previous day. I can't get it to work. Can someone please tell me what I am doing wrong?
Here is the Stored Procedure:

CREATE procedure dbo.GetNPT
as
DECLARE @ERRORCODE int
Declare @EndDate DateTime
Set @EndDate = GetDate() - 1

SELECT EmpID, SUM(DURATION) as Duration FROM DC_WrkJobTime2 WHERE (TranDate = @EndDate) GROUP BY EmpID ORDER BY EmpID
GO

Thanks in advance,
elmorro

 

[AlexCuse]

Guess #1 - TranDate is a varchar column rather than datetime.

You really should have a look at one of the FAQ on effective posting, like this one faq222-2244

You have not really provided much information here on your problem. Sample data and table information would be helpful

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Try this...

[COLOR=blue]CREATE[/color] [COLOR=blue]procedure[/color] dbo.GetNPT
[COLOR=blue]as[/color]
[COLOR=blue]DECLARE[/color] @ERRORCODE [COLOR=blue]int[/color]
[COLOR=blue]Declare[/color] @EndDate [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]Set[/color]     @EndDate = [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0)

[COLOR=blue]SELECT[/color]  EmpID, 
        SUM(DURATION) [COLOR=blue]as[/color] Duration 
[COLOR=blue]FROM[/color]    DC_WrkJobTime2 
[COLOR=blue]WHERE[/color]   TranDate >= @EndDate - 1
        And TranDate < @EndDate
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] EmpID 
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] EmpID

If this works, and you want an explanation, just ask.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

GETDATE() returns current date AND time, if you want to get all records from previous day you could do:

CREATE procedure dbo.GetNPT
as
DECLARE @ERRORCODE int
Declare @EndDate DateTime
Set @EndDate = GetDate() - 1
Set @EndDate = DATEADD(day, DATEDIFF(day,0,@EndDate),0)

SELECT EmpID,
       SUM(DURATION) as Duration
FROM DC_WrkJobTime2
WHERE (DATEADD(day, DATEDIFF(day,0,TranDate),0) = @EndDate)
GROUP BY EmpID ORDER BY EmpID
GO

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Boris,

That query you posted will also work, but the way you wrote it, the query engine will have to perform a table scan. Notice the differences between my query and yours. The way I wrote my query, if there is an index on the TranDate column, it will get used.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Oh yeah, I forgot about guess #2. Maybe that should be #1 ;-)

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Thanks George,
I shouldn't post a comments when we have holiday here and have a little time to think. You are right of course.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

What holiday are you celebrating?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[elmorro]

Thank you all for your quick response. Gmmastros, your code worked.

elmorro