Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Writing a complicated query 1

Status
Not open for further replies.

sugarflux

Technical User
Aug 14, 2003
111
GB
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~
 
SQL:
;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
 
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!

SQL:
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~
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top