June 28, 2012 at 1:29 pm
Hi,
I am trying to pull the most recent records for each department using the "partition by" clause.
The number of records that I want can vary so I have created a table that specifies the number of records desired for each department.
Here is an example of the code that I have written:
IF OBJECT_ID('tempdb..#RecsToPull') IS NOT NULL
DROP TABLE #RecsToPull
CREATE TABLE #RecsToPull
(
DeptAbbr char(10),
CasePulls int
)
INSERT INTO #RecsToPull values('A', 1)
INSERT INTO #RecsToPull values('B', 2)
INSERT INTO #RecsToPull values('C', 3)
IF OBJECT_ID('tempdb..#DeptPurchases') IS NOT NULL
DROP TABLE #DeptPurchases
CREATE TABLE #DeptPurchases
(
DeptNo char(10),
PurchaseDt date
)
INSERT INTO #DeptPurchases values('A', '01/1/2011')
INSERT INTO #DeptPurchases values('A', '02/1/2011')
INSERT INTO #DeptPurchases values('A', '03/1/2011')
INSERT INTO #DeptPurchases values('A', '01/2/2011')
INSERT INTO #DeptPurchases values('A', '02/28/2011')
INSERT INTO #DeptPurchases values('B', '01/1/2011')
INSERT INTO #DeptPurchases values('B', '02/1/2011')
INSERT INTO #DeptPurchases values('B', '03/1/2011')
INSERT INTO #DeptPurchases values('B', '01/2/2011')
INSERT INTO #DeptPurchases values('B', '02/28/2011')
INSERT INTO #DeptPurchases values('C', '01/1/2011')
INSERT INTO #DeptPurchases values('C', '02/1/2011')
INSERT INTO #DeptPurchases values('C', '03/1/2011')
INSERT INTO #DeptPurchases values('C', '01/2/2011')
INSERT INTO #DeptPurchases values('C', '02/28/2011')
--Select * from #RecsToPull
IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2
select DeptNo
, PurchaseDt
, ROW_NUMBER ()
OVER (PARTITION BY DeptNo
ORDER BY DeptNo ) AS counter
INTO #Temp2
from #DeptPurchases as dp
ORDER BY DeptNo
IF OBJECT_ID('tempdb..#Temp3') IS NOT NULL
DROP TABLE #Temp3
select Counter, DeptNo, PurchaseDt
INTO #Temp3
from #Temp2 as Output
join #RecsToPull as MRecs
on Output.DeptNo=MRecs.DeptAbbr
where Output.counter<=CasePulls
order by [PurchaseDt] desc
select DeptNo
,PurchaseDt
from #Temp3
ORDER BY DeptNo,[PurchaseDt] desc
It runs fine, it's just that I don't Know how to get it go give me the most recent records.
The current results are:
DeptNoPurchaseDt
A 2011-01-01
B 2011-02-01
B 2011-01-01
C 2011-03-01
C 2011-02-01
C 2011-01-01
Buit what I am hoping to get is:
DeptNoPurchaseDt
A 2011-03-01
B 2011-03-01
B 2011-02-28
C 2011-03-01
C 2011-02-28
C 2011-02-01
Is this possible to do or is there another way I should be approaching it?
Jon
June 28, 2012 at 1:53 pm
How about the following:
IF OBJECT_ID('tempdb..#RecsToPull') IS NOT NULL
DROP TABLE #RecsToPull;
CREATE TABLE #RecsToPull
(
DeptAbbr char(10),
CasePulls int
);
INSERT INTO #RecsToPull values('A', 1);
INSERT INTO #RecsToPull values('B', 2);
INSERT INTO #RecsToPull values('C', 3);
IF OBJECT_ID('tempdb..#DeptPurchases') IS NOT NULL
DROP TABLE #DeptPurchases ;
CREATE TABLE #DeptPurchases
(
DeptNo char(10),
PurchaseDt date
);
INSERT INTO #DeptPurchases values('A', '01/1/2011');
INSERT INTO #DeptPurchases values('A', '02/1/2011');
INSERT INTO #DeptPurchases values('A', '03/1/2011');
INSERT INTO #DeptPurchases values('A', '01/2/2011');
INSERT INTO #DeptPurchases values('A', '02/28/2011');
INSERT INTO #DeptPurchases values('B', '01/1/2011');
INSERT INTO #DeptPurchases values('B', '02/1/2011');
INSERT INTO #DeptPurchases values('B', '03/1/2011');
INSERT INTO #DeptPurchases values('B', '01/2/2011');
INSERT INTO #DeptPurchases values('B', '02/28/2011');
INSERT INTO #DeptPurchases values('C', '01/1/2011');
INSERT INTO #DeptPurchases values('C', '02/1/2011');
INSERT INTO #DeptPurchases values('C', '03/1/2011');
INSERT INTO #DeptPurchases values('C', '01/2/2011');
INSERT INTO #DeptPurchases values('C', '02/28/2011');
go
with BaseData as (
select
DeptNo,
PurchaseDt,
row_number() over (partition by DeptNo order by PurchaseDt desc) rn
from
#DeptPurchases
)
select
bd.DeptNo,
bd.PurchaseDt
from
BaseData bd
inner join #RecsToPull rtp
on (bd.DeptNo = rtp.DeptAbbr
and bd.rn <= rtp.CasePulls)
order by
bd.DeptNo,
bd.PurchaseDt desc;
June 28, 2012 at 2:01 pm
That's it! Thanks so much.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy