SQL Query Advice - Get Previous Item

Question

Hi, I asked the opposite question here, now I am faced with another problem. Assume I have the following tables (as per poster in previous post)

CustID LastName FirstName
------ -------- ---------
1      Woman    Test
2      Man      Test

ProdID ProdName
------ --------
123    NY Times
234    Boston Globe

ProdID IssueID PublishDate
------ ------- -----------
123    1       12/05/2008
123    2       12/06/2008

CustID OrderID OrderDate
------ ------- ---------
1      1       12/04/2008

OrderID ProdID IssueID Quantity
------- ------ ------- --------
1       123    1       5
2       123    2       12

How do I obtain the previous issue (publishdate) from table 3, of all issue, by WeekDay name? The presious issue of today (Wednesday) will not be yesterday Tuesday but last week Wednesday. The result will be 3 columns. The Product Name, current issue (PublishDate) and previous issue (PublishDate).

Thanks

Edit ~ Here is a problem I am facing. What if the previous issue doesn't exists, it has to go back to the pror week as well. I tried the following as a test but doesn't work

SELECT TOP 1 publishdate FROM dbo.issue 
WHERE prodid = 123 AND datename(dw,publishdate) = datename(dw,'2008-12-31') 
ORDER BY publishdate desc

This is on SQL Server 2000.

Answer 1

Can IssueIDs be assumed to be ordered correctly, ie. every next issue has ID that is larger by 1?

In that case something like this should work:

SELECT Curr.ProdID, Curr.PublishDate As CurrentIssue, Prev.PublishDate AS PrevIssue
FROM Issues Curr, Issues Prev
WHERE ProdID=123 AND Curr.PublishDate='12/06/2008' AND Prev.IssueID=Curr.IssueID - 1

Otherwise there needs to be a way to determine which date is correct for previous issue (last week, last month?) and use subselect with date subtraction.

P.S. It'd be nice if you also included the names of the tables for easier reference, and also specified which SQL server you are using, as, for exmample, date function syntax varies wildly among them.

Answer 2

Maybe something like (syntax may be incorrect)

select ProdID, IssueID, max(PublishDate) 
where PublishDate<'2008-1-1'
group by ProdID, IssueID

Answer 3

Use the ROW_NUMBER() analytical function (PARTITION BY ProdID ORDER BY PublishDate DESC). Combine the MAX(PublishDate) with the rows having value 2 for the given analytical function.

It works in Oracle, and the SQL Server specs seem to be the same for this function, so it should work.

Answer 4

You can use a subquery like this

(SELECT TOP (1) PublishDate From Table3 as newName WHERE newName.PublishDate < PublishDate ORDER BY PublishDate DESC) As PrevDate

I wrote this off the head, you might also need to filter for the product id, and may need to change a few things.

Answer 5

I think you're saying that in this context, your definition of "previous issue" is "the issue that came out on the same day of the week in the previous week".

Does this accomplish what you want? (Calling your "table 2" products and your "table 3" issues.)

SELECT ProdName, current.IssueID, previous.IssueID
  FROM products, issues current, issues previous
  WHERE current.prodID = products.prodID
    AND previous.prodID = current.prodID
    AND previous.publishDate = current.publishDate - 7;

It's not clear if you want this information for the most recent issue, for all issues in the current week, or for all issues ever. You could add a condition on current.publishDate to restrict which "current" issues to look at.

I'm assuming that a previous issue always exists. If it is possible that it doesn't (some products skip days), you may want an outer join between "current" and "previous".

Answer 6

Whether an issue exists for today or yesterday or the day before or not shouldn't be an issue. You have a set which contains publishDates. You know all valid publishDates. So you want the largest publishDate from this set, where the publishDate is smaller than today's date.