February 8, 2014 at 10:18 am
Hi
I think I should be able to do this with a single query using OVER clause, but I cannot get it to work.
I want to get the last Billing run for a each customer, i.e. Last Year, Period and for this combo get the
largest BillRunID The query below gets the desired results.
CREATE TABLE #tBills
(
CustIDint,
BillYear SMALLINT,
BillPeriod TINYINT,
BillRunIDint
)
GO
INSERT INTO #tBills
(CustID, BillYear, BillPeriod,BillRunID )
SELECT 1, 2014, 1, 300 UNION ALL
SELECT 1, 2014, 1, 900 UNION ALL
SELECT 1, 2013, 12, 310 UNION ALL
SELECT 1, 2013, 11, 290 UNION ALL
SELECT 2, 2013, 10, 500 UNION ALL
SELECT 2, 2013, 11, 510 UNION ALL
SELECT 2, 2012, 12, 100 UNION ALL
SELECT 3, 2014, 2, 100 UNION ALL
SELECT 3, 2013, 12, 450
GO
GO
WITH tMaxYear AS
(SELECT custid, MAX(Billyear) as Billyear from #tBills group by custid)
,
tMaxPeriod AS
(SELECT tY.custid, MAX(BillPeriod) as BillPeriod, tY.BillYear from tMaxYear as tY
INNER JOIN #tBills as tAll
ON tY.custid = tAll.Custid
AND tY.billyear = tAll.BillYear
group by tY.custid, tY.BillYear)
SELECT tP.custid, tP.BillPeriod, tP.BillYear, MAX(BillRunID) as billRunID from tMaxPeriod as tP
INNER JOIN #tBills as tAll
ON tP.custid = tAll.Custid
AND tP.billyear = tAll.BillYear
AND tP.BillPeriod = tAll.BillPeriod
GROUP BY tP.custid, tP.BillPeriod, tP.BillYear
Thank you
February 8, 2014 at 10:40 am
You've got a little tangled up in the description! Try these two, I can't tell which one should work for you. The first seems to kinda fit the description better, the second matches your result set.
SELECT
custid, BillPeriod, BillYear, BillRunID, rn
FROM (
SELECT
custid, BillPeriod, BillYear, BillRunID,
rn = ROW_NUMBER() OVER (PARTITION BY custid ORDER BY BillRunID DESC)
FROM #tBills
) d
--WHERE rn = 1
SELECT
custid, BillPeriod, BillYear, BillRunID, rn
FROM (
SELECT
custid, BillPeriod, BillYear, BillRunID,
rn = ROW_NUMBER() OVER (PARTITION BY custid ORDER BY BillYear DESC,BillPeriod DESC)
FROM #tBills
) d
--WHERE rn = 1
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 8, 2014 at 11:49 am
Thank you very much.
it's the second one: get the latest bill run per customer when the customer has multiple records select the one with the Largest billrunid (does that make more sense?)
SELECT
custid, BillPeriod, BillYear, BillRunID, rn
FROM (
SELECT
custid, BillPeriod, BillYear, BillRunID,
rn = ROW_NUMBER() OVER (PARTITION BY custid ORDER BY BillYear DESC,BillPeriod DESC, BillRunID DESC)
FROM #tBills
) d
WHERE rn = 1
I added BillRunID DESC.
I like your answer because it is very readable.
Once again thanks
February 9, 2014 at 9:36 am
You're welcome Terry. It's always good to see the OP figure it out by themselves - nice work.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply