Skip to main content

Aggregate of a column for x day range

The title may not exactly explain with this post is about. I had the same problem while trying to search for a solution to this puzzle.

Background:
I work in debt collection industry. The collectors call debtors and collect money on the debts and based on the amount of money that they collect in a given month, they earn their commission. Sometimes towards the EoM, collectors are notoriously well known to be collecting any (good/bad) payments that they can, so to inflate their monthly numbers. And many a times the bad payment check will bounce as an NSF. But each of these NSFs cost big bucks to the Collection Agencies and the Debtor.

Puzzle:
One of the new analysis required was to find out which collector(s) had more than 10 NSF payments within any 5 day range. Example, between the range of April 1 and April 5, find all collectors that had more than 10 NSF payments or between the range of April 2 and April 6, find all collectors that had more than 10 NSF payments and so on.

Solution:
Here is a nice SQL statement that I wrote that does exactly the same as required:

select a.collector, tbl.dtrange, count(0) CountOfNSFs
from (
select distinct collector, convert(varchar(10), importedon, 101) + ' - ' + convert(varchar(10), importedon+4, 101) DtRange,
convert(varchar(10), importedon, 101) start, convert(varchar(10), importedon+4, 101) stop, count(0) NSFCount
from ach_nsf
where importedon between importedon and importedon+5
group by collector, convert(varchar(10), importedon, 101) + ' - ' + convert(varchar(10), importedon+4, 101), convert(varchar(10), importedon, 101) , convert(varchar(10), importedon+4, 101)) tbl
inner join ach_nsf a
on tbl.collector = a.collector
and a.importedon between tbl.start and tbl.stop
where a.importedon between tbl.start and tbl.stop
group by a.collector, tbl.dtrange, tbl.start
having count(0) >= 10
Labels:

Comments

Post a Comment

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...
27 comments
Read more

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...
6 comments
Read more

MicroStrategy - Understanding Level Metric

Many people have hard time understanding the Level Metrics in MicroStrategy - including me. Here is a quick cheat sheet I put together for reference. Consider the following data layout: Country, Regions, Call Centers Geographical Hierarchy: Country --< Regions --< Call Centers Total 6 call centers : Region1 (R1C1, R1C2), Region2 (R2C3, R2C4), Region3 (R3C5, R3C6) Report Filter : Call Center in (R1C1, R2C3, R2C4) 'The Cheat Sheet' TARGET ATTRIBUTE FILTERING GROUPING Revenue Summary Outcome Region STANDARD STANDARD Apply Report Filter to Call Center output on report and SUM all Revenue for Call Centers displayed on report and Group By Region Region1: R1C1 Region2: R2C3 + R2C4 Region STANDARD NONE Apply Report Filter to Call Center output on report and SUM all Revenue for Call Centers displayed on report and do NOT Group By Region1: R1C1 + R2C3 + R2C...
22 comments
Read more