compare date count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I have been asked to do is this.

How many trouble tickets does a customer have within a 30 day period.

Here's an example -

Account # TT Date
123 1 1/8/2007
123 2 1/15/2007
123 3 2/2/2007
123 4 2/7/2007


The count for Account # 123 would be 4.

I hope that makes sense. Any help would be greatly appreciated!
Thanks,
Keith
 
Are you saying for any 30 day period, or do you know the start and end dates
for the 30 day period?

If you know the end dates, it's

SELECT [Account#], Count(*)
FROM MyTable
WHERE [Date] BETWEEN StartDate AND EndDate
GROUP BY [Account#]

BTW, Date isn't a good name to use: it's a reserved word, and you can get
into all sorts of trouble using reserved words as field names. You should
also avoid the use of special characters (and spaces) in names.
 
Thanks for responding and for the wisdom about the date field.

You asked a great question but no there is no start or end dates. Which now
I think it's impossible to do cause if you had the following
1/1/2006
1/10/2006
2/2/2006
2/3/6006

the count would be 2 (1/1 through 1/10 ) but it could also be 3 (1/10
through 2/3).....argh...CRAP.

THIS SUCKS! So, I would need to ask the boss to clarify this cause this
would be impossible to get back results.

Douglas J. Steele said:
Are you saying for any 30 day period, or do you know the start and end dates
for the 30 day period?

If you know the end dates, it's

SELECT [Account#], Count(*)
FROM MyTable
WHERE [Date] BETWEEN StartDate AND EndDate
GROUP BY [Account#]

BTW, Date isn't a good name to use: it's a reserved word, and you can get
into all sorts of trouble using reserved words as field names. You should
also avoid the use of special characters (and spaces) in names.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Keith said:
What I have been asked to do is this.

How many trouble tickets does a customer have within a 30 day period.

Here's an example -

Account # TT Date
123 1 1/8/2007
123 2 1/15/2007
123 3 2/2/2007
123 4 2/7/2007


The count for Account # 123 would be 4.

I hope that makes sense. Any help would be greatly appreciated!
Thanks,
Keith
Click to expand...
Click to expand...
 
Back
Top