Clever Geek Handbook

SQL Query - Get Previous Item

I asked the opposite question here , now I am facing another problem. Suppose I have the following tables (as per the poster in the previous post)

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

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

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

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

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

How do I get the previous version (publishdate) from table 3, of the whole issue, named WeekDay? Today's question today (Wednesday) will not be Tuesday yesterday, but Wednesday last week. The result will be 3 columns. Product name, current issue (PublishDate), and previous issue (PublishDate).

thank

Edit ~ Here is the problem I ran into. What if the previous problem doesn't exist, it should also return for a simple week. I've 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.

0


source to share


6 answers


It can be assumed that the identifiers can be ordered correctly, i.e. does each next number have an identifier that is 1 more?

In this 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


If not, there must be a way to determine which date is correct for the previous problem (last week, last month?) And use a date-subtracted subquery.

PS It would be nice if you also include the table names for easy referencing, and also indicate which SQL server you are using, since for example the date function syntax varies greatly among them.

+1


source


Maybe something like (syntax might be wrong)



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


source


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 out of my head, you may also need to filter out the product id and may need to change a few things.

+1


source


I think you are saying that in this context, your definition of "previous problem" is "a problem that came out on the same day of the week in the previous week."

Does this accomplish what you want? (Calling your products "Table 2" and problems with "Table 3".)

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 is unclear if you want this information for the most recent issue, for all issues this week, or for all issues. You can add a current.publishDate clause to restrict which "current" issues to look at.

I guess the previous problem always exists. If it is possible that this is not the case (some products skip days), you may need an outer join between "current" and "previous".

+1


source


Use the analytic function ROW_NUMBER () (PARTITION BY ORDED ORD BY PublishDATE DESC). Concatenate MAX (PublishDate) with strings set to 2 for this analytic function.

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

0


source


Whether the problem exists today or yesterday or the day before or not shouldn't be a problem. You have a set that contains publishDates. You know all valid publications. Thus, you want the largest publishDate from this set, where publishDate is less than today's date.

0


source






More articles:


All Articles