SSRS: Turning a Date Value into an Integer for a Mathematical Calculation

Hi TB,

       It sounds like what would work is getting the difference between start and end date using DATEDIFF like:

DATEDIFF(M, <YourStartDate>, <YourEndDate>)    -- if you reverse the order your number will be negative..

Then.. do the difference between your End Date and GETDATE() like:

DATEDIFF(M, <YourEndDate>, GETDATE())

Then Add the two together:

SELECT
     DATEDIFF(M, <YourStartDate>, <YourEndDate>) As Start_Diff
    ,   DATEDIFF(M, <YourEndDate>, GETDATE()) As End_Diff
    ,  (   DATEDIFF(M, <YourStartDate>, <YourEndDate>)    )   +   (DATEDIFF(M, <YourEndDate>, GETDATE())   )   As Total_Diff

FROM <YourTable>

Select allOpen in new window

Then you could apply that in your CASE statement - if that isn't what you're looking for please advise.

I got a negative figure for the total field and my data is duplicating.

Hi TB,

       Did you put the ENDATE before the STARTDATE in your DATEDIFF statement?  Assuming that your startdate preceeds your enddate, and your enddate is not in the future (as in an estimated enddate for a contract), then should only be able to produce a positive number if you put the earlier date ahead of the later date in the DATEDIFF statement.  Can you copy what you ended up using?

The end date is, indeed, in the future.  Again, that's why I said that the Start Date and End Date fields can cross into years and, specifically, future years.

This is what I'm using.  At your leisure, please help me get rid of the negatives.

For now, I have put this aside.

SELECT  RCG.dbo.POContractHeader.VENDORID as VendorID,
--(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
(DATEDIFF(M, RCG.dbo.POContractHeader.EndDate, RCG.dbo.POContractHeader.StartDate))+(DATEDIFF(M, GETDATE(), RCG.dbo.POContractHeader.EndDate))
*(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
'Month ' +
CASE WHEN DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 < 10
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(), RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(2))
END
AS Month, 0 as PastDue
FROM         RCG.dbo.POContractHeader INNER JOIN
                      RCG.dbo.POContractDetail ON RCG.dbo.POContractHeader.ContractNum =
RCG.dbo.POContractDetail.ContractNum LEFT OUTER JOIN
                      RCG.dbo.PODraws ON RCG.dbo.POContractHeader.ContractNum =
RCG.dbo.PODraws.ContractNum
WHERE     (RCG.dbo.POContractHeader.EndDate >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND
(RCG.dbo.POContractHeader.EndDate < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 18, 0))
--and RCG.dbo.POContractHeader.VENDORID IN (@Vendor)
and RCG.dbo.POContractHeader.StartDate IS NOT NULL and RCG.dbo.POContractHeader.EndDate IS NOT NULL
GROUP BY RCG.dbo.POContractHeader.VENDORID, RCG.dbo.PODraws.DateReq, RCG.dbo.POContractDetail.AvailQuantity, RCG.dbo.POContractDetail.UNITCOST, RCG.dbo.POContractHeader.StartDate,
RCG.dbo.POContractHeader.EndDate
HAVING (RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) > 0

Hi TB,

      If I understand correctly, and your objective is to determine how many months remain in the contract.  If that's the case then if startdate is in the past and enddate is in the future, we don't need to use startdate at all, we just want the difference between GETDATE() and the endate of the contract.  In cases where contracts END in the past you probably want to see zero (0) -- assuming that's even possible in your data set.  Lastly, if the startdate is also in the future, then naturally so is the enddate, and I assume you want to see an integer representing the total length of the contract (as opposed to the difference between the end of the contract and the current date..)

Assuming I'm understanding I would use logic like the following:

 CASE 
	WHEN [yourstartdate] > GETDATE() 
	THEN DATEDIFF(M, [yourstartdate], [yourenddate])
	WHEN [yourenddate]<= GETDATE()
	THEN 0
	ELSE DATEDIFF(M, GETDATE(), [yourenddate])
END As 'Months_Remaining'

Select allOpen in new window

Please advise

Hi CoreConcepts:

You're correct, per the first statement that you made:   "my objective is to determine how many months remain in the contract.  If that's the case then if startdate is in the past and enddate is in the future, we don't need to use startdate at all, we just want the difference between GETDATE() and the endate of the contract."

That being the case, does that change the code that you suggested, in your recent posting?

TBSupport

That did it.  Thank you!

TBSupport