Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I need help with this Date stored procedure. 1

Status
Not open for further replies.

elmorro

Programmer
Jun 23, 2005
133
US
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 :)
 
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
 
Try this...

Code:
[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. [smile]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
GETDATE() returns current date AND time, if you want to get all records from previous day you could do:
Code:
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
 
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
 
Oh yeah, I forgot about guess #2. Maybe that should be #1 ;-)

Ignorance of certain subjects is a great part of wisdom
 
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
 
What holiday are you celebrating?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Thank you all for your quick response. Gmmastros, your code worked.

elmorro :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top