• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Counting dates

I have a database where I dump dates.  I need to get a count of how many dates that was dump that fall between date’s ranges.  Please note! I will need to be able to enter the date range
If I enter start date at 8/15/2012 through  8/31/2012 and the dates I dumped is through 9/5/12, I need the count of days that are less than 0-3 days, 4-7 days, 8-14 days, 15-28 days, 28+Days, that only falls within the date range 8/15 – 8/31/2012.
0
Delores_C
Asked:
Delores_C
  • 6
  • 4
  • 2
  • +1
1 Solution
 
peter57rCommented:
"less than 0-3 days, 4-7 days, 8-14 days, 15-28 days"

which two dates are being used to get the difference?
0
 
Dale FyeCommented:
Without knowing a little more about you data structure and exactly what you are trying to do, this is just a stab in the dark.  I usually do this by creating a Range table (tbl_Ranges)

GTE     LT     Desc
0          4      Less than 4 days
4          8      4-7 days
8         15     8-14 days
15       29     15-28 days
29    NULL   Greater than 28 days

Then I create my query with a syntax that looks something like:

SELECT tbl_Ranges.Desc
 , SUM(IIF(DateDiff("d", yourTable.DateField, Date()) >= tbl_Ranges.GTE AND
                  DateDiff("d", yourTable.DateField, Date()) < NZ(tblRanges.LT, 1000), 1, 0)) as Records
FROM tbl_Ranges, yourTable
GROUP BY tbl_Ranges.Desc
ORDER BY tbl_Ranges.GTE
0
 
Delores_CAuthor Commented:
Clarification:  This is the existing fields

1.  Location = SCR
2.  DumpDates = 8/12

There are multiple locations, however, there are multiple dates for a location, in many cases, you may have the same date multiple times..   I need to take a location and determine based on a DumpDates range, how many of each of criteria there are for a location.

I need a prompt ask the date, then use the date I enter to determine the count of each criteria
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Dale FyeCommented:
Can you provide some sample data?  

1.  Create a new database
2.  Import this table into the new database
3.  Delete any proprietary or personal data from the table.
4.  Delete all but about 30-40 records from the table
5.  Compact and Repair
6.  For those remaining records, figure out what the results you expect to see should look like.
7.  It would be helpful if you could put these records in a spreadsheet and annotate in one of the columns which category the record should fall into and why.
8.  Attach the sample data and spreadsheet files to the EE post.
0
 
Delores_CAuthor Commented:
I got it to ask for date, however, I need it to sum the DataCount fields that meet the criteria of 0-3, 7-14, etc. File attached
Cynthia.accdb
0
 
IrogSintaCommented:
Something like this?
Cynthia.accdb
0
 
Delores_CAuthor Commented:
I needed it to ask for a date to count from, It prompts for a date, however, when I enter a date (lets say 9/10/12), I am getting a negative number count, and it is not counting in the group.  I need all numbers to be counting in the group at some level.  I am attaching what I have done up to this point.
Cynthia.accdb
0
 
IrogSintaCommented:
I don't see any negative numbers when I use 9/10/12.  Now if I enter 8/10/12, there are negative numbers but where did you want the negative numbers to be grouped with?  In SCR, for example, 8/1 to 8/10 gives you 9 days, which goes in the 8-14 day group.  On the other and, 8/15 to 8/10 gives you -5 days.  Did you want to ignore the negative number and stick it with the 4 to 7 day group?
0
 
Delores_CAuthor Commented:
Yes, I need to count the number regardless if negative.
0
 
Delores_CAuthor Commented:
I meant even if the number is a negatve 31 (-31) it would be count in the 28+ Days.  All of the numbers need to fall in a group, and the Count needs to be added in a group.  Thanks in advance.
0
 
IrogSintaCommented:
But you still want it to display as negative, right?  It just needs to be added to the groups as if they were positive numbers...
0
 
IrogSintaCommented:
See if this works for you.
Cynthia--1-.accdb
0
 
Delores_CAuthor Commented:
Just what I needed.  Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now