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

# 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
``````
0
Tech_Men
• 3
• 3
1 Solution

Commented:
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)
``````
0

Author Commented:
i there thanks for your help

why i need a datetime parameter u can use an sql func for it
0

Commented:
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

Commented:
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)
``````
0

Author Commented:
sorry i dont want this solution i need a solution whitout parameters
0

Author 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.