return a single line with recent date criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

return a single line with recent date criteria

Author

Topic

carsto
Starting Member

4 Posts

Posted - 2009-11-04 : 16:42:41

SQL newbie - creating views for use in Crystal Reports

SELECT *
FROM dbo.PURC_ORDER_LINE po
WHERE (LAST_RECEIVED_DATE =
(SELECT MAX(po1.LAST_RECEIVED_DATE)
FROM PURC_ORDER_LINE AS po1
WHERE po1.PART_ID = po.PART_ID AND YEAR(LAST_RECEIVED_DATE) < Year(GETDATE())))

This goal of this statement is to return a single line for each Part_ID, containing the most recent Last_Received_Date prior to 1/1 of the current year.

The problem is sometimes there are multiple lines for a Part_ID, could be on same Purch_Order_ID or on different, that were received on the same date which is the Last_Received_Date. So I get multiple lines for a Part_ID, when I only want one. I prefer to get the one with the highest ROWID, but I don't really care, since they were all received on the same date.

nalnait
Starting Member

14 Posts

Posted - 2009-11-04 : 20:56:28

top 1... order by rowid desc

carsto
Starting Member

4 Posts

Posted - 2009-11-05 : 09:10:14

I have tried to plug in top 1 into my above statement but cannot make it work. I'm far too much of a newbie to even have the statement that I already have!

I added (PART_ID = '2444') to see if the result were what I wanted.

Example: (YIELDS NO ROWS)
SELECT *
FROM dbo.PURC_ORDER_LINE po
WHERE (LAST_RECEIVED_DATE =
(SELECT TOP 1 (po1.RowID)
FROM PURC_ORDER_LINE AS po1
WHERE po1.PART_ID = po.PART_ID AND YEAR(LAST_RECEIVED_DATE) < Year(GETDATE()) AND (PART_ID = '2444')
ORDER BY RowID DESC))

Example 2:
SELECT *
FROM dbo.PURC_ORDER_LINE po
WHERE (LAST_RECEIVED_DATE =
(SELECT TOP 1 (po1.last_received_date)
FROM PURC_ORDER_LINE AS po1
WHERE po1.PART_ID = po.PART_ID AND YEAR(LAST_RECEIVED_DATE) < Year(GETDATE()) AND (PART_ID = '2444Z2532')
ORDER BY Last_received_Date DESC))

When adding (PART_ID = '2444'), it returns (3) rows for this Part_ID, all with the same receipt date of 3/21/2000, which is = Last_Receipt_Date but I only want 1 row.

BTW, the Rows are added to the table when the Purchase Order is created. The Last_Received_Date is changed each time there is a receipt against a given line. So the highest RowID is not normally the Last_Received_Date

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-05 : 09:11:57

SELECT TOP 1 *
FROM dbo.PURC_ORDER_LINE po
WHERE (LAST_RECEIVED_DATE =
(SELECT TOP 1 (po1.last_received_date)
FROM PURC_ORDER_LINE AS po1
WHERE po1.PART_ID = po.PART_ID AND YEAR(LAST_RECEIVED_DATE) < Year(GETDATE()) AND (PART_ID = '2444Z2532')
ORDER BY Last_received_Date DESC))

He is correct try top1 like in this

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

carsto
Starting Member

4 Posts

Posted - 2009-11-05 : 13:20:08

When you remove the
AND (PART_ID = '2444Z2532')
you still only get 1 row.

This goal of this statement is to return a single line for each Part_ID,
the row to return is the one containing the most recent Last_Received_Date prior to 1/1 of the current year for THAT PART_ID.

I just included AND (PART_ID = '2444Z2532') so I could verify the results

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-05 : 13:34:27

you need to lose the select *

then it would be helpful to see the ddl

Also, is ProductId Unique?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

carsto
Starting Member

4 Posts

Posted - 2009-11-05 : 15:58:26

This appears to do the trick:
hopefully I haven't missed anything

SELECT ROWID, PART_ID, UNIT_PRICE, PURC_ORDER_ID, ORDER_QTY, LAST_RECEIVED_DATE, PURCHASE_UM
FROM dbo.PURC_ORDER_LINE po
WHERE (ROWID =
(SELECT TOP 1 rowid
FROM dbo.PURC_ORDER_LINE po1
WHERE po1.PART_ID = po.PART_ID AND (YEAR(LAST_RECEIVED_DATE) < YEAR(GETDATE()))
ORDER BY LAST_RECEIVED_DATE DESC))

My table is a list of Purchase Order Lines. As we order the same part(s) over and over there are many lines with the same Part_ID. The only unique column in the table is RowID.

My goal was to create a view[of a table] that contains ONLY one instance of each Part_ID and that instance should be the most recent Last_Received_Date not in this calendar year.

THANK YOU ALL FOR YOUR HELP

X002548, I don't even know how to show you the ddl.
I'm only trying to create views for use in Crystal Reports, I really know nothing about databases.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources