SSRS: Turning a Date Value into an Integer for a Mathematical Calculation
Posted on 2013-10-11
At the end of this posting is a snippet of code from my larger query. There are two fields, here: "Total" and "Month".
I want to leave Month the way that it is, but I need to change the Total field. Specifically, I need to multiply what I show now as Total, by an integer. This integer is based on a timeframe.
Specifically, the RCG.dbo.ContractHeader table has StartDate and EndDate fields. I need to subtract StartDate from EndDate and, then, subtract GetDate(). This should give me an integer representing number of months.
It's this number of months that I will multiply times the Total field's product of AvailQuantity and UNITCOST (from the code below). This will ultimately give me the value of the amount of time remaining in the contract!
Since StartDate and EndDate can represent months that cut across years, how do I derive a number of months and do what I just said I need: (EndDate-StartDate)-GetDate()?
(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,