• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Calculate by current month and year

hi there i have this SP :
the problem is that i get the Calculation of the current last year
i need that the Calculation will be on the current year and month

thanks ....
ALTER PROCEDURE dbo.GetTotalCerditFromCurrentMonth
	
AS
	SET NOCOUNT ON 
SELECT sum(CrMoveTasSum) as CerditSum  FROM dbo.CreditMoveDet inner join dbo.CreditMoves on CrMoveMainId=CreRunId
                WHERE DATEPART(month,CreOpenDate) = DATEPART(month, GetDate())
	RETURN

Open in new window

0
Tech_Men
Asked:
Tech_Men
  • 3
  • 3
1 Solution
 
zadeveloperCommented:
try this
ALTER PROCEDURE dbo.GetTotalCerditFromCurrentMonth
	-- I added this optionaal parameter so that you could have more flexability.
	-- iT WILL NOT AFFECT YOUR CURRENT CALLS TO IF, AND WILL WORK AS IT USED TO IF ITS NOT PASSED IN :)
	@WorkingDate datetime = null  
AS 

--DECLARE @WorkingDate datetime
--SET @WorkingDate = GETDATE()

SET NOCOUNT ON  

SELECT 
	sum(CrMoveTasSum) as CerditSum  
FROM 
	dbo.CreditMoveDet 
	inner join dbo.CreditMoves on CrMoveMainId=CreRunId 
WHERE 
	datepart(month,CreOpenDate) = datepart(month, @WorkingDate) 
	and datepart(year,CreOpenDate) = datepart(year, @WorkingDate) 

Open in new window

0
 
Tech_MenAuthor Commented:
i there thanks for your help

why i need a datetime parameter u can use an sql func for it
0
 
zadeveloperCommented:
inclase you want to give a user the flexability of executing the sp based no a previous date. - not nessasary ... thought it may be usefull though :)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
zadeveloperCommented:
sorry left out the is null check :)
ALTER PROCEDURE dbo.GetTotalCerditFromCurrentMonth
	-- I added this optionaal parameter so that you could have more flexability.
	-- iT WILL NOT AFFECT YOUR CURRENT CALLS TO IF, AND WILL WORK AS IT USED TO IF ITS NOT PASSED IN :)
	@WorkingDate datetime = null  
AS 

--DECLARE @WorkingDate datetime
--SET @WorkingDate = GETDATE()

SET NOCOUNT ON  

if (@WorkingDate is null)
	set @WorkingDate = GETDATE()
	
SELECT 
	sum(CrMoveTasSum) as CerditSum  
FROM 
	dbo.CreditMoveDet 
	inner join dbo.CreditMoves on CrMoveMainId=CreRunId 
WHERE 
	datepart(month,CreOpenDate) = datepart(month, @WorkingDate) 
	and datepart(year,CreOpenDate) = datepart(year, @WorkingDate) 

Open in new window

0
 
Tech_MenAuthor Commented:
sorry i dont want this solution i need a solution whitout parameters
0
 
Tech_MenAuthor Commented:
i get it
thanks ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now