Help Writing a complicated query 1

[sugarflux]

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~

 

[sabinUE]

;with Users (UserID,UserName,GroupID)
AS
	(SELECT 1 ,'Joe',3 UNION ALL
	 SELECT 2 ,'Dave',5 UNION ALL
	 SELECT 3 ,'Geoff', 2 UNION ALL
	 SELECT 4 ,'Gordon', 1 UNION ALL
	 SELECT 5 ,'Jane', 4 )
,Groups (GroupID,GroupName)
AS
	(SELECT 1,'Group1' UNION ALL
	 SELECT 2,'Group2' UNION ALL
	 SELECT 3,'Group3' UNION ALL
	 SELECT 4,'Group4' UNION ALL
	 SELECT 5,'Group5' )
,History (HistoryID,ImportID,UserID,GroupID)
AS
	(SELECT 1, 1, 1, 1  UNION ALL
	 SELECT 2, 4, 2, 4  UNION ALL
	 SELECT 3, 5, 2, 3  UNION ALL
	 SELECT 4, 5, 3, 5  UNION ALL
	 SELECT 5, 5, 4, 2 )
,Imports (ImportID,ImportDate,ImportStatus)
AS
	(SELECT 1,'12/07/2014', 100 UNION ALL
	SELECT 2,'13/07/2014' ,2  UNION ALL
	SELECT 3,'13/07/2014' ,100  UNION ALL
	SELECT 4,'14/07/2014' ,100 UNION ALL
	SELECT 5,'15/07/2014' ,100 )


SELECT 
		ImportDate,UserID,UserName,GroupID AS "From-Group-ID",GroupName AS "From-Group-Name"
FROM
(
	SELECT
		I.ImportDate,U.UserID,U.UserName,H.GroupID,G.GroupName
		,RN = ROW_NUMBER() OVER(PARTITION BY U.UserID ORDER BY I.ImportDate DESC)
	FROM
		Imports AS I
		INNER JOIN History AS H
		ON I.ImportID = H.ImportID
		INNER JOIN Users AS U
		ON H.UserID = U.UserID		
		INNER JOIN	Groups AS G
		ON G.GroupID=H.GroupID 
)A
WHERE A.RN = 1 




ImportDate	UserID	UserName	From-Group-ID	From-Group-Name
12/07/2014	1	Joe	1	Group1
15/07/2014	2	Dave	3	Group3
15/07/2014	3	Geoff	5	Group5
15/07/2014	4	Gordon	2	Group2

sabin MCP

 

[sugarflux]

That's awesome Sabin - thanks!!
Now just a little bit of research to understand it!

I had to change it slightly for a couple of reasons - firstly I want to only return the joiners for 1 particular group (7058) and also I only want to see people who have joined that group within the last 7 days. Also I only wanted 7 days returned in the query (although it is handy I can return more very easily if I want to).

Here's something similar to what I have!

SELECT ih.ImportID,ih.ImportDate,ih.UserID,ih.UserName,ih.GroupID,ih.GroupName
FROM (
	SELECT i.ImportID,i.ImportDate,p.UserID,p.UserName,ph.GroupId,a.GroupName,
		RN=ROW_NUMBER() OVER(PARTITION BY p.UserID ORDER BY i.ImportDate DESC)
	FROM Imports i
	INNER JOIN User_History ph ON i.ImportID=ph.ImportID
	INNER JOIN Users p ON p.UserID=ph.UserID
	INNER JOIN Groups a ON a.GroupID=ph.GroupID
	WHERE p.GroupID=7058
	AND ph.GroupID!=7058
	) ih
INNER JOIN (
	SELECT TOP 7 i.ImportID,i.ImportDate
	FROM Imports i
	WHERE i.ImportStatus=100
	ORDER BY i.ImportDate DESC
	) AS i2 ON i2.ImportID=ih.ImportID
WHERE ih.RN=1

Once again - thanks for your help, life saver

~S~

 

[sabinUE]

Welcome!


sabin MCP