I've been wrestling with this for quite some time now and decided it was time to ask for some help!!
I'm basically trying to get a list of Users who have joined a specified group within the last 7 days (Imports). Whilst this sounds easy, my data is not laid out easily for this purpose....
Here's how it looks:
Users (One record per user)
UserID,UserName,GroupID
eg.
[pre]UserID | Username | GroupID
1 | Joe | 3
2 | Dave | 5
3 | Geoff | 2
4 | Gordon | 1
5 | Jane | 4[/pre]
Groups (One record per group)
GroupID,GroupName
eg.
[pre]GroupID | GroupName
1 | Group1
2 | Group2
3 | Group3
4 | Group4
5 | Group5[/pre]
History (Mulitple records: new record for each user and import when data has changed)
HistoryID,ImportID,UserID,GroupID
eg.
[pre]HistoryID | ImportID | UserID | GroupID
1 | 1 | 1 | 1
2 | 4 | 2 | 4
3 | 5 | 2 | 3
4 | 5 | 3 | 5
5 | 5 | 4 | 2[/pre]
Imports (One record per import)
ImportID
ImportDate
ImportStatus
eg.
[pre]ImportID | ImportDate | ImportStatus
1 | 12/07/2014 | 100
2 | 13/07/2014 | 2
3 | 13/07/2014 | 100
4 | 14/07/2014 | 100
5 | 15/07/2014 | 100[/pre]
Ideally what I want to end up with is:
Users who have joined Group5[5] in the last 7 days:
[pre]ImportDate | UserID | Username | From-Group-ID | From-Group-Name
15/07/2014 | 2 | Dave | 3 | Group3[/pre]
You'll see the problem here is that Dave[2] has moved group twice within that period, first from Group4[4] to Group3[3], then from Group3[3] to Group5[5]. I only want to show the records when users have moved to Group5[5], meaning I need the latest record from the history table.
So - hopefully this is clear enough but please let me know if I've just been confusing!!
Any help greatly appreciated,
~S~
I'm basically trying to get a list of Users who have joined a specified group within the last 7 days (Imports). Whilst this sounds easy, my data is not laid out easily for this purpose....
Here's how it looks:
Users (One record per user)
UserID,UserName,GroupID
eg.
[pre]UserID | Username | GroupID
1 | Joe | 3
2 | Dave | 5
3 | Geoff | 2
4 | Gordon | 1
5 | Jane | 4[/pre]
Groups (One record per group)
GroupID,GroupName
eg.
[pre]GroupID | GroupName
1 | Group1
2 | Group2
3 | Group3
4 | Group4
5 | Group5[/pre]
History (Mulitple records: new record for each user and import when data has changed)
HistoryID,ImportID,UserID,GroupID
eg.
[pre]HistoryID | ImportID | UserID | GroupID
1 | 1 | 1 | 1
2 | 4 | 2 | 4
3 | 5 | 2 | 3
4 | 5 | 3 | 5
5 | 5 | 4 | 2[/pre]
Imports (One record per import)
ImportID
ImportDate
ImportStatus
eg.
[pre]ImportID | ImportDate | ImportStatus
1 | 12/07/2014 | 100
2 | 13/07/2014 | 2
3 | 13/07/2014 | 100
4 | 14/07/2014 | 100
5 | 15/07/2014 | 100[/pre]
Ideally what I want to end up with is:
Users who have joined Group5[5] in the last 7 days:
[pre]ImportDate | UserID | Username | From-Group-ID | From-Group-Name
15/07/2014 | 2 | Dave | 3 | Group3[/pre]
You'll see the problem here is that Dave[2] has moved group twice within that period, first from Group4[4] to Group3[3], then from Group3[3] to Group5[5]. I only want to show the records when users have moved to Group5[5], meaning I need the latest record from the history table.
So - hopefully this is clear enough but please let me know if I've just been confusing!!
Any help greatly appreciated,
~S~