November 12, 2014 at 5:32 am
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?
November 12, 2014 at 5:39 am
I'm not sure what you are trying to accomplish.
show the price of latest price in all of months product billed
Try to formulate this a little better.
ps: your insert statement is invalid. You can use the VALUES clause only once.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 5:43 am
Please correct your INSERT and add what you'd like to see for June in the output. It will help others to understand the problem better.
November 12, 2014 at 5:58 am
Quick suggestion, use the row_number function
😎
USE tempdb;
GO
SET NOCOUNT ON
IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test;
create table dbo.test
(
product varchar(50),
QTY int,
rate float,
Bill_date datetime,
total_val float
)
insert into dbo.test
values ('Milk','50','500','2014-04-20', '25000')
,('Milk','20','560','2014-05-20','12000')
,('Milk','30','450','2014-06-18','13500')
,('Milk','30','700','2014-06-18','21000');
;WITH BASE_DATA AS
(
SELECT
T.product
,T.QTY
,T.rate
,T.Bill_date
,T.total_val
,ROW_NUMBER() OVER
(
PARTITION BY T.product,YEAR(T.Bill_date), MONTH(T.Bill_date)
ORDER BY T.Bill_date DESC
) AS BD_RID
FROM dbo.test T
)
SELECT
BD.product
,BD.QTY
,BD.rate
,BD.Bill_date
,BD.total_val
FROM BASE_DATA BD
WHERE BD.BD_RID = 1;
Results
product QTY rate Bill_date total_val
-------- ----- ----- ----------------------- ----------
Milk 50 500 2014-04-20 00:00:00.000 25000
Milk 20 560 2014-05-20 00:00:00.000 12000
Milk 30 450 2014-06-18 00:00:00.000 13500
November 12, 2014 at 6:03 am
SELECTDATENAME(month,DATEADD(month,MONTH(t.Bill_date),0)) AS [Month],
t.product,
SUM(t.QTY) AS [QTY],
r.rate,
SUM(t.total_val) AS [T_val]
FROMtest t
CROSS APPLY (SELECT TOP 1 rate from test ORDER BY Bill_date DESC,rate DESC) r
GROUPBY MONTH(t.Bill_date),t.product,r.rate
ORDERBY MONTH(t.Bill_date)
Far away is close at hand in the images of elsewhere.
Anon.
November 12, 2014 at 6:10 am
Or may be
select MName, product, QTY
, max(rate) over( partition by product)
, total_val
from test
join (select * from (values (4,'April'),(5,'May'),(6,'June')) as t(MNmbr,MName)) as Months
on Months.MNmbr = DATEPART(M,Bill_date)
Just a guess based on
My expecting output:
Month product Qty Rate T_val
April Milk 50 700 25000
MAY MILK 20 700 12000
November 12, 2014 at 6:19 am
serg-52 (11/12/2014)
max(rate) over( partition by product)
Nice 🙂
I did not think of that :blush:
Far away is close at hand in the images of elsewhere.
Anon.
November 12, 2014 at 6:21 am
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is needed
Far away is close at hand in the images of elsewhere.
Anon.
November 12, 2014 at 6:25 am
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is needed
In that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 6:35 am
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is neededIn that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
November 12, 2014 at 6:42 am
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is neededIn that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
No, the ORDER BY is added from SQL Server 2012 for window aggregations. It was only added for ranking functions.
The 2005 version of the OVER clause docs.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 6:52 am
Koen Verbeeck (11/12/2014)
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is neededIn that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
No, the ORDER BY is added from SQL Server 2012 for window aggregations. It was only added for ranking functions.
Ooops :pinch:
Thanks Koen!
Of course an order in an aggregation doesn't make sense unless it has a ROW/RANGE (default or otherwise) clause within the window specification.
😎
November 12, 2014 at 6:56 am
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is neededIn that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
No, the ORDER BY is added from SQL Server 2012 for window aggregations. It was only added for ranking functions.
Ooops :pinch:
Thanks Koen!
Of course an order in an aggregation doesn't make sense unless it has a ROW/RANGE (default or otherwise) clause within the window specification.
😎
The default is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which is a pretty lousy default.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 7:32 am
Koen Verbeeck (11/12/2014)
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is neededIn that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
No, the ORDER BY is added from SQL Server 2012 for window aggregations. It was only added for ranking functions.
Ooops :pinch:
Thanks Koen!
Of course an order in an aggregation doesn't make sense unless it has a ROW/RANGE (default or otherwise) clause within the window specification.
😎
The default is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which is a pretty lousy default.
I agree! Also the somewhat inconsistent defaults do not make any easier. BTW saw your blog post on this, good job, had though of doing exactly that but not gotten around to do it.
😎
November 13, 2014 at 9:24 am
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is neededIn that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
David is right, MAX(rate) with Over/Partition/OrderBy on Date will not work because MAX gives you the max rate within the partition but not necessarily the rate for the most recent date within the partition.
Instead ROW_NUMBER() with partition/orderby-on-Date/Qualify, as David presented, produces the expected result.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply