December 8, 2010 at 7:46 am
Hi
I have query for below scenario
tablename : prodid
prodid posteddate
1000 2010-12-05 00:00:00.000
1001 2010-12-05 00:00:00.000
1001 2010-12-06 00:00:00.000
1000 2010-12-07 00:00:00.000
1001 2010-12-07 00:00:00.000
the above scenario prodid 1000 expired on 6th dec.
again they will put in 7 th dec. but 1001 its not expired.so i have to retrieve 5 th dec.
Here i need to get
1000 2010-12-07 00:00:00.000
1001 2010-12-05 00:00:00.000
if the date is missing the product expired on that date or not available on that date.
if they put next day the product is came.
How can i do for the above scenario? Can anyone help me for this ?
December 8, 2010 at 9:58 pm
Something like this should help..
SELECTp_o.prodid, MAX( p_o.posteddate ) posteddate
FROMprodid p_o
WHERENOT EXISTS
(
SELECT*
FROMprodid p_i
WHEREp_i.prodid = p_o.prodid
ANDp_o.posteddate = DATEADD( DAY, 1, p_i.posteddate )
)
GROUP BY p_o.prodid
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 8, 2010 at 11:30 pm
it working fine.Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply