Solved

# Count Number of Days Per 100 Records

Posted on 2012-09-11
465 Views
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
Question by:theideabulb

LVL 5

Expert Comment

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

LVL 3

Expert Comment

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"));

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

Author Comment

@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

LVL 3

Expert Comment

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

LVL 3

Expert Comment

I've had a quick look at the manual, I think the function that you need is :
CONVERT (([member_ID]/100),UNSIGNED INTEGER)
CInt([member_ID]/100)

I'm just working on the rest of the queries you'll require...
0

LVL 3

Expert Comment

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;
and

qry100thUserDates:

SELECT qry100thUserNthMinus1.member_ID, qry100thUserNthMinus1.nth, qry100thUserNthMinus1.nthJoinDate, qry100thUser.joinDate AS nthMinus1JoinDate
FROM qry100thUserNthMinus1 LEFT JOIN qry100thUser ON qry100thUserNthMinus1.nthMinus1 = qry100thUser.nth;
Note 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

Author Comment

i am getting a little lost here,  what is the query as a whole, not sure where this second query comes into play
0

LVL 3

Expert Comment

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

LVL 3

Expert Comment

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.
0

LVL 3

Expert Comment

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

Author Comment

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

LVL 3

Accepted Solution

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

LVL 3

Expert Comment

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

Author Closing Comment

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

LVL 3

Expert Comment

No problem at all.  Thanks for the points/grade and good luck with getting the rest done!
0