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 : 371 | Status : [Member] | Replies : 2
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?




Responses

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

Up
0
Down

Resolved

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

Up
0
Down

Resolved
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

Login to post response