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

Count Number of Days Per 100 Records

I am looking to count the number of days in between every 100 registered users.

The columns I have are

member_id
join_date

The date is using the Unix Time Stamp format (example: 1303127150)

So every time it hits 100 users, i would like to see the number of days between that and the previous 100.  Not sure if this is possible.
0
theideabulb
Asked:
theideabulb
  • 10
  • 4
1 Solution
 
sfmnyCommented:
Hi there,

I'm not sure I fully understand the question. I'm assuming you want to the date at every 100 member id, irrespective of everything else.

Use LIMIT for that feature. You can use LIMIT to scroll through the result set - so

SELECT ...
....
LIMIT 99,1 gives the 100th, 199,1 gives 200th and so on
0
 
Oliver WastellCommented:
Hi, everything is possible, it's just a matter of how to go about it!

The following SQL will return the results like that in the screenshot. I've called it qry100thUser:

SELECT tblMember.member_ID, tblMember.joinDate, IIf([member_ID]=1,"Y",IIf(([member_ID]/100)-CInt([member_ID]/100)=0,"Y","N")) AS 100th, IIf([member_ID]=1,0,[member_ID]/100) AS nth
FROM tblMember
WHERE (((IIf([member_ID]=1,"Y",IIf(([member_ID]/100)-CInt([member_ID]/100)=0,"Y","N")))="y"));

Screenshot of qry100thUser results
You can then create a query based on qry100thUser to look up the nth-1 date to sit alongside the nth date.  It should then be relatively straightforward to create a function to compare the number of days between each 100 users.
0
 
theideabulbAuthor Commented:
@odwastell, i would be happy with what you are showing me, then work on the function to figure out the date difference.  I am trying your code and getting this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[member_ID]=1,"Y",IIf(([member_ID]/100)-CInt([member_ID]/100)=0,"Y","N")) AS 100' at line 1

i am using version 5.5
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Oliver WastellCommented:
Sorry I've knocked up the SQL in MS Access, it is probably the CInt that is causing the problem (CInt: Convert to Integer).  If you find the appropriate function for this in mySQL and substitute then it should work.
0
 
Oliver WastellCommented:
I've had a quick look at the manual, I think the function that you need is :
CONVERT (([member_ID]/100),UNSIGNED INTEGER)
instead of
CInt([member_ID]/100)

I'm just working on the rest of the queries you'll require...
0
 
Oliver WastellCommented:
In addition to qry100thUser, you'll need:

qry100thUserNthMinus1:
SELECT qry100thUser.member_ID, qry100thUser.joinDate AS nthJoinDate, qry100thUser.nth, IIf([nth]=0,-1,[nth]-1) AS nthMinus1
FROM qry100thUser;
Screenshot of qry100thUserNthMinus1 resultsand

qry100thUserDates:

SELECT qry100thUserNthMinus1.member_ID, qry100thUserNthMinus1.nth, qry100thUserNthMinus1.nthJoinDate, qry100thUser.joinDate AS nthMinus1JoinDate
FROM qry100thUserNthMinus1 LEFT JOIN qry100thUser ON qry100thUserNthMinus1.nthMinus1 = qry100thUser.nth;
Screenshot of qry100thUserDates resultsNote that I have changed the join dates for the 1st,100th, 200th, 300th members (they were all the same in my first post).

You can add the function to calculate the difference in days between the two dates in this last query.  I hope that helps!
0
 
theideabulbAuthor Commented:
i am getting a little lost here,  what is the query as a whole, not sure where this second query comes into play
0
 
Oliver WastellCommented:
There are three queries.

The first query pulls out the 1st, 100th, 200th, 300th, etc member and the date that they joined.

The third query pulls together the member joining dates for the 100th and 1st member, the 200th and 100th member, the 300th and 200th member, etc (see screen shots).  The second query is simply an intermediate step to pull these together (it generates nthMinus1 to join the nth record, which could be generated in the first query but it would involve calculating the nth minus 1 value for every member - which isn't ideal for performance reasons but to be honest for the number of users you're probably expecting it wouldn't make too much difference).

Does that help clarify?
0
 
Oliver WastellCommented:
Further to my last post, the following screenshot taken from the MS Access query designer might help you to understand  the relationship between the three queries used.
Query Design for qry100thUserDates
0
 
Oliver WastellCommented:
Another way of doing it...
I have assumed that you want to achieve your aim using SQL, i.e. without actual coding, but  another way of doing it would be to have some code that you run periodically, that would write the memberID and joining date of every 100th user to a separate table along with the day count since the previous 100th user.  This code would check the memberID of the last member written to it (i.e. with the highest memberID) and then search your member table  for your next 100th (and subsequent) user from this last member.  Would this approach be better for you?
0
 
theideabulbAuthor Commented:
I am still trying to get this first part to run.  Here is the actual query.  Let me know if i am missing something here.  Still getting an error.


SELECT exp_members.member_ID, exp_members.joinDate, IIf([member_ID]=1,"Y",IIf(([member_ID]/100)-CONVERT(([member_ID]/100),UNSIGNED INTEGER)=0,"Y","N")) AS 100th, IIf([member_ID]=1,0,[member_ID]/100) AS nth
FROM exp_members
WHERE (((IIf([member_ID]=1,"Y",IIf(([member_ID]/100)-CONVERT(([member_ID]/100),UNSIGNED INTEGER)=0,"Y","N")))="y"))



 Warnings: --->
   W (1): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[member_ID]=1,"Y",IIf(([member_ID]/100)-CONVERT(([member_ID]/100),UNSIGNED INTEG' at line 1
0
 
Oliver WastellCommented:
OK, I've started making progress.  I'm doing this now in mySQL.  IIf isn't supported in mysql, but the following works for the first query:

qry100thUser
SELECT tblMember.member_ID, tblMember.join_date,
case cast(member_ID as UNSIGNED INTEGER)
WHEN 1
THEN "Y"
ELSE
  CASE (member_ID/100)-CONVERT((member_ID/100),UNSIGNED INTEGER)
  WHEN 0
  THEN "Y"
  ELSE "N"
  END
END as 100th,
case cast(member_ID as UNSIGNED INTEGER)
WHEN 1
THEN 0
ELSE member_ID/100
END AS nth
FROM tblMember
WHERE
case cast(member_ID as UNSIGNED INTEGER)
WHEN 1
THEN "Y"
ELSE
  CASE (member_ID/100)-CONVERT((member_ID/100),UNSIGNED INTEGER)
  WHEN 0
  THEN "Y"
  ELSE "N"
  END
END = "Y"
0
 
Oliver WastellCommented:
OK, it looks like we cannot store queries in mySQL, so we can't have a query running on a query without resorting to an external program (such a MySQL Workbench) to query the MySQL server.  In order to determine the best way forward for you, how are you planning on using the number of days between each 100th user?
0
 
theideabulbAuthor Commented:
Honestly you have done more than enough with this query.  I will do the rest outside the query to get the datediff part.  I am not very good with mysql, don't use it very often but this was a huge help.  Thank you for all your efforts!!
0
 
Oliver WastellCommented:
No problem at all.  Thanks for the points/grade and good luck with getting the rest done!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now