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

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,
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window



Then you could apply that in your CASE statement - if that isn't what you're looking for please advise.
0
TBSupportAuthor Commented:
I got a negative figure for the total field and my data is duplicating.
0
coreconceptsCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

TBSupportAuthor Commented:
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
TBSupportAuthor Commented:
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
0
coreconceptsCommented:
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'

Open in new window


Please advise
0
TBSupportAuthor Commented:
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
coreconceptsCommented:
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, 
--(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,
(CASE 
WHEN RCG.dbo.POContractHeader.StartDate > GETDATE()
THEN DATEDIFF(M, RCG.dbo.POContractHeader.StartDate, RCG.dbo.POContractHeader.EndDate)
WHEN RCG.dbo.POContractHeader.EndDate <= GETDATE()
THEN 0
ELSE DATEDIFF(M, GETDATE(), RCG.dbo.POContractHeader.EndDate)
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
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
That did it.  Thank you!

TBSupport
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.