Counting problem
-
I have a table that contains account balance for every customer each day. I need to count the number of (consecutive) days for which the balance is not changed.
IF object_id('tempdb..#Balance') IS NOT NULL
drop table #Balance
GO
CREATE TABLE #Balance(
CUSTOMER_NO int
,CURRENT_BALANCE numeric(16,2)
,ACTUAL_DATE datetime
)
INSERT INTO #Balance
SELECT 332 , 179304.00 ,'2009/04/16' UNION ALL
SELECT 332 , 179252.00 ,'2009/04/15' UNION ALL
SELECT 332 , 173697.66 ,'2009/04/14' UNION ALL
SELECT 332 , 115454.38 ,'2009/04/13' UNION ALL
SELECT 332 , 115454.38 ,'2009/04/12' UNION ALL
SELECT 5 , 75406.06 ,'2009/04/16' UNION ALL
SELECT 5 , 73406.06 ,'2009/04/15' UNION ALL
SELECT 5 , 73406.06 ,'2009/04/14' UNION ALL
SELECT 5 , 72406.06 ,'2009/04/13' UNION ALL
SELECT 5 , 73406.06 ,'2009/04/12'
I have following query:
SELECT CUSTOMER_NO
,CURRENT_BALANCE
,min(ACTUAL_DATE) [StartDate]
,max(ACTUAL_DATE) EndDate
,count(1) No_Of_Days
FROM #Balance
GROUP BY CUSTOMER_NO
,CURRENT_BALANCE
ORDER BY CUSTOMER_NO,[StartDate]
The above query is fine if the balance changes and never comes back to the same balance in future.
I get good results for customer number 332, but for customer number 5 the dates and days for the balance 73406.06 are incorrect.
I am not able to think in the right direction. Can anyone point me to the correct direction?
-Vikas Bindra
-
WITH CTE AS (
SELECT CUSTOMER_NO,
CURRENT_BALANCE,
ACTUAL_DATE,
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_NO ORDER BY ACTUAL_DATE) -
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_NO,CURRENT_BALANCE ORDER BY ACTUAL_DATE) AS rnDiff
FROM #Balance)
SELECT CUSTOMER_NO,CURRENT_BALANCE,
MIN(ACTUAL_DATE) AS StartDate,
MAX(ACTUAL_DATE) AS EndDate,
COUNT(DISTINCT ACTUAL_DATE) AS No_Of_Days
FROM CTE
GROUP BY CUSTOMER_NO,CURRENT_BALANCE,rnDiff
ORDER BY CUSTOMER_NO,MIN(ACTUAL_DATE)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark!!
-Vikas Bindra
-
Mark (4/17/2009)
WITH CTE AS (
SELECT CUSTOMER_NO,
CURRENT_BALANCE,
ACTUAL_DATE,
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_NO ORDER BY ACTUAL_DATE) -
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_NO,CURRENT_BALANCE ORDER BY ACTUAL_DATE) AS rnDiff
FROM #Balance)
SELECT CUSTOMER_NO,CURRENT_BALANCE,
MIN(ACTUAL_DATE) AS StartDate,
MAX(ACTUAL_DATE) AS EndDate,
COUNT(DISTINCT ACTUAL_DATE) AS No_Of_Days
FROM CTE
GROUP BY CUSTOMER_NO,CURRENT_BALANCE,rnDiff
ORDER BY CUSTOMER_NO,MIN(ACTUAL_DATE)
Very cool, Mark. Better than that, this finally answers the question of why someone would want to do this kind of strange grouping with a real life problem. It's the first time I've seen a real practical use for it. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply