How to show last purchased rate in all months of output? [Resolved]

Posted by Raghuldrag under Sql Server on 11/12/2014 | Points: 10 | Views : 786 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi Friends

I ve the table like

create table test
(
product varchar(50),
QTY int,
rate float,
Bill_date datetime,
total_val float

)

insert into test values ('Milk','50','500','2014-04-20', '25000')
values ('Milk','20','560','2014-05-20','12000')
values ('Milk','30','450','2014-06-18','13500')
values ('Milk','30','700','2014-06-18','21000')

My expecting output:

Month product Qty Rate T_val
April Milk 50 700 25000
MAY MILK 20 700 12000

like i ve to show the price of latest price in all of months product billed
How to make Query?

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 11/13/2014 [Member] [MVP] Platinum | Points: 50

0	SELECT MonthWise, product, Qty, Rate, Total_val FROM (SELECT Row_number() over(partition by datename(MONTH,Bill_date) order by Bill_date desc)rn ,datename(MONTH,Bill_date) MonthWise ,product, Qty, Rate, Total_val FROM test) T WHERE T.rn= 1 Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Raghuldrag, if this helps please login to Mark As Answer. \| Alert Moderator


SELECT MonthWise, product, Qty, Rate, Total_val 

FROM (SELECT Row_number() over(partition by datename(MONTH,Bill_date) order by Bill_date desc)rn 

	,datename(MONTH,Bill_date) MonthWise

	,product, Qty, Rate, Total_val 

FROM test) T 

WHERE T.rn= 1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/15/2014 [Member] [MVP] Platinum | Points: 50

0	Did you get the solution from above reply? if NOT, please post us back........... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Raghuldrag, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts