January 12, 2018 at 7:33 am
I am struggling to create a query that will return a report containing EO_AppUsers, EO_Policies and only the latest EO_Applications
The report should contain a EO_AppUsers once.
CREATE TABLE [dbo].[EO_AppUsers] (
[Id] NVARCHAR (128) NOT NULL
CREATE TABLE [dbo].[EO_Policies] (
[Id] NVARCHAR (128) NOT NULL,
[AppUser_Id] NVARCHAR (128) NULL,
CREATE TABLE [dbo].[EO_Applications] (
[Id] NVARCHAR (128) NOT NULL,
[Created] DATETIME2 (7) NOT NULL,
[AppUser_Id] NVARCHAR (128) NULL,
An EO_AppUsers has a EO_Policies and EO_AppUsers can have 0 or more EO_Applications.
The following returns what I need except it returns all EO_Applications associated to a c.
I want only the latest EO_Applications for each EO_Applications
select u.Id as userId, u.CustomerId, a.Id as appId, a.Created as appCreated from EO_AppUsers u
left join EO_Policies p on (u.Id = p.AppUser_Id)
left join EO_Applications a on (u.Id = a.AppUser_Id)
The following returns only the latest EO_Applications for each EO_AppUsers
select distinct a.id, a.created, a.AppUser_Id, a.OrderId from EO_Applications a order by Created desc
If I could create a set that contains only the lastest EO_Applications and
use this set instead of joining on EO_Applications, I think that would give me the report I want.
I tried the following but it results in:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I need the ordering to get only the latest EO_Applications
select u.Id as userId, u.CustomerId, a.Id as appId, a.Created as appCreated from EO_AppUsers u
left join EO_Policies p on (u.Id = p.AppUser_Id)
left join (select distinct id, created, AppUser_Id from EO_Applications order by Created desc ) d on (u.Id = a.AppUser_Id)
Hopefully this makes sense. Any hints or suggestions would be appreciated.
January 12, 2018 at 7:49 am
Perhaps something vaguely resembling this. I can't test it because there's no sample data and the columns in your query don't match those in your table DDL.
select u.Id as userId, u.CustomerId, a.Id as appId, a.Created as appCreated from EO_AppUsers u
left join EO_Policies p on (u.Id = p.AppUser_Id)
CROSS APPLY (
select TOP 1 id, created, AppUser_Id
from EO_Applications d
WHERE d.AppUser_id = u.id
order by Created desc ) x
John
January 12, 2018 at 8:46 am
Thanks for the quick reply.
Your solution almost does the job.
It only returns a record if the EO_AppUsers has a EO_Applications.
I need all EO_AppUsers returned regardless if they have an EO_Applications
Test data bellow.
Using :
select u.Id as userId, u.CustomerId, x.Id as appId, x.Created as appCreated from EO_AppUsers u
left join EO_Policies p on (u.Id = p.AppUser_Id)
CROSS APPLY (
select TOP 1 id, created, AppUser_Id
from EO_Applications d
WHERE d.AppUser_id = u.id
order by Created desc ) x
With your solution 1 only the first row is returned.
userid customerid appId appCreated
1224cebf-1c61-450f-a404-77af211e9eba EOC6660 1224cebf-1c61-450f-a404-77af211e9eba 2017-01-01 00:00:00.0000000
The result should look like:
userid customerid appId appCreated
1224cebf-1c61-450f-a404-77af211e9eba EOC6660 1224cebf-1c61-450f-a404-77af211e9eba 2017-01-01 00:00:00.0000000
2c22b268-265c-41f8-b4dc-a108fbe090b7 EOC6659 NULL NULL
69b928cb-be47-421a-a1a0-c10246078dc4 EOC6662 NULL NULL
7f03cefb-99d2-4dec-a595-ac6bf41f012f EOC6665 NULL NULL
8dde0192-3e4b-43b7-b658-573c6cd871d4 EOC6664 NULL NULL
906252ea-1025-4dc5-b574-d6ec5d92fc4d EOC6657 NULL NULL
91562e7b-7854-46ae-acc2-f92b30509c49 EOC6663 NULL NULL
e4988c11-91d5-4bfc-9978-e3c41267320c EOC6661 NULL NULL
f6e889b1-68d2-48c7-8805-6cb2234c337e EOC6658 NULL NULL
January 12, 2018 at 8:55 am
Then use OUTER APPLY instead of CROSS APPLY.
January 12, 2018 at 9:01 am
Excellent, it works.
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply