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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply