Select rows from a table whcih was a result of join statement

Question

Select rows from a table whcih was a result of join statement

SkyBVI

SSCoach

Points: 15128
More actions
February 10, 2011 at 9:22 am

#234196

hI,
I want to do a modification in the table whcih was a result of hte join query....
..i want only tht rows in whcih pricedate(column) is equal to date_sold or nearest to the date _sold( lesser).......
I mean like if there are 5 identical rows like this( in the table) ---
item des qty date_ sold so_p c_p t_s sa_p price pricedate
1)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 11$ '2010-09-03 13:21:33'
2)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 10$ '2010-08-27 11:12:13'
3)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 13$ '2011-02-01 10:43:44'
4)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 08$ '2010-05-09 12:12:23'
5)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 12$ '2010-07-29 17:15:34'
So, in hte above rows from my data, only 1 row should be picked tht is row 2)
because the pricedate ('2010-08-27 11:12:13) is nearest to the date_sold(2010-09-01 08:32:42) in the backward direction,
In row 1) '2010-09-03 is nearest to '2010-09-01 but i dont want that as it is ahead of '2010-09-01....
I hope it was clear.
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SkyBVI SSCoach Points: 15128 More actions · Answer 1

For a particular item and a particular date_sold , there can be max of 1 row and a minimum of 0 rows (if pricedate> date_sold)

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Lowell SSC Guru Points: 323518 More actions · Answer 2

i got thru turning your post into consumable data, and ran out of gas....

if the data was already in consumable format, i probably would have worked on a solution instead.

here's the handoff for the next volunteer:

with mycte(item,des,qty,date_,sold,so_p,c_p,t_s,sa_p,price,pricedate)

AS

(

SELECT 1,'SD','Soda',1,'2010-09-01 08:32:42',10.00,11.00,20.00,0.00,11.00,'2010-09-03 13:21:33' UNION ALL

SELECT 2,'SD','Soda',1,'2010-09-01 08:32:42',10.00,11.00,20.00,0.00,10.00,'2010-08-27 11:12:13' UNION ALL

SELECT 3,'SD','Soda',1,'2010-09-01 08:32:42',10.00,11.00,20.00,0.00,13.00,'2011-02-01 10:43:44' UNION ALL

SELECT 4,'SD','Soda',1,'2010-09-01 08:32:42',10.00,11.00,20.00,0.00,08.00,'2010-05-09 12:12:23' UNION ALL

SELECT 5,'SD','Soda',1,'2010-09-01 08:32:42',10.00,11.00,20.00,0.00,12.00,'2010-07-29 17:15:34'

)

select * from mycte

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@ lowell

Thxs for replying.

I did not get what u replied..

I have around 16k rows wid different items ..

I cant know, how can i apply ur solution to my table.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP