1

Database

I'm trying to construct a difficult SQL Server query to select specific data. First, let me give you some background information...

I'm using a database in which I need to reach 3 tables using this query. You can see the diagram in the picture. As you can see, there are users in this database. These users can buy a subscription to use some public bikes around the city. A subscription has a subscriptiontype. This subscriptiontype declares how long the subscription is valid. This can be for a day, a month or a year. A user can have multiple subscriptions.

With my query, I need to select the userId, the name, the address that consists of a street, number, zipcode, city and countrycode, and the description of the subscriptiontype. For every user, I only need the subscription with the longest duration. so you have to filter the query on subscriptionTypeId DESC. but not only this... a user can have multiple subscriptions from the same type, so I only need the most current one. This means I also have to filter on ValidFrom DESC, on the longest during subscriptiontype.

I know I need to use a subquery for this, but can't seem to find a decent query that works... This is how far I got, but I've been using multiple techniques to try to get what I need:

SELECT 
    su.userId, u.name, u.street, u.number, u.zipcode, u.CountryCode, st.description, 
    MAX(su.validFrom) AS MaxValidFrom, 
    (SELECT MAX(SubscriptionTypeId) FROM dbo.Subscriptions 
     WHERE UserId = su.UserId) AS MaxSubscriptionTypeId
FROM 
    dbo.Users u 
INNER JOIN 
    dbo.Subscriptions su ON u.userId = su.userId
INNER JOIN 
    dbo.SubscriptionTypes st ON su.subscriptionTypeId = st.subscriptionTypeId
HAVING 
    su.SubscriptionTypeId = MAX(su.SubscriptionTypeId)
ORDER BY 
    su.UserId, MaxValidFrom DESC;

Thanks in advance for helping me!

Joren

  • 2
    a little sample data will get this answered much quicker. – JM_ Nov 6 at 16:01
  • Is your request to select the highest SubscriptionTypeId for each user, along with the most recent "ValidFrom" date of that subscription? or just to select the most recent subscription based on the ValidFrom? – Richard Nov 6 at 16:22
  • 1
    Break your problem into pieces that you can focus on and solve. Forget all the joined and focus on "the most recent subscription of longest duration". Notice that subscription has ValidFrom but not ValidTo. That seems to be big problem. It also seems to me that SubscriptionType should contain the "length" information. Can someone stop a subscription early? – SMor Nov 6 at 16:27
0

SQL can get a bit tied up with inline sub-queries depending on power and resources indexes etc.

I would avoid custom functions because the query plan is almost always negatively affected by it.

Break it up this will get you the Max(SubscriptionTypeId) a user has ever had, along with the latest instance of the user being subscribed to that TypeId.

SELECT 
    u.userId, u.name, u.street, u.number, u.zipcode, u.CountryCode
    --,st.description 
    --,MAX(su.validFrom) AS MaxValidFrom, 
    --,(SELECT MAX(SubscriptionTypeId) FROM dbo.Subscriptions WHERE UserId = su.UserId) AS MaxSubscriptionTypeId
into #temp
FROM 
    dbo.Users u 
WHERE EXISTS (SELECT top 1 1 from dbo.Subscriptions i_su where i_su.UserId = u.UserId)
--Now you have a list of all who have at least 1 subscription inside a temp table.

Select u.*
    ,st.description
    ,su.ValidFrom
    ,su.SubscriptionTypeId
    --This function will return a "Rank" for each user (partition) in order of the "Order By" clause.
    ,ROW_NUMBER() OVER (partition by u.UserId order by SubscriptionTypeId DESC, ValidFrom DESC) as RowNo
into #temp2
FROM #temp u
INNER JOIN dbo.Subscriptions su
ON u.userId = su.userId
INNER JOIN dbo.SubscriptionTypes st
ON su.subscriptionTypeId = st.subscriptionTypeId

SELECT *
FROM #temp2
where RowNo = 1
-- RowNo 1 has the data you want based on the rank.

if (object_id('temptb..#temp') IS NOT NULL)
    DROP TABLE #temp
if (object_id('temptb..#temp2') IS NOT NULL)
    DROP TABLE #temp2
0

Joren,

try a scaler function call like this

CREATE FUNCTION MaxSubscriptionTypeID

(   @UserId int ) RETURNS int AS BEGIN  DECLARE @ID int     SELECT @ID = MAX(SubscriptionTypeId) FROM dbo.Subscriptions 
     WHERE UserId = @UserID     -- Return the result of the function    RETURN idnull(@ID,0)

END GO

SELECT 
    su.userId, u.name, u.street, u.number, u.zipcode, u.CountryCode, st.description, 
    MAX(su.validFrom) AS MaxValidFrom, 
    dbo.MaxSubscriptionTypeID( su.UserId) AS MaxSubscriptionTypeId FROM 
    dbo.Users u  INNER JOIN 
    dbo.Subscriptions su ON u.userId = su.userId INNER JOIN 
    dbo.SubscriptionTypes st ON su.subscriptionTypeId = st.subscriptionTypeId HAVING 
    su.SubscriptionTypeId = MAX(su.SubscriptionTypeId) ORDER BY 
    su.UserId, MaxValidFrom DESC;
  • Scalar functions are so horribly inefficient. A cross apply would be a more performant approach to this. – Sean Lange Nov 6 at 16:22
  • Sean, can be inefficient, but not in this case UserID is the primary key on the user table and there are only up to 3 records per user. – John Meek Nov 6 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.