Solved

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

Posted on 2013-10-11
Medium Priority
794 Views
Hello:

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()?

Thanks!

TBSupport

SELECT
(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
Question by:TBSupport
• 5
• 4

LVL 3

Expert Comment

ID: 39565982
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())

``````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>
``````

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

LVL 1

Author Comment

ID: 39566124
I got a negative figure for the total field and my data is duplicating.
0

LVL 3

Expert Comment

ID: 39566145
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?
0

LVL 1

Author Comment

ID: 39566164
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.
0

LVL 1

Author Comment

ID: 39566173

For now, I have put this aside.

--(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
*(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
RCG.dbo.POContractDetail.ContractNum LEFT OUTER JOIN
RCG.dbo.PODraws.ContractNum
HAVING (RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) > 0
0

LVL 3

Expert Comment

ID: 39566239
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'
``````

0

LVL 1

Author Comment

ID: 39566941
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
0

LVL 3

Accepted Solution

coreconcepts earned 2000 total points
ID: 39567102
Hi TB,

Looks like we're on the same page - no that won't change the logic (assuming we considered the scenarios possible in your dataset).

I tossed in that logic in your code block and commented out what you had for the Total column and replaced it with your specific fields.  It looked good to me, but as I can't run it I'll have to rely on you to tell me.

Code follows:

``````SELECT  RCG.dbo.POContractHeader.VENDORID as VendorID,
--(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
--*(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
(CASE
THEN 0
END)*(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
RCG.dbo.POContractDetail.ContractNum LEFT OUTER JOIN
RCG.dbo.PODraws.ContractNum
HAVING (RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) > 0
``````
0

LVL 1

Author Comment

ID: 39569978
That did it.  Thank you!

TBSupport
0

## Featured Post

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.