How do I determine how many days have past since a change?

[#1]

I have a relational model that is all based on ITMSNumber. What I'm trying to do is something like the following. (Please excuse the pseudo code).

There is a field called ChangeDate and another field called ChangeNewValue.

Left keep

Table6:

Load

ITMSNumber

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

SQL SELECT ITMSNumber,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

The date field (ChangeDate) is formatted like so- 2016-12-12 10:50:38.377

I want it to say that it's been 1, 5, 15, etc. days passed since the ITMSNumber has changed to 'Quarantined- DSC'.

I should say that I want a table that has a bunch of values in it for all of the ITMSNumber's that have changed to Quarantined- DSC. Like so-

ITMSNumber | DaysPassed

12374              2

12407              10

18472              8

[Aurelien_Martinez]

Left keep

Table6:

Load

ITMSNumber

IF(ChangeNewValue='Quarantined- DSC', Floor(Today() - Date#(ChangeDate, 'YYYY-MM-DD hh:mm:ss.fff'))) as DaysPassed

;

SQL SELECT ITMSNumber,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

[Anonymous · Accepted solution]

Hi,

Replace

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

with

IF(ChangeNewValue='Quarantined- DSC', date(Today()) -floor(date(TIMESTAMP# (ChangeDate,'YYYY-MM-DD hh:mm:ss.fff') as DaysPassed

Best regards,

Cosmina

[#4]

There's supposed to be 4 closing parenthesis there just to note.

It didn't work. It didn't provide any values at all for DaysPassed

[#5]

This worked and seemed like it would be the right answer, but when I cross checked it with the database this is what I got.

Today is 2017-01-18

The date of the change was- 2017-01-03 09:28:44.763

The difference is 15 days, however, the DaysPassed says that it's only been 9 days since the change.

The script works but it's not giving the right results...

[Aurelien_Martinez]

IF(ChangeNewValue='Quarantined- DSC', Floor(Today() - Date#(ChangeDate, 'YYYY-MM-DD hh:mm:ss.fff')))

[#7]

I changed it to

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;

And it's giving me more accurate numbers, but they're still not correct.

[Aurelien_Martinez]

When I do that, I have the good result :

date(Today()) - Floor(date(TIMESTAMP('2017-01-03 09:28:44.763', 'YYYY-MM-DD hh.mm.ss.fff')))

OR

Ceil(Today() - Date#('2017-01-03 09:28:44.763', 'YYYY-MM-DD hh:mm:ss.fff'))

[#9]

How are you getting 15? Mine is saying 10. It's been 15 days since 2017-01-03

[Aurelien_Martinez]