Question

I couldn t quite find a scenario that matched what I am trying to do.

I have one table that holds UserID, their Subscription PackageID and their Subscription date.

Sample data for #Subscription for userID 14525398:

UserID      Package     Date
14525398    188535      2011-05-17 00:00:00.000
14525398    188535      2011-06-16 00:00:00.000
14525398    188536      2011-06-23 00:00:00.000

In the example above, on June 16th this person subscribed to PackageID 188535. On June 23rd they upgraded to PackageID 188536.

In another table I have their download history, but what I am trying to do is derive what PackageID they were subscribed to on the date of their download. This download table has UserID, Number of Downloads, Download Date, and the PackageID with no value that I need to update.

Sample data for #Download table is:

UserID      Dloads  Date                PackageID
14525398    3       2011-06-18 00:00:00.000     0
14525398    2       2011-06-18 00:00:00.000     0
14525398    2       2011-06-19 00:00:00.000     0
14525398    5       2011-06-24 00:00:00.000     0
14525398    2       2011-06-18 00:00:00.000     0

Using the example above, I need a query that accurately shows that the fourth record (5 downloads record) was on PackageID 188536 because that was the most recent package prior to the download date of 2011-06-24, whilst the rest would be on PackageID 188535.

I tried updating like so:

UPDATE #Download SET PackageID = (
SELECT TOP 1 PackageID 
FROM #subscription 
WHERE userID = #Download.userID AND Date <= #Download.Date
)

However this just inaccurately sets them all 188535. As mentioned, the downloads on 2011-06-24 should get set as PackageID 188536.

Any assistance would be greatly appreciated. SQL Server 2008.

Answer 1

UPDATE #Download SET PackageID = (
  SELECT TOP 1 PackageID
  FROM #subscription
  WHERE userID = #Download.userID AND Date <= #Download.Date 
  order by Date desc
)

I think it should work...

Answer 2

Since you need to pick the correct subscription, i have added the datediff to the where clause so that you pick the closest subscription date compared to the download date

UPDATE #Download SET PackageID = (
    SELECT TOP 1 PackageID 
    FROM #subscription 
    WHERE userID = #Download.userID 
    AND datediff(Date,#Download.Date) > 1
    orderby datediff(Date,#Download.Date)
)

友情链接