Solved Sum DateDiff

[amir0914]

Hi all, I have a table and it contains of stop dates of our equipment, the dates specify that which days the equipment was failure and not working. the below image is showing total days of equipment downtime is 45.

Now I need to know sum days of downtime based on two dates. for example :
I want to know stop days From 15/04/2020 to 01/06/2020, >>>>>>Sum of stop days = 24
Can someone tell me how can I do that by query or SQL?

 

[Isaac]

You might look into use the Totals button (in query design, near top), then Sum the [Stop Days] column. Drag From and To down to the lower grid again, and this time change the totals row to "where" and put in your criteria -

 

[theDBguy]

Hi. I am thinking you could probably try something similar to this approach.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

 

[Isaac]

Sorry, I made a very silly mistake - and just corrected my post. Flying too fast on the keyboard!
Hopefully this is a simple totals query, new screenshot above.

 

[theDBguy]

Sorry, I made a very silly mistake - and just corrected my post. Flying too fast on the keyboard!
Hopefully this is a simple totals query, new screenshot above.

@Isaac I'm not sure it's as easy as that. If you run that Totals query, I think the result will be 21, instead of the expected 24.

 

[Isaac]

@Isaac I'm not sure it's as easy as that. If you run that Totals query, I think the result will be 21, instead of the expected 24.

I stand corrected! Thanks for pointing that out. I was taking the requirement more as, Select records where the From and To were in between those dates, then Sum the totals. But if OP wants to count only days that are both in between From and To columns, as well as in between the parameter dates, you're right. Basically this means that OP's reporting requirement has, unfortunately, nothing to do with the Stop Days that they are storing....

@amir0914
Any time you have to jump through hoops to generate a report, it's a good time to stop and think about whether your table and data storage setup is meeting your reporting needs. For example ... In this scenario, it would have made things easier to simply store each single day of stoppage as a separate record.

I think dbGuy has given you the first clue as to your solution! And with the added benefit of being sql-based. (Unless you want to do some very complicated IIF and DateDiff statements and then sum those, but I think his way will be easier--I started doing it that way and it got ugly fast).

 

[plog]

First, Stop Days shouldn't be a field in a table. It's a purely calculated value and those don't go into tables, they get calculated in queries and used from there.

For your issue, I would make a function in a module and do the Stop Days calculation there. You would pass it 4 date values and it would do the rest:

Function get_StopDays(in_From, in_To, in_StartLimit, in_EndLimit)
  ' determines stop days of dt_From to dt_To as long as those dates fall within the limits

ret=0
' return value, default is 0

dt_Start = in_From
dt_End = in_To
 ' these are dates to calculate on, by default they are what was passed

if dt_Start<in_StartLimit then dt_Start=in_StartLimit
if dt_End>in_EndLimit then dt_End=in_EndLimit
' if dates are outside the limits, sets them to the limits 

if dt_Start<in_EndLimit AND dt_End>in_StartLimit then ret=DateDiff(dt_Start, dt_End)
' as long as the span falls within the limits does calculation

get_StopDays=ret 


End Function

The above code isn't 100% correct, you'll need to fix the DateDiff and I can't write that much code without a syntax error, so expect those. But that's how you do it. Then in a query you would have this:

SELECT SUM(get_StopDays([From],[To], "15/04/2020", "01/06/2020") AS StopDays
FROM Table1

 

[amir0914]

Thank you @Isaac , but that's not what I want, Stop Days is a calculation field on subform, actually I don't need it. I need to calculate sum of stop days between two dates. like this :

@Isaac. your right. it would easier if each record stored single day, but it's difficult to enter single day on each record because stop days may continue for 60 days sequentially.
@plog, I think you understand what I mean, but your code returns zero. (I use it in vba form)

pSQL = "SELECT SUM(get_StopDays(Dfrom,Dto, '15/04/2020', '01/06/2020')) AS StopDays FROM tbl_Date"

 

[plog]

Again, my code wasn't valid code. I would run a simple query first to test it:

SELECT Dfrom, Dto, get_StopDays(Dfrom, Dto, '15/04/2020', '01/06/2020') FROm tbl_Date

USe that to debug it and make sure it returns valid individual results before summing them.

 

[Isaac]

@Isaac. your right. it would easier if each record stored single day, but it's difficult to enter single day on each record because stop days may continue for 60 days sequentially

I'm not sure exactly what your current user interface is that creates those records (the ones you showed us) to begin with, but it would be trivial to add some code to take 2 dates from a form where someone is entering From and To, and create something like:

dim dtTo as date, dtStart as date, dtLoop as date, strDate as string
dtStart = cdate(Form_FormName.Controlname.value)
dtTo = cdate(Form_FormName.AnotherControlname.value)
dtLoop=dtStart

Do While dtLoop <= dtTo
    strDate=format(dtLoop,"mm/dd/yyyy")
    currentDb.Execute "insert into tablename (field1) values (#" & strDate & "#)",dbFailOnError
    dtLoop = dtLoop + 1
Loop

In a second or two you'd have your 60 days entered

 

[theDBguy]

Thank you @Isaac , but that's not what I want, Stop Days is a calculation field on subform, actually I don't need it. I need to calculate sum of stop days between two dates. like this :

View attachment 84927
@Isaac. your right. it would easier if each record stored single day, but it's difficult to enter single day on each record because stop days may continue for 60 days sequentially.
@plog, I think you understand what I mean, but your code returns zero. (I use it in vba form)

pSQL = "SELECT SUM(get_StopDays(Dfrom,Dto, '15/04/2020', '01/06/2020')) AS StopDays FROM tbl_Date"

Hi @amir0914 . Please don't forget to take a look at the link I posted earlier and ask any questions if you don't understand it.

 

[arnelgp]

see this sample.

 

[amir0914]

Hi @amir0914 . Please don't forget to take a look at the link I posted earlier and ask any questions if you don't understand it.

Hi theDBguy, Thank your very much, sorry I didn't see your link, I thought that's an ads. that was very helpful and I used it in my project.

 

[theDBguy]

Hi theDBguy, Thank your very much, sorry I didn't see your link, I thought that's an ads. that was very helpful and I used it in my project.

LOL. Re: ads. That's funny. I'll have to remember that for next time.

 

[plog]

I didn't see your link, I thought that's an ads

Ha ha, I'm with you. 25 years links have appeared a certain way, but some how modern user interface designers know best.